Relationship of Entity Framework 4 with non-core columns / fields and different columns / field names

Question

I am creating a new service with EF4 that connects to an outdated database (which cannot be modified).

(This BD, it seems, was built by a brilliant DB, as you will see ... dirty work, I know, but I have to do it)

Basically, I have two tables ( SegurosPassageiros and LocsPassageiros ), with their keys, which are associated with other tables in the usual way. As shown in the model below, they do not have a physical connection with PK / FK, but they are connected by the odd columns " SegurosPassageiros.CodPassageiro " and " LocsPassageiros.CodLocPassageiroInterno ". In addition, this one-to-many association:

LocsPassageiros 1 ... * SegurosPassageiros

I found many answers related to non-key association, but not both non-keys and different names in the same scenario.

tables:

LocsPassageiros ---------------------------------- CodLocPassageiro (PK) | int Nome | varchar CodLocPassageiroInterno | int ---------------------------------- ---------------------------------- SegurosPassageiros ---------------------------------- CodSeguroPassageiro(PK) | int CodPassageiro | int ---------------------------------- 

Code (class "SeguroPassageiro", mapped to the table "SegurosPassageiros"):

 public class SeguroPassageiro { [Key] public Int32 CodSeguroPassageiro { get; set; } .... (other fields) //tried this, no success //[ForeignKey("Passageiro")] public virtual Int32 CodLocPassageiroInterno { get; set; } //tried this annotation with no success [Association("Passageiro_Seguros", "CodPassageiro", "CodLocPassageiroInterno")] public virtual Passageiro Passageiro { get; set; } } 

Class "Passageiro" mapped to the table "LocsPassageiros":

 public class Passageiro { [Key] public Int32 CodLocPassageiro { get; set; } ... (other fields) //tried this, no success //[ForeignKey("Seguros")] public Int32 CodLocPassageiroInterno { get; set; } //tried these annotations with no success [ForeignKey("CodLocPassageiroInterno")] [Association("Passageiro_Seguros", "CodLocPassageiroInterno", "CodPassageiro")] public List<SeguroPassageiro> Seguros { get; set; } } 

Model setup:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); modelBuilder.Entity<Dominio.Aereo.Passageiro>().ToTable("LocsPassageiros"); modelBuilder.Entity<Dominio.Aereo.SeguroPassageiro>() .ToTable("SegurosPassageiros"); //Tried both lines below (together and separeted) with no success: //modelBuilder.Entity<Dominio.Aereo.SeguroPassageiro>().HasRequired(s => s.Passageiro).WithOptional(); //modelBuilder.Entity<Dominio.Aereo.Passageiro>().HasMany(p => p.Seguros).WithRequired(); //Tried "linking" the column "CodPassageiro" on table "SegurosPassageiros" to //column "CodLocPassageiroInterno" on table "LocsPassageiros". No success. modelBuilder.Entity<Dominio.Aereo.SeguroPassageiro>().Property(s => s.CodLocPassageiroInterno).HasColumnName("CodPassageiro"); } 

With this model, after several tens of tests, the closest I could achieve by getting a List in the Passageiro object, but with the wrong association: "SegurosPassageiros.CodPassageiro" and "LocsPassageiros.CodLocPassageiro" (instead of "LocsPassageiros.CodLocPassageiro" Interno * "). on getting the wrong association (getting PK on LocsPassageiros).

Does anyone know how I can get this connection in EF?

Answer 1

This is a job. (I would not post this as an answer if I had the privilege of posting it as a comment.) Instead of using the Association directly, you can use the EF request and attach both objects to Where.

Answer 2

Unfortunately this is not possible. EF can build a one-to-many relationship only on top of the PK defined in the underlying entity. In your case, only when LocsPassageiros.CodLocPassageiro used as the master key.

In the database, you can build such a relationship only when the master key is unique - it must be a primary key or must have a unique constraint. If the database does not meet these requirements, then the relation is invalid. EF does not currently support unique restrictions, so the only way is to use a primary key.