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

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 istr开发者_如何学JAVAue 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?

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.

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);