Problem returning Entity models: "The Entity or Complex type ... cannot be constructed in a LINQ to Entities query"

Question

I’m trying to return a list of objects generated by the Entity Framework Database First but I get this error:

The Entity or Complex type 'leaosites04Model.TB_LEMBRETES' cannot be constructed in a LINQ to Entities query.

I am trying somehow not to create a class for each different type of query I do with different columns in EF... The method is like this:

public List<TB_LEMBRETES> getLembretes(int? situacao)
{
    try
    {
        using (dbEmpEntities db = new dbEmpEntities())
        {
            List<TB_LEMBRETES> result = 
                (from l in db.TB_LEMBRETES
                 join c in db.TB_CLIENTE on l.TB_CLIENTE.id_cliente equals c.id_cliente
                 where l.situacao == 0
                 select new TB_LEMBRETES
                 {
                    dt_lembrete = l.dt_lembrete,
                    obs = l.obs,
                    TB_CLIENTE = new TB_CLIENTE() { id_cliente = c.id_cliente, nome = c.nome }
                 }).ToList();

            return result;
        }
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

TB_LEMBRETE and TB_CLIENTE are models within Model1.edmx (within Model1.tt), which were generated by the Entity at the time of Database First.

I’ve seen on the net in some places that this is not possible, but I don’t understand why!

Below are the models:

[Table("leaosites04.TB_LEMBRETES")]
public partial class TB_LEMBRETES
{
    [Key]
    public int id_lembrete { get; set; }

    public int? id_cliente { get; set; }

    public DateTime? dt_lembrete { get; set; }

    [Column(TypeName = "text")]
    [StringLength(65535)]
    public string obs { get; set; }

    public virtual TB_CLIENTE TB_CLIENTE { get; set; }
}


[Table("leaosites04.TB_CLIENTE")]
public partial class TB_CLIENTE
{
 public TB_CLIENTE()
 {
     TB_LEMBRETES = new HashSet<TB_LEMBRETES>();
     TB_TELEFONES = new HashSet<TB_TELEFONES>();
 }

 [Key]
 public int id_cliente { get; set; }

 [Required]
 [StringLength(100)]
 public string nome { get; set; }

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

}

Answer 1

The solution suggested by Harry Potter is really the most practical and most suggested, but, complementing the way it started to do, it should look something like this:

public List<TB_LEMBRETES> getLembretes(int? situacao)
{
    try
    {
        using (dbEmpEntities db = new dbEmpEntities())
        {
            var resultTipoAnonimo = 
                (from l in db.TB_LEMBRETES
                 join c in db.TB_CLIENTE on l.TB_CLIENTE.id_cliente equals c.id_cliente
                 where l.situacao == 0
                 select new 
                 {
                    dt_lembrete = l.dt_lembrete,
                    obs = l.obs,
                    id_cliente = c.id_cliente, 
                    nome = c.nome
                 }).ToList();

            List<TB_LEMBRETES> result =
                resultTipoAnonimo.Select(r => new TB_LEMBRETES()
                {
                    dt_lembrete = r.dt_lembrete,
                    obs = r.obs,
                    TB_CLIENTE = new TB_CLIENTE() { id_cliente = r.id_cliente, nome = r.nome }
                }).ToList();

            return result;
        }
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

This way, you recover an anonymous type list and then convert to your desired type.

I believe that only in cases where you want a minimum amount of columns among many existing ones, or if you have a column with a large volume of data in the same table, such as a blob, for example, it is recommended to use the latter form, so I’d save on searching for such a large volume of data that I wouldn’t even use.