EF Core 5 - LINQ cannot be translated

Question

Well, I know that's a lot of questions about, but I didn't found a answer for my problem yet.

I have rules in entity classes:

class Evento:

public virtual ICollection<Sessao> Sessao { get; set; }
public bool Ativo()
{
    return DataPublicacao <= DateTime.Today
                   && (!DataFim.HasValue || DataFim.Value >= DateTime.Today)
                   && Sessao.Any(sessao => sessao.Ativa());
}

class Sessao:

public bool Ativa() => Status == StatusSessao.Ativa && (Recorencia && DataInicio <= DateTime.Today || (!Recorencia && DataInicio <= DateTime.Today && DataFim >= DateTime.Today));
//I had try to put DateTime.Today in a variable, but same exception

the LINQ code:

var cardsEventos = await Consulta //DbSet<Evento>
            .Include(x => x.Sessao)
            .Where(ev => ev.Ativo())
            .Where(x => x.Destaque) //...

When call the method, it throws the exception with that message:

System.InvalidOperationException: The LINQ expression 'DbSet<Evento>()
    .Where(e => e.Ativo())' could not be translated.

But if i put same rules directly on linq, it works.

All rules are same in the another parts of the software, so my think is have just one point of failure of these rules, how can I do that?

The answer to all similar question is/was that custom method calls cannot be translated to SQL, not sure what "solution" are you looking for. If you want translation, find and use some 3rd party library which allows injection/replacement of parts of the query expression tree - AutoMapper, LINQKit, NeinLinq, DelegateDecompiler etc. — Ivan Stoev, Sep 05 '22 at 17:23
no, it shouldn't call the method until you executed query. You're trying to execute this code on DB-side - but DB doesn't know anything about your functions in the code and EF doesn't know how to translate e.Ativo() to SQL query — Michael Kokorin, Sep 05 '22 at 17:33
For translating to the SQL it should not call method, but understand method body, which unfortunately is not available for LINQ translator. — Svyatoslav Danyliv, Sep 05 '22 at 17:36
I think you can find your answer in this question: https://stackoverflow.com/questions/68737681/the-linq-expression-could-not-be-translated-either-rewrite-the-query-in-a-form — Ashkan_Noori, Sep 05 '22 at 17:39
Well, I undestand, I'll think in another solution to do that and share if I have a elegant way. Thank you guys. — Kross, Sep 05 '22 at 17:39
@Kross, I can show solution with LINQKit, if you don't mind. — Svyatoslav Danyliv, Sep 05 '22 at 17:43

Svyatoslav Danyliv · Accepted Answer · 2022-09-05T18:50:02.107

I would suggest to use LINQKit. It needs configuring DbContextOptions:

builder
    .UseSqlServer(connectionString) // or any other Database Provider
    .WithExpressionExpanding();     // enabling LINQKit extension

Define your methods in the following way. We just need Expression Tree from them:

class Evento
{
    ... // other  properties 

    public virtual ICollection<Sessao> Sessao { get; set; }

    [Expandable(nameof(ActivoImpl))]
    public bool Ativo()
    {
        throw new InvalidOperationException("Server side only method");
    }

    // this method will be invoked by LINKQKit and LambdaExpression 
    // will be injected into final Expression Tree before passing to EF Core
    private static Expression<Func<Evento, bool>> ActivoImpl()
    {
        // for instance methods `this` is represented as first lambda parameter 
        return evento => evento.DataPublicacao <= DateTime.Today
            && (!evento.DataFim.HasValue || evento.DataFim.Value >= DateTime.Today)
            && evento.Sessao.Any(sessao => sessao.Ativa());
    }
}

class Sessao
{
    ... // other  properties 

    [Expandable(nameof(ActivaImpl))]
    public bool Ativa()
    {
        throw new InvalidOperationException("Server side only method");
    }

    private static Expression<Func<Sessao, bool>> ActivaImpl()
    {
        return sessao => sessao.Status == StatusSessao.Ativa 
            && (sessao.Recorencia && sessao.DataInicio <= DateTime.Today 
                || (!sessao.Recorencia && sessao.DataInicio <= DateTime.Today && sessao.DataFim >= DateTime.Today)
            );
    }
}

Then your LINQ query should work without any changes:

var cardsEventos = await Consulta //DbSet<Evento>
    .Include(x => x.Sessao)
    .Where(ev => ev.Ativo())
    .Where(x => x.Destaque)

We think almost the same ideia. I just make you private method as public and use like this: var cardsEventos = await Consulta .AsExpandable() .Where(Evento.Ativo()) .Where(Evento.EventoPublicado()) and Evento.Ativo is: public static Expression> Ativo() => e => e.DataPublicacao <= DateTime.Today && (!e.DataFim.HasValue || e.DataFim.Value >= DateTime.Today) that way, works like a charm, I really appreciate your help, thanks! — Kross, Sep 05 '22 at 20:54

score 0 · Answer 2 · answered Sep 05 '22 at 18:11

You might want to rewrite your code as

the LINQ code:

var ativa = sessao.Ativa();  
var cardsEventos = await Consulta //DbSet<Evento>
            .Include(x => x.Sessao)
            .Where(ev => DataPublicacao <= DateTime.Today
                   && (!DataFim.HasValue || DataFim.Value >= DateTime.Today)
                   && Sessao.Any(sessao => ativa))
            .Where(x => x.Destaque)

Just replaced the required code to show the change needs to be done. OP might need to reshuffle the variables and properties as per need.

Linq to Entities does not allow methods, As all the conditions needs to be mapped to SQL equivalent and for C# method there is none.

It would be easier if you get the return values in the variables and use those variables in the query instead.

It was similar to this, but the where clause was replicated in multiples points, if I need to change this clause, I have to search all lines that use this, that's why I want to put this in just one place — Kross, Sep 05 '22 at 18:37

EF Core 5 - LINQ cannot be translated

2 Answers2