CODEWITHSUNDEEP
c#linqenumsentity-framework-core.net-6.0
jboo
jboo

Reputation: 374

Entity Framework Core with linq does not translate enum in a in query

I try to do a in query in a where clause with enum type TypeDocument and it's not working because it looks like linq expression cannot be translated to a SQL query. Linq cannot convert the enum in int for the comparison if I understand correctly:

documents.Where(doc => query.TypesExclus.Any(type => type != doc.TypeDocument))

I know I can do a .ToListAsync() before and than the where after but I prefer to let the database server handler the filter for better performance.

I have tried to add value conversion but this doesn't change the result. https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations

I get the following error:

System.InvalidOperationException : The LINQ expression 'type => (int)type == (int)EntityShaperExpression:
Sp.Domain.Entities.Document ValueBufferExpression: ProjectionBindingExpression: Outer IsNullable: False .TypeDocument' 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'.

Code:

public async Task<GetDocumentsResult> RetrieveAsync(GetDocumentRegimeQuery query)
{
    var documents = context.Documents
        .AsNoTracking()
        .Where(doc => doc.Regime.Id == query.RegimeId
                      && doc.AfficherSiteParticipant == true)
        .Select(d => new DocumentDto { Id = d.Id, Nom = d.Nom, TypeDocument = d.TypeDocument, DateSauvegarde = d.DateSauvegarde, Extension = d.Extension });

    if (query.TypesExclus.Any())
    {
        documents = documents.Where(doc => query.TypesExclus.Any(type => type != doc.TypeDocument));
    }

    return new GetDocumentsResult { Documents = await documents.ToListAsync() };
}

Upvotes: 1

Views: 1409

Answers (1)

Vivek Nuna
Vivek Nuna

Reputation: 1

Entity Framework Core does not support translating enum types to SQL.

You can try to convert the enum to an integer before performing the comparison.

documents = documents.Where(doc => query.TypesExclus.Any(type => (int)type != (int)doc.TypeDocument));

Upvotes: 2

Related Questions