CODEWITHSUNDEEP
c#.netormentity-framework-6
STW
STW

Reputation: 46366

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?

Upvotes: 2

Views: 579

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

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.

Upvotes: 1

Related Questions