Subsonic 3, SimpleRepository, SQL Server: How to find rows with a null field?
- by desautelsj
How ca I use Subsonic's Find<T> method to search for rows with a field containing the "null" value. For the sake of the discussion, let's assume I have a c# class called "Visit" which contains a nullable DateTime field called "SynchronizedOn" and also let's assume that the Subsonic migration has created the corresponding "Visits" table and the "SynchronizedOn" field.
If I was to write the SQL query myself, I would write something like:
SELECT * FROM Visits WHERE SynchronizedOn IS NULL
When I use the following code:
var visits = myRepository.Find<Visit>(x => x.SynchronizedOn == null);
Subsonic turns it into the following SQL query:
SELECT * FROM Visits WHERE SynchronizedOn == null
which never returns any rows.
I tried the following code but it throws an error:
visits = repository.Find<Visit>(x => x.SynchronizedOn.HasValue);
I was able to use the following syntax:
var query = from v in repository.All<Visit>()
where v.SynchronizedOn == null
orderby v.CreatedOn
select v;
visits = query.ToList<Visit>();
but it's not as nice an short as using the Find<T> method.
Anyone knows how I can specify the "SynchronizedOn IS NULL" condition in the Find<T> method?
