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?

Answer 1

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)