sql server - 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.

Answer 1

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.