Generated Query For Tinyint Column Introduces A Cast To Int

July 25, 2024 Post a Comment

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 i

Solution 1:

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.

Solution 2:

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 = newshort[] { 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

Solution 3:

The CAST will affect performance because indexes won't be used on TinyintColumn

This is combination of points 2 and 4 in "Ten Common SQL Programming Mistakes". CAST is a function on a column, and without it you'll have a datatype mismatch anyway

@p__linq__0 should be tinyint or explicitly CAST.

However, it could be LINQ doesn't like tinyint primary keys according to MS Connect and (SO) asp.net mvc linq sql problem

You could "byte" the bullet (sorry) and use smallint...

Solution 4:

The contains solution may not be optimized by the DB if the smallint comparison is one segment of the filtering on multiple columns and there is an index matching the those columns. I verified that using the Equals method fixed this problem with the SmallInt type, at least on EF6.

Instead of

short shortValue = 6;
var entityList = from r in rep.DataContext.FooTable
                 where r.SmallIntColumn == shortValue
                 select r;

use

short shortValue = 6;
var entityList = from r in rep.DataContext.FooTable
                 where r.SmallIntColumn.Equals(shortValue)
                 select r;

Solution 5:

I'm posting the solution I've taken for this problem.

It seems that EntityFramework 4.0 always generates queries with CAST in tinyint or smallint fields. So for performance optimization, I have decided to change to INT those fields to avoid the CAST and I have changed the size of other nvarchar fields that I still could decrease from nvarchar(50) to nvarchar(30). So at the end I have changed the size of the row from 143 Bytes to 135 Bytes.

Lab Sql