evaluating a boolean against a bit field in LINQ To SQL query

Question

I am trying to do a simple little LINQ To SQL query against a SQL CE database.

var result = from item in items
             where item.IsTrue == true
             select item;

The problem is that the IsTrue property is a bit field in the database (bool in the CLR). When it goes to SQL in CE I get SELECT ... WHERE ([t0].IsTrue = 1).. The 1 value is an integer to SqlCe and it wont cast it by default.

The index that I have on that column (the IsTrue column) doesn't get used. Instead it tries to cast all of the istrue values in the database to integers and compares them to the 1.

How can I get LINQ To SQL to generate WHERE ([t0].IsTrue = Cast(1 as bit))...? I need somehow to force the casting of that value to a bit, and thereby allowing use of the index?

I tried:

• item.IsTrue == Convert.ToBoolean(1)
• item.IsTrue == Convert.ToBoolean("true")
• item.IsTrue == (bool)true)

Hoping to get the expression tree to have a cast in it so that it converts to a cast in LINQ To SQL, but i cant seem to find a way. Any ideas?

Answer 1

Have you tried leaving out the == true part and just using

var result = from item in items where item.IsTrue select item;

Alternatively, using method syntax:

var result = items.Where(item => item.IsTrue);