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)

Answer 2

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.