Question
I am querying a tinyint column and entity-framework generates a SELECT query that introduces a CAST to INT for this column even when the value that I am using in the WHERE clause is of the type byte.
Looking the Model, the generated Type for my tinyint column is byte.
Looking the code:
byte byteValue = 6;
var entityList = from r in rep.DataContext.FooTable
where r.TinyintColumn == byteValue
select r;
Looking the generated query:
SELECT [Extent1].[TinyintColumn] AS [TinyintColumn] WHERE @p__linq__0 = CAST( [Extent1].[TinyintColumn] AS int)
I have strict restrictions in the performance so I don't want those CAST in any select.
So my question whould be, is there any way to avoid this CAST over the column tinyint? or am I doing something wrong?
Thanks in advance.
Accepted Answer
If you use IList<T>.Contains with a List<byte> the Entity Framework won't cast.
List<byte> byteValue = new List<byte> { 6 };
var entityList = from r in rep.DataContext.FooTable
where byteValue.Contains(r.TinyintColumn)
select r;
I ran into the same problem and blogged about it.
Popular Answer
My colleague found very nice trick to overcome this issue on Entity Framework 4.0.
Works for smallint, I didn't try on tinyint.
Insteal of equals (==) - use Contains() operator which was implemented with EF 4.0.
For example:
say you have the column SmallIntColumn.
instead of:
short shortValue = 6;
var entityList = from r in rep.DataContext.FooTable
where r.SmallIntColumn == shortValue
select r;
use
short[] shortValue = new short[] { 6 };
var entityList = from r in rep.DataContext.FooTable
where shortValue.Contains(r.SmallIntColumn)
select r;
Check the SQL generated - it is now without the CAST!
And from my tests - the execution plan used my (filtered) index on the column just perfectly.
Hope it helped.
Shlomi
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
