How can I expose the Join table?

Question

I'm trying to follow this idea from Jimmy Bogard on avoiding Many-to-Many-Mappings in ORMs

Given this setup I want to be able to also directly expose the 'Join' object in the relationship.

Objects

User
Role
UserRole (association object)

Code:

public class User
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

public class Role
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

public class UserRole
{
    public Guid UserId { get; set; }
    public Guid RoleId { get; set; }
    public User User { get; set; }
    public Role Role { get; set; }
}

public class MyContext : DbContext 
{

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Entity<UserRole>().HasKey(u => new { u.RoleId, u.UserId });
        modelBuilder.Entity<User>().HasMany(x => x.Roles).WithMany(x => x.Users).Map(m =>
        {
            m.ToTable("UserRoles");
            m.MapLeftKey("UserId");
            m.MapRightKey("RoleId");
        });
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
    //public DbSet<UserRole> UserRoles { get; set; } 
}

If I add a migration for this I get what I expect.

public override void Up()
{
    CreateTable(
        "dbo.Roles",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "dbo.Users",
        c => new
            {
                Id = c.Guid(nullable: false),
                Name = c.String(),
            })
        .PrimaryKey(t => t.Id);

    CreateTable(
        "dbo.UserRoles",
        c => new
            {
                UserId = c.Guid(nullable: false),
                RoleId = c.Guid(nullable: false),
            })
        .PrimaryKey(t => new { t.UserId, t.RoleId })
        .ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
        .ForeignKey("dbo.Roles", t => t.RoleId, cascadeDelete: true)
        .Index(t => t.UserId)
        .Index(t => t.RoleId);
}

As soon as I add a DBset to the DbContext for the UserRoles object. EF can't find the PK for the UserRoles object.

UserRoles: EntityType: EntitySet 'UserRoles' is based on type 'UserRole' that has no keys defined.

I then try specifying the Key like this:

modelBuilder.Entity<UserRole>().HasKey(u => new { u.RoleId, u.UserId });

But EF doesn't know I want to use the same UserRoles table because it add's a 2nd table for that object.

CreateTable(
"dbo.UserRoles1",
c => new
    {
        RoleId = c.Guid(nullable: false),
        UserId = c.Guid(nullable: false),
    })
.PrimaryKey(t => new { t.RoleId, t.UserId })
.ForeignKey("dbo.Roles", t => t.RoleId, cascadeDelete: true)
.ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
.Index(t => t.RoleId)
.Index(t => t.UserId);

How can I instruct the DbModelBuilder that I want to only use a single UserRole table?

I have a demo .sln for this problem on github

That's not avoiding many-to-many, that's just many-to-many with unidirectional. — Komengem, May 17 '14 at 16:14
Yeah I read the post again and now see you have to give up the many-to-many to expose the join table. — ctrlShiftBryan, May 17 '14 at 18:11

score 2 · Accepted Answer · answered May 17 '14 at 15:28

I think you're mixing two things together.

If you want to expose the M:N table then you cannot use M:N association in Entity Framework because Entity Framework hides that for you. You should then map it as two 1:M and N:1 associations. In that case you force Entity Framework to think about it that way and not as M:N. And you'll have to specify all the conditions when querying across this association yourself (that's probably what you want to do). Basically doing the joining that Entity Framework does on your behalf.

But you can also use both worlds. Though it might be dangerous, you have to carefully do updates, deletes etc. You can create a view i.e. MyUserRoles that's just select * from UserRoles and map this as a backing "table" for UserRoles entity. But as I said, you should be very careful in changes, as you might easily confuse the DbContext by changing the MyUserRoles while Entity Framework thinks there's no change in M:N.

How can I expose the Join table?

1 Answers1