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
+
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.067 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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();