CODEWITHSUNDEEP
c#sql-serverentity-framework
Belén Morenate
Belén Morenate

Reputation: 389

Generated query for tinyint column introduces a CAST to int

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.

Upvotes: 17

Views: 10328

Answers (10)

user7435032
user7435032

Reputation: 23

The db column is probably nullable. Try this: r.TinyintColumn.Value == byteValue

Upvotes: 0

Alex W
Alex W

Reputation: 3373

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;

Upvotes: 3

Michaël Hompus
Michaël Hompus

Reputation: 3489

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.

Upvotes: 12

Filip Sielimowicz
Filip Sielimowicz

Reputation: 1

Try more complex version of IntCastFixExtension :

namespace System.Linq {


/// <summary>
/// author: Filip Sielimowicz inspired by
/// http://www.entityframework.info/Home/SmallIntProblem
/// </summary>
public static class IntCastFixExtension {

    public static IQueryable<T> FixIntCast<T>(this IQueryable<T> q, bool narrowMemberExpr = true, bool narrowConstantExpr = true) {
        var visitor = new FixIntCastVisitor() {
            narrowConstExpr = narrowConstantExpr,
            narrowMembExpr = narrowMemberExpr
        };
        Expression original = q.Expression;
        var expr = visitor.Visit(original);
        return q.Provider.CreateQuery<T>(expr);
    }

    private class FixIntCastVisitor : ExpressionVisitor {

        public bool narrowConstExpr;
        public bool narrowMembExpr;

        protected override Expression VisitBinary(BinaryExpression node) {
            bool eq = node.NodeType == ExpressionType.Equal;
            bool neq = node.NodeType == ExpressionType.NotEqual;
            if (eq || neq) {
                var leftUncasted = ReducePossiblyNotNecessaryIntCastExpr(node.Left);
                var rightUncasted = ReducePossiblyNotNecessaryIntCastExpr(node.Right);
                var rightConst = node.Right as ConstantExpression;
                if (leftUncasted == null) {
                    return base.VisitBinary(node);
                }
                if (rightUncasted != null) {
                    if (NarrowTypesAreCompatible(leftUncasted.Type, rightUncasted.Type)) {
                        // Usuwamy niepotrzebne casty do intów występujące po obu stronach equalsa
                        return eq ? Expression.Equal(leftUncasted, rightUncasted) : Expression.NotEqual(leftUncasted, rightUncasted);
                    }
                } else if (rightConst != null) {
                    // Zamiast casta argumentu z lewej w górę do inta (tak zrobił linq2entity)
                    // zawężamy występującą po prawej stałą typu 'int' do typu argumentu z lewej
                    if (narrowConstExpr && (rightConst.Type == typeof(int) || rightConst.Type == typeof(int?))) {
                        var value = rightConst.Value;
                        var narrowedValue = value == null ? null : Convert.ChangeType(rightConst.Value, leftUncasted.Type);
                        Expression narrowedConstExpr = Expression.Constant(narrowedValue, leftUncasted.Type);
                        return eq ? Expression.Equal(leftUncasted, narrowedConstExpr) : Expression.NotEqual(leftUncasted, narrowedConstExpr);
                    }
                } else if (node.Right.NodeType == ExpressionType.MemberAccess) {
                    // Jak po prawej mamy wyrażenie odwołujące się do zmiennej typu int to robimy podobnie jak przy stałej
                    // - zawężamy to, zamiast upcasta do inta z lewej.
                    if (narrowMembExpr) {
                        var rightMember = node.Right;
                        var narrowedMemberExpr = Expression.Convert(rightMember, leftUncasted.Type);
                        return eq ? Expression.Equal(leftUncasted, narrowedMemberExpr) : Expression.NotEqual(leftUncasted, narrowedMemberExpr);
                    }
                }
            }
            return base.VisitBinary(node);
        }

        private bool NarrowTypesAreCompatible(Type t1, Type t2) {
            if (t1 == typeof(short?)) t1 = typeof(short);
            if (t2 == typeof(short?)) t2 = typeof(short);
            if (t1 == typeof(byte?)) t1 = typeof(byte);
            if (t2 == typeof(byte?)) t2 = typeof(byte);
            return t1 == t2;
        }

        private bool IsNullable(Type t) {
            return t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>);
        }

        private Expression CorrectNullabilityToNewExpression(Expression originalExpr, Expression newExpr) {
            if (IsNullable(originalExpr.Type) == IsNullable(newExpr.Type)) {
                return newExpr;
            } else {
                if (IsNullable(originalExpr.Type)) {
                    Type nullableUncastedType = typeof(Nullable<>).MakeGenericType(newExpr.Type);
                    return Expression.Convert(newExpr, nullableUncastedType);
                } else {
                    Type notNullableUncastedType = Nullable.GetUnderlyingType(newExpr.Type);
                    return Expression.Convert(newExpr, notNullableUncastedType);
                }

            }
        }

        private Expression ReducePossiblyNotNecessaryIntCastExpr(Expression expr) {
            var unnecessaryCast = expr as UnaryExpression;
            if (unnecessaryCast == null ||
                unnecessaryCast.NodeType != ExpressionType.Convert ||
                !(unnecessaryCast.Type == typeof(int) || unnecessaryCast.Type == typeof(int?))
            ) {
                // To nie jest cast na inta, do widzenia
                return null;
            }
            if (
                (unnecessaryCast.Operand.Type == typeof(short) || unnecessaryCast.Operand.Type == typeof(byte)
                || unnecessaryCast.Operand.Type == typeof(short?) || unnecessaryCast.Operand.Type == typeof(byte?))
            ) {
                // Jest cast z shorta na inta
                return CorrectNullabilityToNewExpression(unnecessaryCast, unnecessaryCast.Operand);
            } else {
                var innerUnnecessaryCast = unnecessaryCast.Operand as UnaryExpression;
                if (innerUnnecessaryCast == null ||
                    innerUnnecessaryCast.NodeType != ExpressionType.Convert ||
                    !(innerUnnecessaryCast.Type == typeof(int) || innerUnnecessaryCast.Type == typeof(int?))
                ) {
                    // To nie jest podwójny cast między intami (np. int na int?), do widzenia
                    return null;
                }
                if (
                    (innerUnnecessaryCast.Operand.Type == typeof(short) || innerUnnecessaryCast.Operand.Type == typeof(byte)
                    || innerUnnecessaryCast.Operand.Type == typeof(short?) || innerUnnecessaryCast.Operand.Type == typeof(byte?))
                ) {
                    // Mamy podwójny cast, gdzie w samym środku siedzi short
                    // Robimy skrócenie, żeby intów nie produkował zamiast short -> int -> int?
                    // powinno ostatecznie wychodzić short -> short czyli brak castowania w ogóle.
                    return CorrectNullabilityToNewExpression(unnecessaryCast, innerUnnecessaryCast.Operand);
                }
            }
            return null;
        }
    }
}

}

Upvotes: 0

Ben
Ben

Reputation: 2484

If you like to preserve the logic you can use expression rewrite method. The code will be like db.MyEntities.Where(e => e.Id == i).FixIntCast() and you keep the application logic as is.

Upvotes: 0

Peter
Peter

Reputation: 11

I ran into the exact same problem while using EF with a lambda expression. Beefing up the datatype to an int is not a solution and even bad practice. What i found and as other reported here is that you do get correct code when you take a more clumsy aproach, like:

SomeEntity.FindBy( i => new List { 1 }.Contains( i.TinyintColumn ) )

But when then you run into other issues with more then one value to match against. The following will not use parameterised query values, and but just inline them into the query body!

SomeEntity.FindBy( i => new List { 1, 2 }.Contains( i.TinyintColumn ) )

That is not as bad at the original problem, but still not good as it means the database has to complile a plan for every combination of values that you throw at it and makes performance analysis next to impossible as there is no proper aggregation of execution times. It also has some performance effects you rather not see in high load enviroments!

Do not get me started on what these kind of behaviors/anti-patterns would do to char/nchar datatypes and their effect on indexes. As i see it, centralizing everything around the datatype system C# implements is both limited and causes major issues.

My view on EF is that very basic queries on well modelled tables are transformed to bad SQL code and EF follows anti-patterns. It is not something I find impressive in the light of the hype and the added complexity in development EF brings! I wont go into that here right now, as that would be a whole different discussion!

Pick any of the above solutions, but know the drawbacks before using them. Maybe version 10 of EF will solve the problem to a degree, i don't hold my breath however.

Upvotes: 1

Ranjan Dailata
Ranjan Dailata

Reputation: 36

If you have an Sql table column data type of tinyint , the corresponding POCO objects should have a property of type byte. This will work for you. Else you when you are iterating through the LINQ object , it will throw an error stating that unable to convert byte type to say int or whatever as you may have defined for the property.

I just verified with EF 4.3 Code First Approach , Everything went well.

Upvotes: 1

shlomiw
shlomiw

Reputation: 365

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

Upvotes: 6

Bel&#233;n Morenate
Bel&#233;n Morenate

Reputation: 389

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.

Upvotes: 1

gbn
gbn

Reputation: 432639

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...

Upvotes: 2

Related Questions