CODEWITHSUNDEEP
c#entity-framework-4associationsone-to-many
Daniel Ferreira
Daniel Ferreira

Reputation: 43

Entity Framework 4 association with non-key columns/fields and different columns/field names

I'm building a new service with EF4 that connects to a legacy DB (that cannot be modified).

(This BD seems to have been built by a DB genius as you'll see... dirty job, I know, but I have to do it)

Basically, I have two tables (SegurosPassageiros and LocsPassageiros), with their keys that associates with other tables on the regular way. As shown in the below model, they don't have a physical association with PK/FK but they are linked by the non-key columns "SegurosPassageiros.CodPassageiro" and "LocsPassageiros.CodLocPassageiroInterno". Also, this association is one-to-many:

LocsPassageiros 1...* SegurosPassageiros

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

the tables:

 LocsPassageiros 
 ----------------------------------
 CodLocPassageiro (PK)   | int
 Nome                    | varchar
 CodLocPassageiroInterno | int
 ----------------------------------


 ----------------------------------
 SegurosPassageiros
 ----------------------------------
 CodSeguroPassageiro(PK)  | int
 CodPassageiro            | int
 ----------------------------------

The code (class "SeguroPassageiro" mapped to 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 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; }
}

Setting up the model:

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 dozens of trials, the closest I could reach was getting a List in the Passageiro object but with the wrong association: "SegurosPassageiros.CodPassageiro" and "LocsPassageiros.CodLocPassageiro" (instead of "LocsPassageiros.CodLocPassageiro*Interno* "). EF insists on getting the wrong association (getting the PK on LocsPassageiros).

Anyone know how can I get this association wrking in EF?

Upvotes: 4

Views: 1207

Answers (2)

sak
sak

Reputation: 312

This is a work around. (I wouldn't have posted this as answer, if I had privilege to post this as a comment.) Instead of using the Association directly, you can use a EF query and join both the entities in a Where.

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

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

In database you can build such relation only when the principal key is unique - it must be primary key or it must have unique constraint. If the database doesn't meet these requirements the relation is invalid. EF currently doesn't support unique constraints so the only way is to use primary key.

Upvotes: 3

Related Questions