c# - LINQ to SQL will not generate sargable query -

i'm using linq sql (not entity framework), system.data.linq.datacontext library, hitting sql server 2005 database , using .net framework 4.

the table dbo.dogs has column "active" of type char(1) null. if writing straight sql query be:

select * dbo.dogs active = 'a'; 

the linq query this:

from d in mydatacontext.dogs d.active == 'a' select d; 

the sql gets generated above linq query converts active field unicode. means cannot use index on dbo.dogs.active column, slowing query significantly:

select [t0].name, [t0].active [dbo].[dog] [t0] unicode([t0].[active]) = @p1 

is there can stop linq sql inserting unicode() call (and losing benefit of index on dogs.active)? tried wrapping parameters using entityfunctions.asnonunicode() method, did no (it inserted convert() nvarchar instead of unicode() in generated sql), eg:

...where d.active.tostring() == entityfunctions.asnonunicode('a'.tostring()); 

you can little hack (as required linq sql , ef). declare property nchar in dbml. hope remove need unicode conversion. tricking l2s in benign way that.

maybe need insert entityfunctions.asnonunicode call make right hand side non-unicode type.

you can try mapping column varchar.