A conflict in exposing a foreign key as a property during mappings

Question

I have the following table structure:

Affiliations

Id    Party1    Party2
1     100       200
2     300       400

And a table referenced through the FKs of Affiliations

Parties

Id    Name
100   Hank
200   Ted
300   Kim
400   Joe

Using my Party entity (not shown), I define a relationship between the tables using Fluent API:

modelBuilder.Entity<Party>()
            .HasMany(c => c.LeftPartyAffiliations)
            .WithRequired()
            .Map(m => m.MapKey("Party1"));

modelBuilder.Entity<Party>()
            .HasMany(c => c.RightPartyAffiliations)
            .WithRequired()
            .Map(m => m.MapKey("Party2"));

The party entity maps left and right relationships to their respective properties, LeftPartyAffiliations and RightPartyAffiliations. By defining the rule above, EF automatically adds Party1 and Party2 to my Affiliations table.

My problem lies in the fact that while the values in each affiliation map correctly, I don't have a way of knowing what the affiliation Id on the left and right sides, as I am mapping these tables to my columns through my Party relationship.

For instance, I would like to be able to access the actual Party1 and Party2 Id's via my Affiliation entity:

public class PartyAffiliation
{
    ...

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

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

    ...
}

...but this is impossible as I am already defining my relationship through mapping to my Left and Right collections, from Party, via Fluent API.

A perfect situtation would allow me to do the Fluent API mappings, which would create the columns and relationships as well as allowing me to access my Party1 and Party2 Id's via the physical columns defined in my Affiliation mapping.

If I attempt to define the properties phyically in the entity as well as in Fluent API, I get:

Each property name in a type must be unique. Property name was already defined 'xxx'

Is there a workaround to this issue or a way of mapping a column to multiple properties without physically adding an additional field to the database?

Wouldn't it be easier to change the structure of the Affiliations table to be generic? With the following fields: Id, PartyId, Type. That way you could filter by the third column and you could ad as many as you'd like (party1, party2, party3, ..., n) instead of having two fixed columns — amhed, Feb 12 '13 at 15:08
@amhed I should have noted, I don't control the database structure. I have essentially used code first to create the structure based off designs out of my control. — George Johnston, Feb 12 '13 at 16:04
Does the database already have the keys in place? why not use a database-first approach? — amhed, Feb 12 '13 at 17:27

score 1 · Accepted Answer · answered Feb 12 '13 at 17:45

You can use HasForeignKey instead of MapKey:

modelBuilder.Entity<Party>()
    .HasMany(c => c.LeftPartyAffiliations)
    .WithRequired()
    .HasForeignKey(pa => pa.Party1)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Party>()
    .HasMany(c => c.RightPartyAffiliations)
    .WithRequired()
    .HasForeignKey(pa => pa.Party2)
    .WillCascadeOnDelete(false);

It maps a property in your model class to a foreign key column in the database. Disabling cascading delete is necessary here (at least for one of the two relationships), otherwise you'd have a multiple cascading delete path from Party to PartyAffiliation which is forbidden in SQL Server.

A conflict in exposing a foreign key as a property during mappings

1 Answers1