0.. 1 to 1 modeling in the Entity Framework - Error The Entity or Complex

Question

I’m trying to make a query but returns the error:

Additional information: The Entity or Complex type 'WMB.CieloB.Model.Cielotoken' cannot be constructed in a LINQ to Entities query.

My consultation:

    var temp = (from tok in db.CieloTokens
             join rec in db.CieloRecorrencias
             on tok.CieloRecorrenciaId equals rec.CieloRecorrenciaId 
             where rec.ClienteId == IDC && tok.ClienteId == IDC && tok.CieloRecorrenciaId > 0
             select new CieloToken()).FirstOrDefault();

My 2 models (I removed some properties/attributes to decrease the post):

  public class CieloToken
    {
        [Key, ForeignKey("CieloRecorrencia")]
        [Column("int_ID")]
        public int CieloTokenId { get; set; }

        [Column("int_IDC")]
        public int ClienteId { get; set; }

        [Column("str_Token")]
        [MaxLength(100)]
        public string Token { get; set; }

        public int? CieloRecorrenciaId { get; set; }

        public virtual CieloRecorrencia CieloRecorrencia { get; set; }

    }

public class CieloRecorrencia
{
    [Key]
    [Column("int_ID")]
    public int CieloRecorrenciaId { get; set; }

    public int ClienteId { get; set; }
    public virtual CieloToken cieloToken { get; set; }
}

I tried to do with LAMBDA

    var teste_Capeta = db.CieloTokens
            .Include(i => i.CieloRecorrencia)
            .FirstOrDefault(w => w.ClienteId == IDC && w.CieloRecorrencia.ClienteId == IDC && w.CieloRecorrenciaId > 0);

There the value is null, and there are records.

NOTE: in Where I put 2 equal fields (Clienteid), of different tables only to ensure and in tests.

Answer 1

Your problem is time you make a select new CieloToken().

Try it this way:

var temp = (from tok in db.CieloTokens
             join rec in db.CieloRecorrencias
             on tok.CieloRecorrenciaId equals rec.CieloRecorrenciaId 
             where rec.ClienteId == IDC && tok.ClienteId == IDC && tok.CieloRecorrenciaId > 0
             select tok).FirstOrDefault();

Thus you will return the record that the database query returns. If you need Join information you can create an anonymous object.

Already the line where rec.ClienteId == IDC && tok.ClienteId == IDC may be the reason you don’t get results. You don’t need the property public int? CieloRecorrenciaId { get; set; }, the relationship mapping is already being done in the class CieloRecorrencia