LINQ to entities - Filtering data with IQueryable.Contains

Question

I have been struggling with a problem for some time. I'm currently moving an old project from entity framework to entity framework core 2.2.4.

In this project, the user can define a lot of search criterias on a web page before he does the request of the data to the server. I do not enter into much details about the query but it links 5 tables where 2 of the tables contains a list of dynamic name+value parameters (vertically stored in the table). Those parameters are associated to their parent table (entity). The query returns a list of unique identifiers. They represent the unique key to one of the parent table.

For my ease and due to the complexity to translate a such request to a LINQ query, i've decided to first construct dynamically the query with the search criterias in a c# string.

In the previous version of the EntityFramework, i've achieved that with:

List<string> arrFilteredGlobalId = _dbContext.Database.SqlQuery<string>("select GLOBALID from.....").ToList<string>();

Then i can do:

var full = from bundle in _dbContext.BUNDLE
                where arrFilteredGlobalId.Contains(bundle.GLOBALID)
                select bundle;
int num = full.Count(); // <= works perfectly

In the fresh new version of EntityFramework Core, context.Database.SqlQuery does not exist anymore. I had to do the following in order to achieve the same logic:

1. Declare a new class DBGlobalIdentifier with a single property

public class DBGlobalIdentifier
{
    public string GLOBALID { get; set; }
}

2. In my context class, i've declared a DbQuery object typed with my class DBGlobalIdentifier previously defined.

public virtual DbQuery<DBGlobalIdentifier> Identifiers { get; set; }

3. Finally, i do

IQueryable<DBGlobalIdentifier> arrFilteredGlobalId =  this._context.Identifiers.FromSql("select GLOBALID from.....");

// DBGlobalIdentifier test = arrFilteredGlobalId.First(); <== works perfectly.

var full = from bundle in _context.Bundles
                where arrFilteredGlobalId.Contains(new DBGlobalIdentifier() { GLOBALID = bundle.GLOBALID })
                select bundle;

int num = full.Count(); // <= it fails and returns an exception (see here under)

Exception returned:

An unhandled exception occurred while processing the request.
NullReferenceException: Object reference not set to an instance of an object.
Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizer.TryOptimizeContains(ResultOperatorBase resultOperator, QueryModel queryModel)

NullReferenceException: Object reference not set to an instance of an object.
Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizer.TryOptimizeContains(ResultOperatorBase resultOperator, QueryModel queryModel)
Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizer.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, int index)
Remotion.Linq.Clauses.ResultOperatorBase.Accept(IQueryModelVisitor visitor, QueryModel queryModel, int index)
Remotion.Linq.QueryModelVisitorBase.VisitResultOperators(ObservableCollection<ResultOperatorBase> resultOperators, QueryModel queryModel)
Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.TransformingQueryModelExpressionVisitor<TVisitor>.VisitSubQuery(SubQueryExpression expression)
Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
Remotion.Linq.Clauses.WhereClause.TransformExpressions(Func<Expression, Expression> transformation)
Remotion.Linq.QueryModel.TransformExpressions(Func<Expression, Expression> transformation)
Microsoft.EntityFrameworkCore.Query.Internal.QueryOptimizer.Optimize(QueryCompilationContext queryCompilationContext, QueryModel queryModel)
Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel, bool asyncQuery)
Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.OptimizeQueryModel(QueryModel queryModel, bool asyncQuery)
Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor<TResult>(QueryModel queryModel)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore<TResult>(Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger<Query> logger, Type contextType)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<>c__DisplayClass13_0<TResult>.<Execute>b__0()
Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore<TFunc>(object cacheKey, Func<Func<QueryContext, TFunc>> compiler)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute<TResult>(Expression query)
System.Linq.Queryable.Count<TSource>(IQueryable<TSource> source)

An idea about my problem?

Thank you! Fred

Answer 1

The cause of the problem is not quite interesting - the exception call stack indicates EF Core query translator bug (there are still many). Most likely caused by the (unexpected) usage of new DBGlobalIdentifier inside the Contains.

The idea with query type is correct. Since select raw SQL queries are composable, the solution is to extract back the value using Select and then use regular Contains:

var arrFilteredGlobalId = _context.Identifiers
    .FromSql("select GLOBALID from.....")
    .Select(x => x.GLOBALID); // <--

var full = from bundle in _context.Bundles
           where arrFilteredGlobalId.Contains(bundle.GLOBALID)
           select bundle;