EF Code First: many-to-many and one-to-many

Question

Most likely, this is due to the fact that I lack knowledge in the EF Code First fluent API, but I'm at a dead end.

I want to simulate the following:

• Group collection with id and name
• Collection Users with ID and Name
• Each user is assigned exactly one primary group.
• Each user can have zero or many secondary groups.

The structure of the table I'm going to will look like this:

Groups

• Id
• Name

Users

• Id
• Name
• PrimaryGroupId

SecondaryGroupAssignments

• Userid
• Groupid

I hit my head against a wall trying to simulate this using EF Code First, but I cannot get it to accept both connections between the user and the group. Sorry for not sending any .NET code (I'm glad), but it's probably all wrong.

Is there any way to make EF a model? I assume that I need to do some configuration with the Fluent API. Perhaps the best question is: is there a good, definitive link for the Fluent API?

Thanks!

Answer 1

Try this one (untested):

public class Group { public int Id { get; set; } public string Name { get; set; } public virtual ICollection<User> PrimaryUsers { get; set; } public virtual ICollection<User> SecondaryUsers { get; set; } } public class User { public int Id { get; set; } public string Name { get; set; } public int PrimaryGroupId { get; set; } public virtual Group PrimaryGroup { get; set; } public virtual ICollection<Group> SecondaryGroups { get; set; } } public class Context : DbContext { public DbSet<User> Users { get; set; } public DbSet<Group> Groups { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<User>() .HasRequired(u => u.PrimaryGroup) .WithMany(g => g.PrimaryUsers) .HasForeignKey(u => u.PrimaryGroupId) .WillCascadeOnDelete(false); modelBuilder.Entity<User>() .HasMany(u => u.SecondaryGroups) .WithMany(g => g.SecondaryUsers) .Map(m => m.MapLeftKey("UserId") .MapRightKey("GroupId") .ToTable("SecondaryGroupAssignments")); } } 

Answer 2

Based on Ladislav's excellent answer, here's how to do it without using any mappings - just attributes that apply to the model classes themselves:

 public class Group { public int Id { get; set; } [MaxLength(300)] public string Name { get; set; } public ICollection<User> Users { get; set; } } public class User { public int Id { get; set; } [MaxLength(300)] public string Name { get; set; } [ForeignKey("PrimaryGroup")] public int PrimaryGroupId { get; set; } [Required] public Group PrimaryGroup { get; set; } [InverseProperty("Users")] public ICollection<Group> SecondaryGroups { get; set; } } 

Notes

If you want, you can add the virtual keyword to 2 ICollection and Group . This allows lazy loading . Performance, I do not recommend, but it is possible.

I included MaxLength attributes with an arbitrary (but safe) length of 300 , because when wrapping rows in EF without MaxLength, you get columns with low performance NVarChar(MAX) . It doesn't really matter what they ask, but it's better to post good code.

I recommend the names User and Group for your EF classes against classes. They are going to complicate any SQL that you try to run later, you need to type [User] and [Group] to access them and complicate the use of these classes in MVC controllers, where your User class will conflict with the Context User property that gives you access to the Identity Asp.Net library.