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.

1
15
1/26/2012 9:47:43 AM

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.

11
12/1/2015 6:43:06 AM

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





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow