STW STW - 1 month ago 6
C# Question

How can I map a single Property as part of multiple compound Foreign Keys using Entity Framework 6?

I'm setting up an EF Code First mapping to a legacy database. The object model is a 1-to-1 of the database schema (no trying to pretty-up the schema).

The database has a table with two compound Foreign Keys, and one column is used in both Foreign Keys. Here's a pseudo-sample:

CREATE TABLE This_One
(
CommonColumn INT NOT NULL, -- NOTE: This column is part of both Compound FK's
ColumnOne INT NOT NULL,
ColumnTwo INT NOT NULL,

CONSTRAINT FK_ONE FOREIGN KEY (CommonColumn, ColumnOne)
REFERENCES Other_One (CommonColumn, ColumnOne),
CONSTRAINT FK_Two FOREIGN KEY (CommonColumn, ColumnTwo)
REFERENCES Other_Two (CommonColumn, ColumnTwo)
);


For my class-mappings I've been using Data Annotations. The
ForeignKeyAttribute
that doesn't
AllowMultiple
on it's
AttributeUsage
, so a property can only have a single
[ForeignKey]
applied to it, and the attribute only accepts a single name.

// Can't apply the compound keys this way
[Table("This_One")]
public class FKOnProperty
{
public int Common {get;set;}

[ForeignKey(nameof(OtherOne))] // [ForeignKey(nameof(OtherTwo))]
public int One {get;set;}

...

public OtherOne OtherOne {get;set;}

public OtherTwo OtherTwo {get;set;}
}

// Or this way
[Table("This_One")]
public class FKOnNavigation
{
public int Common {get;set;}

public int One {get;set;}

public int Two {get;set;}

[ForeignKey(nameof(One))] // [ForeignKey(nameof(Two))]
public OtherOne OtherOne {get;set;}

...
}


In a nutshell, I can't use the attribute to specify that the
CommonColumn
is part of two keys, and since both keys are compound I can't apply it on the navigation property.

How can I map two compound foreign keys that have a common property? Or is it not possible with Data Annotations?

Answer

You can apply ForeignKey attribute on the navigation properties using comma separated string with the property names:

public class This_One
{
    [Key, Column(Order = 1)]
    public int CommonColumn { get; set; }
    [Key, Column(Order = 2)]
    public int ColumnOne { get; set; }
    [Key, Column(Order = 3)]
    public int ColumnTwo { get; set; }

    [ForeignKey("CommonColumn,ColumnOne")]
    public Other_One Other_One { get; set; }
    [ForeignKey("CommonColumn,ColumnTwo")]
    public Other_Two Other_Two { get; set; }
}

Excerpt from the ForeignKey constructor documentation:

If you add the ForeigKey attribute to a foreign key property, you should specify the name of the associated navigation property. If you add the ForeigKey attribute to a navigation property, you should specify the name of the associated foreign key(s). If a navigation property has multiple foreign keys, use comma to separate the list of foreign key names.

Comments