The Entity or Complex type '*' cannot be constructed in a LINQ to Entities query

Question

I can’t do this?

public List<Filial> GetAll()
{
    //Mostra todos os registros incluindo os desativados para os Administradores
    var ret = _db.Filiais.AsNoTracking()
                 .Where(e => e.Visible)
                 .OrderBy(e => e.FilialNome)
                 .Select(x => new Filial
                 {
                     FilialId = x.FilialId,
                     FilialNome = x.FilialNome,
                     FilialEndereco = x.FilialEndereco,
                     FilialBairro = x.FilialBairro,
                     FilialFixPhone = x.FilialFixPhone
                  }).ToList();

    return ret;
}

the mistake

The Entity or Complex type 'Ccvalemixef.Infra.Data.Context.Affiliate' cannot be constructed in a LINQ to Entities query.

Answer 1

That’s right, you can’t. The mistake itself says

The Entity or Complex type 'Ccvalemixef.Infra.Data.Context.Affiliate' cannot be constructed in a LINQ to Entities query.

Free translation

The entity or complex type 'Ccvalemixef.Infra.Data.Context.Subsidiary' cannot be created in a query LINQ to Entitites

The correct thing is to bring the data to memory, using .ToList() and then build the objects.

Pay attention to always leave the ToList() after the Where() and of OrderBy() so that the data are filtered and ordered in the database and not in memory.

var ret = _db.Filiais.AsNoTracking()
             .Where(e => e.Visible)
             .OrderBy(e => e.FilialNome)
             .ToList()
             .Select(x => new Filial
             {
                 FilialId = x.FilialId,
                 FilialNome = x.FilialNome,
                 FilialEndereco = x.FilialEndereco,
                 FilialBairro = x.FilialBairro,
                 FilialFixPhone = x.FilialFixPhone
             }).ToList();