Fluent API Map from a lookup table with a composite primary key to another table

Question

I have a class with a composite primary key that's used as a lookup table for 3 different tables. So, the primary key is a composite of the 3 corresponding foreign keys.

I want to use one of the keys in the composite key as a foreign key to another table.

here is the lookup table mapping

public MultiMap()
{
    ToTable("multi");
    HasKey(t => t.ParentId);
    HasKey(t => t.AttributeId);
    HasKey(t => t.ItemId);
    Property(t => t.ParentId).HasColumnName("parent_id");
    Property(t => t.AttributeId).HasColumnName("attribute_id");
    Property(t => t.ItemId).HasColumnName("item_id");    
}

I want to use ItemID as a foreign key to map to another table, but it's not working.

I'm trying to use this to map it

HasRequired(t => t.ListItem).WithMany().HasForeignKey(t => t.ItemId);

But it's not working.

I assume it's because it's trying to map the composite primary key to the foreign key. Which is just an int.

I'm getting the message of

Multiplicity is not valid in Role 'XXX' in relationship 'XXX'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be '1'.

How should I be doing the mapping?

Just for clarification: You're asking to have one of the parts of the composite key be referenced in a different table as a FK to this Multi table, correct? — Corey Adler, Jan 08 '15 at 22:33
@GertArnold Gah! such a silly mistake. Thanks, that did it. If you want to make this an answer, I'll mark it. — Smeegs, Jan 09 '15 at 12:14

score 1 · Accepted Answer · answered Jan 09 '15 at 21:36

If you want to map a compound primary key you should use the syntax...

HasKey(t => new {t.ParentId, t.AttributeId, t.ItemId});

...not three subsequent HasKey statements. Eaxh HasKey statement overwrites the previous one.

You can't use one attribute of a compound key as foreign key target, because it's not unique. If you want an entity to refer to ItemID only, you have to create a foreign key to the table of which ItemID is the primary key (Item I'd think).

Fluent API Map from a lookup table with a composite primary key to another table

1 Answers1