Rewriting an SQL statement as a LINQ query results in runtime error

user_107625 0
Viewed 178

As a follow-up to this question of mine I have got this SQL:

SELECT Documents.* 
FROM Documents 
WHERE Documents.ID IN
(
  SELECT Keywords.DocumentID 
  FROM Keywords
  WHERE 
    Keywords.Keyword = 'KeywordA' OR 
    Keywords.Keyword = 'KeywordB' 
  GROUP BY Keywords.DocumentID 
  HAVING COUNT(Keywords.Keyword) = 2 
)

I did use Linqer to convert the query to C# to use with Entity Framework Core 5:

from Document in db.Document
where
    (from Keyword in db.Keyword
    where
      Keyword.Value == "KeywordA" ||
      Keyword.Value == "KeywordB"
    group Keyword by new {
      Keyword.DocumentId
    } into g
    where g.Count(p => p.Value != null) == 2
    select new {
      g.Key.DocumentId
    }).Contains(new { DocumentId = Document.DocumentId })
select Document

This compiles successfully, but upon running the query, I get an error:

The LINQ expression '<see below>' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The formatted LINQ expression from the above error message reads:

DbSet<Keyword>()
    .Where(k => k.Value == "KeywordA" || k.Value == "KeywordB")
    .GroupBy(
        keySelector: k => new { DocumentId = k.DocumentId }, 
        elementSelector: k => k)
    .Where(e => e
        .Count(p => p.Value != null) == 2)
    .Select(e => new { DocumentId = e.Key.DocumentId })
    .Any(p => p == new { DocumentId = EntityShaperExpression: 
        EntityType: Document
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .DocumentId })

I really do not understand what's wrong here. I could only imagine that Linqer is too old to generate valid C# code for use in EF Core 5.

My question

Could someone give me a hint on what I'm doing wrong here and how to resolve the issue? (I.e. how to rewrite the C# query)

entity-framework-core .net-core sql linq c#
4 Answers

It looks like the .Contains(new { DocumentId = Document.DocumentId }) part is your problem. It's having trouble translating it into an expression because Document hasn't been evaluated at that point.

If you have the FK setup, you could refactor it this way:

from d in db.Documents
where d.Keywords
       .Where(k => (new[] { "keyword A", "keyword B" }).Contains(k.Keyword))
       .Count() == 2
select d
user_5656330

EF Core query translation still doesn't support many LINQ constructs, and all that without documentation of what exactly is/is not supported really puts us on the trial-and-error path, thus it's not surprising that external tools cannot produce a "proper" translation.

In this particular case the problem is Contains call with complex argument (event though it is a class with single member). Because they support only Contains with primitive argument.

So the minimal change needed to make this work is to replace

select new
{
    g.Key.DocumentId
}).Contains(new { DocumentId = Document.DocumentId })

with

select g.Key.DocumentId).Contains(Document.DocumentId)
user_52025630

Try this:

var result =
    from Document in db.Document
    where
        (from Keyword in db.Keyword
         where
       Keyword.Value == "KeywordA" ||
       Keyword.Value == "KeywordB"
         group Keyword by Keyword.DocumentId
          into g
         where g.Count(p => p.Value != null) == 2
         select g.Key).Any(d => d == Document.DocumentId)
    select Document;
user_14277860

If having 2 separate queries, 1 for the keywords and 1 for the documents, is not an issue, this should work too:

var matchedDocumentIds = db.Keywords
    .Where(keyword => keyword.Keyword == "KeywordA" || keyword.Keyword == "KeywordB")
    .GroupBy(keyword => keyword.DocumentID)
    .Where(grp => grp.Count() > 2)
    .Select(grp => grp.Key);

var filteredDocs = db.Documents.Where(doc => matchedDocumentIds.Any(matchedDocId => matchedDocId == doc.ID));

This assumes there is a foreign key in place between the 2 tables.

user_53627800
Related