Reputation: 389
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
Reputation: 23
The db column is probably nullable.
Try this: r.TinyintColumn.Value == byteValue
Upvotes: 0
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
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
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
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
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
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
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
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
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