Use Intersect inside Where clause

Question

I'm trying to select a subset of two lists of the same object as part of a where clause, and getting an error.

{"Expression of type 'System.Linq.IQueryable1[<>f__AnonymousType772[System.Int32,System.Int32]]' cannot be used for parameter of type 'System.Linq.IQueryable1[Entity.Entities.QuestionModuleQuestionDto]' of method 'System.Linq.IQueryable1[Entity.Entities.QuestionModuleQuestionDto] Where[QuestionModuleQuestionDto](System.Linq.IQueryable1[Entity.Entities.QuestionModuleQuestionDto], System.Linq.Expressions.Expression1[System.Func`2[Entity.Entities.QuestionModuleQuestionDto,System.Boolean]])' (Parameter 'arg0')"}

public async Task<bool> ValidateQuestions(List<QuestionModuleQuestion> questions)
{
    var questionModules = await _context.QuestionModule
        .Include(qm => qm.Questions)
        Where(qm =>
            qm.Questions.Select(q => new {q.QuestionId, q.Sequence})
                .Intersect(questions.Select(q => new {q.QuestionId, q.Sequence})).Any())
        .ToListAsync();

    return questionModules.Any(qm => qm.Questions.Count == questions.Count);
}

I rewrote the clause to look like the following, but I'm not too terribly fond of this approach.

var questionModules = await _context.QuestionModule.Include(qm => qm.Questions)
                .Where(qm => qm.Questions.Any(qmq => questions.Any(question =>
                    question.QuestionId == qmq.QuestionId && question.Sequence == qmq.Sequence)))
                .ToListAsync();

You can remove `Include` unless you want to load the related questions. `Include` is only used to eagerly load related entities, not to perform a JOIN. LINQ-to-EF queries are translated to SQL. A good looking LINQ query is no use if it results in an ugly SQL query. In any case, the database engine will simplify the generated SQL query when it compiles it into an execution plan. — Panagiotis Kanavos, May 12 '22 at 15:23
@PanagiotisKanavos I'm intentionally loading the subobjects here. — CBC_NS, May 12 '22 at 15:25
@GuruStron yes, but I feel with the double .Any() clauses there would be a performance hit. — CBC_NS, May 12 '22 at 15:26
As for why these queries don't work, they're trying to do something that can't be written in SQL - use local list values in a server-side query. `questions.Select(q => new {q.QuestionId, q.Sequence}))` is a local list of value pairs. There's no way to write a SQL query that performs an `INTERSECT` with local data. The only options would be to create a table-valued parameter that includes those pairs, or emit all the values in the query itself using row constructuros, ie `VALUES ( (1,'Blah1'), (2,'Blah2),...) questions(QuestionId,Seqence)` — Panagiotis Kanavos, May 12 '22 at 15:27
@PanagiotisKanavos OP claims that second one does work (so I wonder how and why). — Guru Stron, May 12 '22 at 15:28
The only way the second query would work is by either generating a TVP or materializing the list in the query using row constructors. — Panagiotis Kanavos, May 12 '22 at 15:29
@CBC_NS performance is determined by the SQL query, indexes and statistics, not the LINQ query. You'd have bad performance either way because `questions`, no matter how it's materialized, would have no indexes. `INTERSECT` itself would result in bad performance unless both columns were indexed on both sides — Panagiotis Kanavos, May 12 '22 at 15:31
@CBC_NS try using `ToQueryString()` on the query to see what the actual SQL query is. The operation you try to perform isn't an intersection, it's an attempt to load modules with questions that match a local list. If the questions were a table in the database you'd use a JOIN, not INTERSECT. Maybe, just maybe, EF Core was smart enough to convert the second query to a JOIN between the `Questions` table and the materialized values of the list. — Panagiotis Kanavos, May 12 '22 at 15:36
I don't believe that the second query works. EF doesn't translate local lists of objects into SQL. Which EF version is this? — Gert Arnold, May 12 '22 at 16:09

score 0 · Answer 1 · answered May 12 '22 at 16:18

EF Core has limited support for operations with local collections. You can use only Contains and very simple Any.

You can use this extension FilterByItems and rewrite query in the following way. Query refactored to be effective and I hope that I have not changed requirements.

public async Task<bool> ValidateQuestions(List<QuestionModuleQuestion> questions)
{
    var questionCount = questions.Count;
    var query = _context.QuestionModule
        Where(qm =>
            qm.Questions.AsQueryable()
                .FilterByItems(questions, (q, i) => q.QuestionId == i.QuestionId && q.Sequence == i.Sequence, true)
                .Count() == questionCount);

    return await questionModules.AnyAsync();
}

Use Intersect inside Where clause

1 Answers1