How to make composite key based on 2 foreign keys in Entity Framework?

Question

Is it possible to do this?

If so What is syntax to define such scenario in model

Table 1

Key 1

Table 2

Key 2

Table 3

composite of Key 1, Key 2

Possible duplicate of [Mapping composite keys using EF code first](https://stackoverflow.com/questions/19792295/mapping-composite-keys-using-ef-code-first) — CodeNotFound, May 20 '18 at 14:42
Not exactly. I have tried that, but problem is, relationship constraint with their respected table is missing. — S P, May 20 '18 at 14:59
If you are saying Table 3 is not relating to the other two tables, then you need to address your FK mappings. You can do that a few ways. By convention if your key is named Table1Id it will be a FK to table 1. You could also use a `ForeignKey` attribute or fluent code. [Here is an example](https://adrianscorner.wordpress.com/2014/04/04/designing-a-many-to-many-relationship-with-additional-fields-using-entity-framework/) similar to your question. Helps to show actual models BTW. — Steve Greene, May 20 '18 at 16:09

score 0 · Answer 1 · answered May 20 '18 at 20:02

Formally, it's not mandatory to have foreign key constraints in database - you need foreign keys only. If not having FK constraints is your case, then you, still, can easily reproduce this structure in EF (you didn't mention EF version, so I'm using EF6).

1. Connection string

You need to enable MARS connection to avoid this error when using explicit loading (see item 4):

<configuration>
    <connectionStrings>
        <add name="StackOverflowContext"
             providerName="System.Data.SqlClient"
             connectionString="Server=(localdb)\mssqllocaldb;Database=StackOverflow;Trusted_Connection=Yes;MultipleActiveResultSets=True;" />
    </connectionStrings>
</configuration>

2. T-SQL to create test tables and seeding:

create table dbo.Table1 (Table1Id int primary key);
create table dbo.Table2 (Table2Id int primary key);
create table dbo.Table3 (Table1Id int, Table2Id int);

insert into dbo.Table1 values (10), (20);
insert into dbo.Table2 values (30), (40);
insert into dbo.Table3 values (10, 30);
insert into dbo.Table3 values (20, 40);

3. EF context and classes

public class StackOverflowContext : DbContext
{
    public DbSet<Table1> Table1 { get; set; }
    public DbSet<Table2> Table2 { get; set; }
    public DbSet<Table3> Table3 { get; set; }
}

[Table("Table1")]
public class Table1
{
    public int Table1Id { get; set; }
}

[Table("Table2")]
public class Table2
{
    public int Table2Id { get; set; }
}

[Table("Table3")]
public class Table3
{
    // Composite key of two keys
    [Key, Column(Order = 1)]
    public int Table1Id { get; set; }
    [Key, Column(Order = 2)]
    public int Table2Id { get; set; }

    // Navigation properties
    public Table1 Table1 { get; set; }
    public Table2 Table2 { get; set; }
}

4. Get data from Table3

Now fetch data from Table3, but not by directly reading properties Table1Id and Table2Id, but through navigation properties. For this to work, I use explicit loading:

private void OnRun(object sender, EventArgs e)
{
    using (var db = new StackOverflowContext())
    {
        var t3 = db.Table3;
        foreach (Table3 t in t3)
        {
            // Explicit loading
            db.Entry(t).Reference(p => p.Table1).Load();
            // Get Table1Id through navigation property
            int id = t.Table1.Table1Id;
        }
    }
}

Hope this will clear the picture!

How to make composite key based on 2 foreign keys in Entity Framework?

1 Answers1