Skip to content
/

Filtering on a tinyint with Entity Framework

When writing .NET code to access a SQL database we often rely on the Entity Framework (EF). The EF makes it very easy to retrieve data from the database by generating a SQL Query for us. But we shouldn't trust it blindly as the EF can also generate a bad query. It will return the correct data yes, but at what performance cost?

I have a table with lots of data, and to keep the table as small as possible on disk and in memory I want to optimize the columns. This can be done by using varchar instead of nvarchar, date instead of datetime and also using tinyint instead of int. Of course you only can do this if your data allows this.

The problem

Changing a column in my table from int to tinyint gave me a storage and memory space win, but it also gave me a performance loss!

In my case the SQL column "HasSong" is a tinyint. EF translates this to a Byte Structure in the .NET model.

The following code:

var query = from d in dataEntities.Donations
            where d.HasSong == 1
            select d.DonationId;

produces this SQL Query:

SELECT
    [Extent1].[DonationId] AS [DonationId]
FROM
    [dbo].[Donations] AS [Extent1]
WHERE
    1 = CAST([Extent1].[HasSong] AS int)

When SQL encounters this CAST it will skip all Indexes that are on the HasSong column resulting in the query engine using non-optimized indexes or even worse: full table scans.

So this explains my performance loss, but how do we convince EF not to cast my byte to an int?

The familiar "Contains"

Browsing the internet gave me my first idea: using the familiar contains method I encountered on an earlier post about the EF: Joining an IQueryable with an IEnumerable.
I just have to add an Array with a single value.

So trying this:

var tinyintComparison = new byte[] { 1 };

var query = from d in dataEntities.Donations
            where tinyintComparison.Contains(d.HasSong)
            selectd.DonationId;

was not such a good idea as it throws an ArgumentException:
Screenshot showing Visual Studio debugger with the message "ArgumentException: DbExpressionBinding requires an input expression with a collection ResultType"
It did work for other people using a SMALLINT, so I guess an array of bytes is a special case reserved for working with binary data in your database.

The solution

Lucky enough we are not far from a solution, changing the array to a List<T> Class:

var tinyintComparison = new List<byte> { 1 };

var query = from d in dataEntities.Donations
            where tinyintComparison.Contains(d.HasSong)
            selectd.DonationId;

results in the following query:

SELECT
    [Extent1].[DonationId] AS [DonationId]
FROM
    [dbo].[Donations] AS [Extent1]
WHERE
    1 = [Extent1].[HasSong]

There we are! No more casting. The SQL query engine can use the correct indexes again and we still won disk and memory space.

Conclusion

If you don't want a CAST in your SQL query when using a tinyint use the IList<T>.Contains Method.

4 Comments

  1. /

    Thanks Michaël, this is the only workaround I found for this issue and it seems to work fine for me. The only thing I did find was that the revised query now casts the value which your example doesn't do?

    • /

      Hi Rich,
      That's strange, it shouldn't cast anything, which version of SQL. .NET and EF are you using?

  2. /

    Nice article but 1 question. What if we have to compare values ? for example i have a tinyint column and i want to query table by myValue is greater that this column..

    Your solution is just for "Equal", what if "Greater than" or "Less than" ?

  3. /

    Instead of using contains/equals and rewriting queries, consider using FixIntCast extension on querablies, as shown here:
    http://stackoverflow.com/questions/9016265/generated-query-for-tinyint-column-introduces-a-cast-to-int

Leave a comment