use parameter from method argument inside linq con

问题:

i have a problem using Contains() method with parameter coming from the method arguments.

i am using entity framework core 1.1 and mysql connector version 6.10.0-alpha.

i have this code:

public IEnumerable<Message> search(string content) {
    var bla = this.appDbContext.Messages.Where(x => x.Content.Contains("edit")).ToList();
    var bla1 = this.appDbContext.Messages.Where(x => x.Content=="edit").ToList();
    var bla2 = this.appDbContext.Messages.Where(x => x.Content==content).ToList();
    var bla3 = this.appDbContext.Messages.Where(x => x.Content.Contains(content)).ToList();
    ...

the first 3 lines works,

however, the fourth line (bla3) returns the following error:

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[0] An unhandled exception has occurred while executing the request System.InvalidOperationException: When called from 'VisitChildren', rewriting a node of type 'System.Linq.Expressions.Expression' must return a non-null value of the same type. Alternatively, override 'VisitChildren' and change it to not visit children of this type.

at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection'1 nodes, String callerName) at Microsoft.EntityFrameworkCore.Query.Expressions.SqlFunctionExpression.VisitChildren(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.VisitExtension(Expression node) at Microsoft.EntityFrameworkCore.Query.Expressions.SqlFunctionExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ConditionalRemovingExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.Expressions.LikeExpression.VisitChildren(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.VisitExtension(Expression node) at Microsoft.EntityFrameworkCore.Query.Expressions.LikeExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ConditionalRemovingExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitWhereClause(WhereClause whereClause, QueryModel queryModel, Int32 index) at Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection'1 bodyClauses, QueryModel queryModel) at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) --- End of stack trace from previous location where exception was thrown ---

why can't i use parameter from the method arguments inside Contains() linq expression?

and what can i do to be able to use it?

回答1:

apparently this is a real bug and not something i missunderstand in the way entity framework works.

we started talking about it here in entity framework's github issues board:

Wrong query generation when using Contains inside where predicate. #6687 https://github.com/aspnet/EntityFramework/issues/6687#issuecomment-272543460

and then it was branched over to mysql bugs forum:

Bug #84505 Using Contains method in expression with a variable throws exception in EF Core http://bugs.mysql.com/bug.php?id=84505

and to a new dedicated issue in entity framework's github issues board:

Query : possible error around conditional removing visitor and sql functions #7441 https://github.com/aspnet/EntityFramework/issues/7441

hope this is fixed soon.

回答2:

Here is my best guess for you.

When you use LINQ to SQL and pass a parameterized predicate to the Where clause, the compiler converts that predicate into an expression tree.

Your parameterized predicate is x => x.Content.Contains(content).

When the runtime is using an expression tree, there are additional constraints. You are seeing the exception that VisitAndConvert throws.

In the other three cases, I imagine that the compiler either does not need to use expression trees or can use less complicated ones, in which case you are not seeing the same error.

If the MySql provider cannot handle that complexity of expression trees, then you could filter the Messages in memory instead of in the database query. If there are a LOT of messages, though, this could fill up your memory, because you will be retrieving ALL the messages from the database.

this.appDbContext.Messages
    .ToList() // finish the call to the database
    .Where(x => x.Content.Contains(content)) // then filter the data
    .ToList();

回答3:

bug officially fixed in version 6.10.1: https://www.nuget.org/packages/MySql.Data/6.10.1-beta

https://bugs.mysql.com/bug.php?id=84505

[9 Feb 21:17] Christine Cole

Posted by developer:

Fixed as of the upcoming MySQL Connector/NET 6.10.1 release, and here's the changelog entry:

EF Core: Using the Contains method in an expression with a variable generated an exception.

Thank you for the bug report.

and here is the official release post:

http://insidemysql.com/mysql-connectornet-6-10-1-beta-has-been-released/

Bugs Fixed

• EF Core: Using the Contains method in an expression with a variable generated an exception. (Bug #25394204, Bug #84505)