Mapping many to many relationship

Question

I am have some trouble getting Entity Framework to handle a many to many relationship in my data schema. Here is my model:

public class User
{
    public int UserId { get; set; }
    public int Username { get; set; }
    public IEnumerable<Customer> Customers { get; set; }
    ...
}

public class Customer
{
    public int CustomerId { get; set; }
    ...
}

public class CustomerUser
{
    public int CustomerUserId { get; set; }
    public int CustomerId { get; set; }
    public int UserId { get; set; }
    public DateTime CreatedTimestamp { get; set; }
    ...
}

Here is the mapping:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>().HasKey(u => u.UserId).ToTable("Users");
        modelBuilder.Entity<Customer>().HasKey(c => c.CustomerId).ToTable("Customer");
        modelBuilder.Entity<CustomerUsers>().HasKey(cu => cu.CustomerUserId).ToTable("CustomerUsers");

        modelBuilder.Entity<CustomerUsers>()
            .HasRequired(cu => cu.User)
            .WithRequiredDependent()
            .Map(m =>
                {
                    m.ToTable("Users");
                    m.MapKey("CustomerUsers.UserId");
                });
}

My database has a Users, Customers, and CustomerUsers table with columns that match the model.

I am trying to execute the following query:

result = (from u in context.Users
                      join customerUsers in context.CustomerUsers on u.UserId equals customerUsers.User.UserId
                      join customers in context.Customers on customerUsers.CustomerId equals customers.CustomerId into ps
                      select new
                      {
                          User = u,
                          Customers = ps
                      }).ToList().Select(r => { r.User.Customers = r.Customers.ToList(); return r.User; });

When I run the code, I get the following error:

The Column 'CustomerUserId' specified as part of this MSL does not exist in MetadataWorkspace

Can anyone see what is wrong with my approach?

Thanks!

I should note that I am intentionally trying to not include a reference to the CustomerUsers table from either the Customer or User class. The majority of the time, the payload of the CustomerUsers table is not important, only which customers are associated to which users. There are some reporting scenarios where the additional information in the join table is necessary, but since this is not the typical situation, I would like to avoid cluttering up the models by having this additional indirection.

I should note that I am intentionally trying to not include a reference to the CustomerUsers table from either the Customer or User class. The majority of the time, the payload of the CustomerUsers table is not important, only which customers are associated to which users. There are some reporting scenarios where the additional information in the join table is necessary, but since this is not the typical situation, I would like to avoid cluttering up the models by having this additional indirection. — jon, Feb 27 '12 at 19:48
Don't write a comment to your own question. You can edit your question and put new info into the question. It will move your question on top of the active questions and attract more readers. Also, I'd recommend to create your model so that it represents the real relationships in the DB. (You need them anyway, at latest when you want to query data for your report scenarios.) You can still create shortcuts by designing appropriate service methods (`GetCustomersForUser(...)`, ...) or something. — Slauma, Feb 27 '12 at 20:33
oops :) forgive me I am new here. I have considered the approach you suggest, though I would still prefer to leave those details out of the entity models. I am hoping that is not my only option — jon, Feb 27 '12 at 21:46

score 4 · Accepted Answer · answered Feb 27 '12 at 18:50

4

Instead of trying to map this as many to many, map it as two one to many relationships. See the discussion of many to many join tables with payload in Many-to-Many Relationships in this tutorial:

http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application

answered Feb 27 '12 at 18:50

tdykstra

5,880
2
23
20

thank you for the link, it is a good reference, though it is not quite what I am after. – jon Feb 27 '12 at 21:47
I give up, entity framework has broken my will. I suppose this is the closest I can get to what I am after. Thanks again for the link. – jon Feb 28 '12 at 13:35

score 2 · Answer 2 · answered Feb 27 '12 at 18:52

For your model you will need probably two one-to-many relationships and the following navigation properties:

public class User
{
    public int UserId { get; set; }
    public int Username { get; set; }
    // ...
    public ICollection<CustomerUser> CustomerUsers { get; set; }
}

public class Customer
{
    public int CustomerId { get; set; }
    //...
    public ICollection<CustomerUser> CustomerUsers { get; set; }
}

public class CustomerUser
{
    public int CustomerUserId { get; set; }
    public int CustomerId { get; set; }
    public int UserId { get; set; }
    public DateTime CreatedTimestamp { get; set; }
    //...
    public User User { get; set; }
    public Customer Customer { get; set; }
}

And the following mapping:

modelBuilder.Entity<CustomerUser>()
    .HasRequired(cu => cu.User)
    .WithMany(u => u.CustomerUsers)
    .HasForeignKey(cu => cu.UserId);

modelBuilder.Entity<CustomerUser>()
    .HasRequired(cu => cu.Customer)
    .WithMany(c => c.CustomerUsers)
    .HasForeignKey(cu => cu.CustomerId);

Mapping many to many relationship

2 Answers2

Linked