A CAST to int is introduced in the generated query for the tinyint column.
c# entity-framework sql-server
Question
Even though the data I am using in the WHERE clause is of the type byte, entity-framework constructs a SELECT query for the tinyint column that adds a CAST to INT.
My tinyint column's produced Type, according to the model, is byte.
examining the code
byte byteValue = 6;
var entityList = from r in rep.DataContext.FooTable
where r.TinyintColumn == byteValue
select r;
Examining the produced statement:
SELECT [Extent1].[TinyintColumn] AS [TinyintColumn] WHERE @p__linq__0 = CAST( [Extent1].[TinyintColumn] AS int)
Because of my high performance requirements, I don't want them CAST in any pick.
Therefore, I would want to know whether there is a method to prevent this CAST over the column tinyint. or Am I mishandling this?
I appreciate it.
Accepted Answer
If you employIList<T>.Contains
with aList<byte>
Entity Framework won't allow casting.
List<byte> byteValue = new List<byte> { 6 };
var entityList = from r in rep.DataContext.FooTable
where byteValue.Contains(r.TinyintColumn)
select r;
The identical issue occurred to me and a blog post about it.
Popular Answer
On Entity Framework 4.0, my colleague discovered a pretty excellent technique to solve this problem.
I didn't tried it on tinyint, but it works on smallint.
Use the Contains() operator, which was introduced with EF 4.0, instead of the equals (==) operator.
For instance:
Let's suppose you have the SmallIntColumn column.
in place 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 produced SQL; the CAST has been removed!
The execution plan utilized my (filtered) index on the column flawlessly, according to my testing.
Hope it was helpful.
Shlomi
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
