Simple SQL query to LINQ

Question

I have the following query:

SELECT FROM tblMailToSend
WHERE (DateToSend < @dateToSend OR DateToSend IS NULL) AND DateSent IS NULL

@dateToSend is passed in as a param

I'm trying to convert this to linq to sql query.

I've got:

db.MailToSend.Where(m => m.DateToSend == null || m.DateToSend <= dateToSend)
                    .Where(m => m.DateSent == null)

But this is giving the following SQL:

SELECT *
FROM [dbo].[tblMailToSend] AS [t0]
WHERE ([t0].[DateSent] IS NULL) AND (([t0].[DateToSend] IS NULL) OR ([t0].[DateToSend] <= @p0))

Which is giving the wrong results...

What Linq query would I need to match the correct (first) sql?

Answer 1

Try this one:

db.MailToSend.Where(m => (m.DateToSend == null || m.DateToSend <= dateToSend) && m.DateSent == null);

Answer 2

Might be a typo, but your SQL is using < whilst your Linq is using <=. Apart from that your queries look like they should produce the same results.

Answer 3

The only different is that in your linq statement you are using m.DateToSend <= dateToSend instead of the strict less than in your first sql statement. So change that and everything should be right.

Answer 4

In the Linq-question you have DateToSend <= @DateToSend (Less or equal) but in the SQL-query you just check <