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

Question

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)

What happens if, instead of select new {g.Key.DocumentId}).Contains(...) you try select 1).Any() — Charlieface
– Charlieface, Commented May 10, 2021 at 11:32

Ivan Stoev · Accepted Answer · 2021-05-10 11:52:59Z

2

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)

answered May 10, 2021 at 11:52

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Uwe Keim Over a year ago

Thanks, Ivan, this seems to work. I now get an error 'Data is Null. This method or property cannot be called on Null values.' at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull() when materializing the query (e.g. calling .ToList() on the result). Since I thought I already check for null I'm not sure on why this occurs now.

Uwe Keim Over a year ago

Seems that your suggestion there applies here, too.

Ivan Stoev Over a year ago

Yeah, that's a separate issue. You should be getting it even if you do simply db.Document.ToList(). You have to find which Document property is marked/using non nullable type and has null value in the database.

Uwe Keim Over a year ago

Actually it was an int that should have been an int? and also a DateTime that should have been an DateTime?. See this answer.

Ivan Stoev Over a year ago

string marked as [Required] or int? mapped to int doesn't matter - EF does not expect null coming from database. And the class type or atribute or fluent config must be corrected to reflect the database table column nullability.

zXynK · Accepted Answer · 2021-05-10 11:48:58Z

2

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

edited May 10, 2021 at 11:48

answered May 10, 2021 at 11:30

zXynK

1,3801 gold badge19 silver badges43 bronze badges

2 Comments

Uwe Keim Over a year ago

Are you sure this compiles (here in my program, it doesn't)? Seems that in select k.DocumentId the k is unknown.

zXynK Over a year ago

edited the selected statement. you can select d

Tomas Chabada · Accepted Answer · 2021-05-10 11:42:39Z

1

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;

answered May 10, 2021 at 11:42

Tomas Chabada

3,0391 gold badge20 silver badges19 bronze badges

Comments

mccuna · Accepted Answer · 2021-05-10 11:48:05Z

1

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.

answered May 10, 2021 at 11:48

mccuna

7611 gold badge10 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

My question

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My question

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related