CODEWITHSUNDEEP
c#entity-frameworklinqef-core-5.0
Luca
Luca

Reputation: 363

Query cannot be translated

I have the following class:

class document
{
    int Id;
    List<Tag> Tags;
}

class Tag
{
    int Id;
}

I want to get all the documents that have at least one of the tags that the user selects.

I have written the following linq query:

List<int> tagIds = tags.Select (x => x.Id).ToList ();

query.Where (doc => tagIds.Any (x => doc.Tags.Select (y => y.Id).Contains (x)));

If I execute it against a list of documents, it works, but if I execute it against a sqlite database using efcore 5 I get the following error:

System.InvalidOperationException: 'The LINQ expression 'DbSet<DemaDocument>()
    .Where(d => __tagIds_0
        .Any(x => DbSet<Dictionary<string, object>>("DemaDocumentDemaTag")
            .Where(d0 => EF.Property<Nullable<int>>(d, "Id") != null && object.Equals(
                objA: (object)EF.Property<Nullable<int>>(d, "Id"), 
                objB: (object)EF.Property<Nullable<int>>(d0, "DocumentsId")))
            .Join(
                inner: DbSet<DemaTag>(), 
                outerKeySelector: d0 => EF.Property<Nullable<int>>(d0, "TagsId"), 
                innerKeySelector: d1 => EF.Property<Nullable<int>>(d1, "Id"), 
                resultSelector: (d0, d1) => new TransparentIdentifier<Dictionary<string, object>, DemaTag>(
                    Outer = d0, 
                    Inner = d1
                ))
            .Select(ti => ti.Inner.Id)
            .Any(p => p == x)))' 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.'

How can I rewrite the query using fluent LINQ so that it will work? Is it possible, or do I have to retrieve the documents in memory and then run the query? This would not be ideal, because the documents will grow over time...

Thanks in advance

Upvotes: 0

Views: 1658

Answers (1)

JonasH
JonasH

Reputation: 36729

.Contains should be translated as a SQL IN statement, i.e. x IN 1, 2, 3. This requires the list to be constant. In your example doc.Tags.Select (y => y.Id) is unique for each document, so it cannot be translated to a constant list.

What you are doing is more or less checking if two lists intersects, so we should be able to reverse the order of the two lists:

query.Where(doc => doc.Tags.Any(x => tagIds.Contains(x.Id)))

Now the tagIds is constant for the query, and the .Contains statement can be properly translated.

Upvotes: 3

Related Questions