Problem returning Entity models: "The entity or complex type ... can not be constructed in a LINQ to Entities query"

Question

Problem returning Entity models: "The entity or complex type ... can not be constructed in a LINQ to Entities query"

Navigation

#1 by (2 votes)

3

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

The entity or complex type 'leaosites04Model.TB_LEMBRETES' can not be constructed in a LINQ to Entities query.

I'm trying to somehow not create a class for each different type of query I make 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_LEBBRETE and TB_CLIENTE are models, within Model1.edmx (within Model1.tt), which were generated by 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; }

}

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

asked by anonymous 03.07.2014 / 10:49

1 answer

Best way to store images in a database Consume values from a webservice

score 2 · Accepted Answer

The solution suggested by Harry Potter, is actually the most practical and suggested, but complementing the way it started, 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);
    }
}

In 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 number 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, it would be recommended to use the latter way, so it would save you from searching for such a greater volume of data that you would not even want to use it.