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

Question

This is probably just because my knowledge with the EF Code First fluent API is lacking, but I'm stumped.

I want to model the following:

• A Groups collection with Id and Name
• A Users collection with Id and Name
• Each user is assigned to exactly one primary group
• Each user may have zero or many secondary groups

The table structure I'm going for would look like:

Groups

• Id
• Name

Users

• Id
• Name
• PrimaryGroupId

SecondaryGroupAssignments

• UserId
• GroupId

I've been beating my head against a wall trying to model this with EF Code First, but I can't get it to accept both relationships between User and Group. Sorry for not posting any .NET code (I'm happy to), but it's probably all wrong anyway.

Is there a way to make EF model this? I'm assuming I have to do some sort of configuration with the Fluent API. Maybe a better question is: is there any good, definitive reference for the Fluent API?

Thanks!

Chthonian

Answer 1

Try this (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"));
    }
}

Prime

Can I just say, Ladislav, that this is one of THE most useful posts I've ever had the pleasure of coming across / using to solve my exact problem! Thank you! – Responsion

Lad is the man with Entity Framework, so thankful to have such a great resource. One more reason the stack is awesome! – Scudo

Answer 2

Based on Ladislav's excellent answer, here's how to do it without using any mappings - just attributes applied 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 the 2 ICollections and the Group. This allows lazy-loading. Performance-wise, I don't recommend it, but it is possible.

I included MaxLength attributes with an arbitrary (but safe) length of 300, because putting strings out in EF without a MaxLength gets you low-performance NVarChar(MAX) columns. Totally irrelevant to what's being asked but better to post good code.

I recommend against class names "User" and "Group" for your EF classes. They're going to complicate any SQL you attempt to run later, having to type [User] and [Group] to access them, and complicate using these classes in MVC Controllers where your class User will conflict with the Context property User that gives you access to the Asp.Net Identity library.

Lolanthe