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

I landed on this page because I had the same problem.

I found you can work around this particular brokenness by using compiled queries. To use your example:

DataContext context = /* ... */;

Func <DataContext, IQueryable<ItemType> compiledQuery = null;

compiledQuery = CompiledQuery.Compile(
   (DataContext ctx) => (from item in Items
                         where item.IsTrue
                         select item)
);

var result = compiledQuery(context);

If you look at the SQL this generates (by hooking up the log stream in the DataContext) it for some reason does the right thing where using non-compiled queries does not. Your indices that have bools in them will start working.