| Bug #73643 | Exception when using IEnumera.Contains(model.property) in Where predicate | ||
|---|---|---|---|
| Submitted: | 19 Aug 2014 12:48 | Modified: | 22 Oct 2014 15:00 |
| Reporter: | Ryan Griffith (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 6.8.3.0 | OS: | Windows (Windows 7 64x) |
| Assigned to: | Fernando Gonzalez.Sanchez | CPU Architecture: | Any |
[21 Aug 2014 20:40]
Ryan Griffith
Apparently this issue is even more simply than I described. The code below will exhibit the same behavior:
Models.MyDbContext context = new Models.MyDbContext();
int[] ids = new int[] { 1, 2, 3 };
var results = context.projs.Where(o => ids.Contains(1));
results.ToList(); // Error with essentially the same stack trace
at MySql.Data.Entity.SqlGenerator.Visit(DbPropertyExpression expression)
at MySql.Data.Entity.SqlGenerator.Visit(DbInExpression expression)
at System.Data.Entity.Core.Common.CommandTrees.DbInExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
at MySql.Data.Entity.SqlGenerator.VisitBinaryExpression(DbExpression left, DbExpression right, String op)
at MySql.Data.Entity.SqlGenerator.Visit(DbAndExpression expression)
at System.Data.Entity.Core.Common.CommandTrees.DbAndExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
at MySql.Data.Entity.SelectGenerator.Visit(DbFilterExpression expression)
at System.Data.Entity.Core.Common.CommandTrees.DbFilterExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression)
at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree)
at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver)
at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at WindowsFormsApplication1.Form1..ctor() in c:\Users\ryan\Documents\Visual Studio 2012\Projects\WindowsFormsApplication1\Form1.cs:line 36
at WindowsFormsApplication1.Program.Main() in c:\Users\ryan\Documents\Visual Studio 2012\Projects\WindowsFormsApplication1\Program.cs:line 19
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
[21 Aug 2014 22:57]
Ryan Griffith
After further research in the source code, it appears that the Connector assumes that the comparison value for an IN statement is a DbPropertyExpression and is ignoring the fact that it could be a Constant or any a method (i.e. ToString()). I'm looking further into the issue.
[22 Aug 2014 18:37]
Ryan Griffith
The following code segment should fix part of the issue (which was due to casting).
File: Source\MySql.Data.Entity\Generators\SqlGenerator.cs
#if EF6
public override SqlFragment Visit(DbInExpression expression)
{
ColumnFragment cf = null;
// Check for a valid expression kind
switch (expression.Item.ExpressionKind) {
case DbExpressionKind.Cast:
cf = Visit(expression.Item as DbCastExpression) as ColumnFragment;
break;
case DbExpressionKind.Property:
cf = Visit(expression.Item as DbPropertyExpression) as ColumnFragment;
break;
default:
throw new NotSupportedException(string.Format("DbInExpression does not support and Item of type {0}", expression.Item.ExpressionKind));
}
InFragment inf = new InFragment();
......
[29 Aug 2014 16:34]
Gabriela Martinez Sanchez
Hi, I'm trying to reproduce this behavior but I'm not getting the same result. Do you have any special configuration for the Model? Thanks in advance.
[29 Aug 2014 18:07]
Ryan Griffith
Hi Francisco,
************
You are correct, there was an error in one of my examples. I've included updated examples below.
************
I have determined the problem exhibited is found in at least 2 scenarios. I downloaded the source and tracked it back to the fact that SqlGenerator.Visit(DbInExpression expression) does not support any argument type other than a "DbPropertyExpression" to the Contains() method. This means that DbCastExpression, DbConstantExpression and DbParameterReferenceExpression (at a minimum) will fail when being provided as the DbInExpression.Item argument.
// EXAMPLE 1:
// This will fail due to "two" being a DbConstantExpression (which is the value of DbInExpression.Item)
List<string> strIds = new List<string>(new[] { "two" });
context.AnyModelAtAll.Where(model => strIds.Contains(model.id.ToString())).ToList();
// EXAMPLE 2:
// This will fail due to "myNum" being a DbParameterReferenceExpression (which is the value of DbInExpression.Item)
long[] longs = new long[] { 1, 2, 3 };
int myNum = 1;
context.AnyModelAtAll.Where(o => longs.Contains(myNum)).ToList();
// EXAMPLE 3:
// This will fail due to "(long)o.id" being a DbCastExpression (which is the value of DbInExpression.Item)
// Note: "id" is an Int
long[] longs = new long[] { 1, 2, 3 };
context.AnyModelAtAll.Where(o => longs.Contains((long)o.id)).ToList();
[29 Aug 2014 18:22]
Ryan Griffith
Yet another correction:
// EXAMPLE 1 CORRECTION:
// This will fail due to "model.id.ToString()" being a DbCastExpression (which is the value of DbInExpression.Item)
List<string> strIds = new List<string>(new[] { "two" });
context.AnyModelAtAll.Where(model => strIds.Contains(model.id.ToString())).ToList();
[6 Sep 2014 0:41]
Emil Lenngren
I don't understand either why the argument is assumed to be a column. It can be whatever expression. I suggest you simply change the type of the field "Argument" in "InFragment" from "ColumnFragment" to "SqlFragment", and then change the first line of code in Visit(DbInExpression) from ColumnFragment cf = Visit(expression.Item as DbPropertyExpression) as ColumnFragment; to SqlFragment cf = Visit(expression.Item); (and maybe rename the variable cf).
[25 Sep 2014 22:37]
Fernando Gonzalez.Sanchez
Verified in Connector/NET 6.9.4, with EF6, VS2013 with the samples provided at Aug 29th.
[25 Sep 2014 23:50]
Fernando Gonzalez.Sanchez
The only exception is the EXAMPLE 1: which actually fails due to using ToString() not supported by Entity Framework (when Model.Id is int): LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression. at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.ContainsTranslator.TranslateContains(ExpressionConverter parent, Expression sourceExpression, Expression valueExpression) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.ContainsTranslator.Translate(ExpressionConverter parent, MethodCallExpression call) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq) at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert() at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery.ToTraceString() at System.Data.Entity.Internal.Linq.InternalQuery`1.ToString() at System.Data.Entity.Infrastructure.DbQuery`1.ToString() CodeFirstTests.cs(1654,0): at MySql.Data.Entity.CodeFirst.Tests.CodeFirstTests.TestContainsListWitConstant()
[25 Sep 2014 23:53]
Fernando Gonzalez.Sanchez
But can be forced to be DbConstantExpression with
var q = db.Movies.Where(p => strIds.Contains("two"));
[29 Sep 2014 16:45]
Fernando Gonzalez.Sanchez
Posted by developer: With the fix the LINQ to Entities queries no longer fail for the case that it contains a predicate using IList.Contains that has as argument a DbCastExpression, DbConstantExpression, DbParameterReferenceExpression. This fix will appear in MySQL Connector/NEt 6.7.6, 6.8.4, 6.9.5.
[29 Sep 2014 16:48]
Ryan Griffith
Fernando, Are you saying that you have completed this fix? If so, I would love to see the diff for my own personal knowledge.
[21 Oct 2014 22:16]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Connector/NET 6.7.6 / 6.8.4 / 6.9.5 releases, and here's the changelog entry: LINQ to Entities queries failed for the cases that contained a predicate using "IList.Contains" with an argument of "DbCastExpression, DbConstantExpression, DbParameterReferenceExpression". Thank you for the bug report.
[21 Oct 2014 22:16]
Philip Olson
Fixed as of the upcoming MySQL Connector/NET 6.7.6 / 6.8.4 / 6.9.5 releases, and here's the changelog entry: LINQ to Entities queries failed for the cases that contained a predicate using "IList.Contains" with an argument of "DbCastExpression, DbConstantExpression, DbParameterReferenceExpression". Thank you for the bug report.
[22 Oct 2014 15:54]
Fernando Gonzalez.Sanchez
Hi Ryan,
The change is:
--- a/Source/MySql.Data.Entity/Fragments/SqlFragment.cs
+++ b/Source/MySql.Data.Entity/Fragments/SqlFragment.cs
@@ -195,7 +195,7 @@ namespace MySql.Data.Entity
internal class InFragment : NegatableFragment
{
public List<LiteralFragment> InList;
- public ColumnFragment Argument;
+ public SqlFragment Argument;
--- a/Source/MySql.Data.Entity/Generators/SqlGenerator.cs
+++ b/Source/MySql.Data.Entity/Generators/SqlGenerator.cs
@@@ -25,16 -25,10 +25,16 @@@ using System.Diagnostics
using System.Text;
using System.Data;
using System.Collections.Generic;
-using System.Data.Common.CommandTrees;
-using System.Data.Metadata.Edm;
using MySql.Data.MySqlClient;
using System.Globalization;
+#if EF6
+using System.Data.Entity.Core.Common.CommandTrees;
+using System.Data.Entity.Core.Metadata.Edm;
+#else
+using System.Data.Common.CommandTrees;
+using System.Data.Metadata.Edm;
+#endif
+
namespace MySql.Data.Entity
{
@@@ -233,26 -227,6 +233,26 @@@
return expression.Argument.Accept(this);
}
+#if EF6
+ public override SqlFragment Visit(DbInExpression expression)
+ {
- ColumnFragment cf = Visit(expression.Item as DbPropertyExpression) as ColumnFragment;
++ SqlFragment sf = expression.Item.Accept(this);
+ InFragment inf = new InFragment();
- inf.Argument = cf;
++ inf.Argument = sf;
+ for( int i = 0; i < expression.List.Count; i++ )
+ {
+ LiteralFragment lf = Visit( expression.List[ i ] as DbConstantExpression ) as LiteralFragment;
+ inf.InList.Add( lf );
+ }
+ return inf;
+ }
+
+ public override SqlFragment Visit(DbLambdaExpression expression)
+ {
+ throw new NotImplementedException();
+ }
+#endif
+
Description: Given the following code below, an exception will be thrown: ---------------------------------------------------------------------------- var context = new Models.MyDbContext(); List<string> strIds = new List<string>(new[] { "17143" }); // Works context.projs.Where(model => model.id.ToString() == "17143").ToList(); // Works context.projs.Where(model => strIds.Cast<long>().Contains(model.id)).ToList(); // Throws error // Note: "id" is a long context.projs.Where(model => strIds.Contains(model.id.ToString())).ToList(); ---------------------------------------------------------------------------- Exception: Object reference not set to an instance of an object. Backtrace: at MySql.Data.Entity.SqlGenerator.Visit(DbPropertyExpression expression) at MySql.Data.Entity.SqlGenerator.Visit(DbInExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbInExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) at MySql.Data.Entity.SqlGenerator.VisitBinaryExpression(DbExpression left, DbExpression right, String op) at MySql.Data.Entity.SqlGenerator.Visit(DbAndExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbAndExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) at MySql.Data.Entity.SelectGenerator.Visit(DbFilterExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbFilterExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree) at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory) at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver) at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator) at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5() at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at WindowsFormsApplication1.Form1..ctor() in c:\Users\ryan\Documents\Visual Studio 2012\Projects\WindowsFormsApplication1\Form1.cs:line 41 at WindowsFormsApplication1.Program.Main() in c:\Users\ryan\Documents\Visual Studio 2012\Projects\WindowsFormsApplication1\Program.cs:line 19 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() How to repeat: var context = new Models.MyDbContext(); List<string> strIds = new List<string>(new[] { "17143" }); // Throws error // Note: "id" is a long context.projs.Where(model => strIds.Contains(model.id.ToString())).ToList(); Suggested fix: Cast the array to the type for comparison: context.projs.Where(model => strIds.Cast<long>().Contains(model.id)).ToList();