Lambda Linq Problem: LINQ to Entities does not recognize... get_Item(Int32). How to resolve?

Question

When trying to execute the loop for (time when query is executed) is giving error in my lambda expression:

var funcionarios = repository.Funcionarios
    .Include(x => x.Cargo)
    .Include(x => x.Bairro)
    .Include(x => x.Bairro.Cidade)
    .Include(x => x.Historicos)
    .Where(x => x.Historicos
        .Count(hist => hist.TipoHistoricoId == parametros.HistoricoManutencaoCargoId &&
            hist.DataRegistro.Year == AnoBase) > 0)
    .Where(x => x.Bairro != null && x.Bairro.Cidade != null)
    .OrderBy(x => x.Bairro.Cidade.Id)
    .ThenByDescending(x => x.Historicos[0].DataRegistro);

foreach (var func in funcionarios)
{
...
}

I’m getting the following error:

LINQ to Entities does not recognize the method 'Domain.Funcionariohistorico get_Item(Int32)' method, and this method cannot be Translated into a store Expression.

Domain.FuncionarioHistorico refers to the property Historicos of the Employee being carried by: .Include(x => x.Historicos).

Apparently my expression for the Join / Include is not correct, or need to store in variable some of the values used in it to then pass to the expression.

The parametros is a local variable that stores some system parameters, including the History Type that is used for job maintenance. HistoricoManutencaoCargoId is the property that accesses the Id of that kind.

If I need the model I add a small example because the current one is great.

How to solve this?

Answer 1

The Include awaits an object complex related to your Model: http://msdn.microsoft.com/en-us/library/gg696785(v=vs.113). aspx

Therefore, what causes the problem is not the Include, and yes the Count, that within a Where cannot be translated immediately. Hence the error.

In any case, this way that is written is weird. You can simplify to:

var funcionarios = repository.Funcionarios
    .Include(x => x.Cargo)
    .Include(x => x.Bairro)
    .Include(x => x.Bairro.Cidade)
    .Include(x => x.Historicos)
    .Where(x => x.Bairro != null && x.Bairro.Cidade != null)
    .OrderBy(x => x.Bairro.Cidade.Id)
    .ThenByDescending(x => x.Historicos[0].DataRegistro);

funcionarios = funcionarios.SelectMany(f => f.Historicos)
    .Where(hist => hist.TipoHistoricoId == parametros.HistoricoManutencaoCargoId &&
            hist.DataRegistro.Year == AnoBase)
    .Select(hist => hist.Funcionario)
    .ToList();