Problem returning Entity models: "the entity or complex type ... cannot be constructed in a LINQ to Entities query"

Question

Problem returning Entity models: "the entity or complex type ... cannot be constructed in a LINQ to Entities query"

I am trying to return a list of the objects generated by 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'm trying somehow not to create a class for each different type of query i do with different columns in EF... The method looks 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_CLIENT are models, within Model1.edmx (inside the Model1.tt), which were generated by the Entity at the time of Database First.

I have seen on the net in some places that this is not possible, but I did not 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; }

}

3

c#entity-framework linq entity-framework-6 linq-to-entities

Author: Maniero, 2014-07-03

Source

1 answers

score 2 · Accepted Answer

The solution suggested by Harry Potter, is actually the most practical and most suggested, however, complementing the form he began 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 retrieve an anonymous type list and then convert it to your desired type.

I believe that only in cases where you want a minimum amount of columns among many existing, or if you have a column with a large volume of data in the same table, such as a blob, for example, it is that it would be recommended to use this last way, so it would save you from searching for such a larger volume of data that you would not even make use of.