EF code-first many-to-many with additional data

Question

I have these tables:

CREATE TABLE [EDR_SECURITY].[Person](
    [ixPerson] [bigint] IDENTITY(1,1) NOT NULL,
    [sName] [nvarchar](200) NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



CREATE TABLE [EDR_SECURITY].[SecurityGroup](
    [ixSecurityGroup] [bigint] IDENTITY(1,1) NOT NULL,
    [sName] [nvarchar](200) NOT NULL,
    [sDescription] [nvarchar](400) NULL,
) ON [PRIMARY]


CREATE TABLE [EDR_SECURITY].[PersonSecurityGroupDefinition](
    [ixPerson] [bigint] NOT NULL,
    [ixSecurityGroup] [bigint] NOT NULL,
    [fPrivilege] [bigint] NOT NULL,
 CONSTRAINT [PK_PersonSecurityGroupDefinition] PRIMARY KEY CLUSTERED 
(
    [ixPerson] ASC,
    [ixSecurityGroup] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

How do I create in EF code-first the relationships between Person and Security Group?

Thanks.

If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Apr 17 '12 at 21:20

NSGaga-mostly-inactive · Accepted Answer · 2012-04-18T16:01:07.117

4

If I understood exactly from the raw SQL...

EDIT: and as suggested in your specific case I think this is pretty close at least...

public class Person
{
    public int PersonID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<PersonSecurityGroupDefinition> 
        PersonSecurityGroups { get; set; }
}
public class SecurityGroup
{
    public int SecurityGroupID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public virtual ICollection<PersonSecurityGroupDefinition> 
        PersonSecurityGroups { get; set; }
}
public class PersonSecurityGroupDefinition
{
    public int PersonID { get; set; }
    public int SecurityGroupID { get; set; }
    public int Privilege { get; set; }
    // don't use virtual here as these are PK-s
    public Person Person { get; set; }
    public SecurityGroup SecurityGroup { get; set; }
}

...and in the OnModelCreating (override in your DbContext...

modelBuilder.Entity<PersonSecurityGroupDefinition>()
    .HasKey(i => new { i.PersonID, i.SecurityGroupID });

modelBuilder.Entity<PersonSecurityGroupDefinition>()
    .HasRequired(i => i.Person)
    .WithMany(u => u.PersonSecurityGroups)
    .HasForeignKey(i => i.PersonID)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<PersonSecurityGroupDefinition>()
    .HasRequired(i => i.SecurityGroup)
    .WithMany(d => d.PersonSecurityGroups)
    .HasForeignKey(i => i.SecurityGroupID)
    .WillCascadeOnDelete(false);

...hope this is it.

edited Apr 18 '12 at 16:01

answered Apr 17 '12 at 21:28

NSGaga-mostly-inactive

14,052
3
41
51

me too, look closely - this is the index table, it's many-to-many - you just need to replace the User w/ Person, Role with SecurityGroup and your PersonSecurityGroupDefinition is UserRole. – NSGaga-mostly-inactive Apr 18 '12 at 12:53
You really could have spent the additional minute to replace the class and property names with the names from the question and show how the other two entities would look like. You know the complete answer and it just feels a bit lazy to copy and paste another model into the answer and say "here, this is very similar". – Slauma Apr 18 '12 at 15:39
+1: Good answer :) I don't understand though why the nav. properties in the join entity shouldn't be `virtual`, they represent normal FK relationships. That the FK is part of the PK at the same time doesn't matter imo. I would also remove the `WillCascadeOnDelete(false)` because a join table usually has cascading delete. There won't be a "multiple cascading delete path" exception because the two cascading deletes are from different tables. – Slauma Apr 18 '12 at 16:19
@Slauma point taken on the edit :) - edited - I just had no time and I feel like a good pointer gets people 'involved' more. Nevertheless you're right and on SO it seems to be a predominant way of answering (thorough answers). – NSGaga-mostly-inactive Apr 18 '12 at 16:31
@Slauma I had problems in that very same case if FK-s that are PK-s (or corresponding navigation properties) are marked as virtual - with some strange error (don't remember, seemingly all is right but insert fails) - it may be just with Sql CE provider or in more complex cases (e.g. withMany() empty etc.). Frankly not sure, will try to nail that down with some details when I manage, I just remember that was the required thing and since then I'm keeping those two things together. – NSGaga-mostly-inactive Apr 18 '12 at 16:49
This specially useful when it references itself, e.g. a Manager (Person) that supervise others and also could be supervised. such as `List SupervisedBy`, and `List SupervisorOf` – Jaider Apr 20 '14 at 02:32

EF code-first many-to-many with additional data

1 Answers1

Linked