Subsonic 3, SimpleRepository, SQL Server: How to find rows with a null field?

Question

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?

Answer 1

You can use:

var visits = myRepository.All<Visit>().Where(x => x.SynchronizedOn == null).ToList();