Composite primary key as shadow property in many-to-many relation

c# ef-fluent-api entity-framework-core

Question

I'm using .NET Core end Entity Framework core to build many-to-many relation between two entities. I've built join entity to fullfil the relation and based primary key on shadow properties like this :

Entity User :

public class User
{
    [Key]
    public int IDUser { get; set; }
    [Required]
    public string Forename { get; set; }

    public List<UserGroup> UsersGroups { get; set; }
}

Entity Group :

public class Group
{
    [Key]
    public int IDGroup { get; set; }
    [Required]
    public string GroupName { get; set; }

    public List<UserGroup> UsersGroups { get; set; }
}

Entity UserGroup :

public class UserGroup
{
    public Group Group { get; set; }
    public User User { get; set; }
}

DBcontext class :

public class DBContext : DbContext
{
    public DBContext(DbContextOptions<DBContext> options)
        : base(options)
    {

    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // shadow property - primary/foreign key
        modelBuilder.Entity<UserGroup>()
            .Property<int>("IDUser");

        // shadow property - primary/foreign key
        modelBuilder.Entity<UserGroup>()
            .Property<int>("IDGroup");

        // composite primary key based on shadow properties
        modelBuilder.Entity<UserGroup>()
            .HasKey( new string[]{ "IDUser", "IDGroup" });

        modelBuilder.Entity<UserGroup>()
            .HasOne(ug => ug.Group)
            .WithMany(g => g.UsersGroups)
            .HasForeignKey(???); //what to do here ?

        modelBuilder.Entity<UserGroup>()
            .HasOne(ug => ug.User)
            .WithMany(u => u.UsersGroups)
            .HasForeignKey(???); // what to do here ?

        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Group> Groups { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UserGroup> UserGroups { get; set; }
}

Now. How can I properly establish Foreign Key on UserGroup entity based on my shadow composite primary key ? I would like this shadow primary key to be foreign key simultaneously. I don't know how to refer to this shadow primary key now in order to make foreign key. I marked where I don't know what to do with question markes.

1
0
8/7/2017 6:26:15 PM

Accepted Answer

You are trying to create a many-to-many join table without defining any scalar properties and you are utilizing shadow property to configure the join table. For EF fluent API, where you have to refer to a shadow property you need to use string based methods. Due to lack of backing CLR property lambda expression doesn't work.

In your case, "what to do here" part is just use the string name of the property. e.g.

modelBuilder.Entity<UserGroup>()
    .HasOne(ug => ug.Group)
    .WithMany(g => g.UsersGroups)
    .HasForeignKey("IDGroup"); //what to do here ?

Same for the other relationship. This is general mechanism when you want to configure a shadow property as your foreign key property. Furthermore, configuring the shadow property in HasForeignKey does not require you to define the shadow property in advance, since EF can infer the type based on the properties on principal side of relationship. Though for HasKey you still need to declare the shadow properties since EF has no knowledge of types. (As you have done in your example)

EF Core also has convention to figure out FK property. One of the convention is to use property as FK if it has same name as the principal property. In your special case as above, since your principal side primary key property is named as IDGroup which is same as foreign key you are trying to configure, EF will use that by convention automatically. That means you can ignore configuring your relationship (as @David suggested). Also since EF discovers relationships based on navigations you can remove following piece of code fully from your application and it will create the same model.

modelBuilder.Entity<UserGroup>()
    .HasOne(ug => ug.Group)
    .WithMany(g => g.UsersGroups);
//.HasForeignKey(???); //what to do here ?

modelBuilder.Entity<UserGroup>()
    .HasOne(ug => ug.User)
    .WithMany(u => u.UsersGroups);
    //.HasForeignKey(???); // what to do here ?
2
8/7/2017 11:02:47 PM

Popular Answer

.HasForeignKey() declares Foreign Key Properties on your entity.

If you don't want Foreign Key Properties on your linking Entity (and you should have them), just omit the .HasForeignKey declaration and EF will use map the FK columns by convention.

eg

   protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // shadow property - primary/foreign key
        modelBuilder.Entity<UserGroup>()
            .Property<int>("IDUser");

        // shadow property - primary/foreign key
        modelBuilder.Entity<UserGroup>()
            .Property<int>("IDGroup");

        // composite primary key based on shadow properties
        modelBuilder.Entity<UserGroup>()
            .HasKey(new string[] { "IDUser", "IDGroup" });

        modelBuilder.Entity<UserGroup>()
            .HasOne(ug => ug.Group)
            .WithMany(g => g.UsersGroups);
        //.HasForeignKey(???); //what to do here ?

        modelBuilder.Entity<UserGroup>()
            .HasOne(ug => ug.User)
            .WithMany(u => u.UsersGroups);
            //.HasForeignKey(???); // what to do here ?

        base.OnModelCreating(modelBuilder);
    }

Generates

CREATE TABLE [UserGroups] (
    [IDUser] int NOT NULL,
    [IDGroup] int NOT NULL,
    CONSTRAINT [PK_UserGroups] PRIMARY KEY ([IDUser], [IDGroup]),
    CONSTRAINT [FK_UserGroups_Groups_IDGroup] FOREIGN KEY ([IDGroup]) REFERENCES [Groups] ([IDGroup]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserGroups_Users_IDUser] FOREIGN KEY ([IDUser]) REFERENCES [Users] ([IDUser]) ON DELETE CASCADE
);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow