I am using LINQ to SQL to retrieve data, using boolean conditions (BIT columns in SQL). My LINQ query looks something like this:
var query = from r in db.Requests
select r;
query = query.Where(r => r.Completed == someBooleanVal);
query = query.Where(r => r.Cancelled == someOtherBool);
return query.ToList();
The 'Where()' gets applied in a different method, that's why I'm putting it in separately.
When the boolean values are given as false, the generated SQL looks something like this:
SELECT [t0].[col1], [t0].[col2], [t0].[col3], [t0].[etc]
FROM [dbo].[Requests] AS [t0]
WHERE (NOT(([t0].[Cancelled]) = 1) AND (NOT(([t0].[Completed]) = 1)
in stead of what I would use:
WHERE [t0].[Cancelled] = 0 AND [t0].[Completed] = 0
This runs very, very slowly. I strongly suspect that it is because of the negative conditions on the boolean values it generated (all the selected columns are covered by an index, and the two columns in the where clause have a separate index on them).
Why is it generating negative conditions? How can I fix it?