How to configure many to many relationship using entity framework fluent API

Question

I'm trying to set up a many to many relationship in EF code first but the default conventions is getting it wrong. The following classes describes the relationship:

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class Account
{        
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

One Account can have many Products.

However the EF conventions will create the DB tables as:

Products Table
--------------
Id
Name
Account_Id  <- What is this?

Accounts Table
--------------
Id
Name

This doesn't look like a many-to-many table structure? How can i get configure the fluent API to reflect the relationship and create an intermediary table:

AccountProducts Table
---------------------
Account_Id
Product_Id

Isn't that which you want a many to many? – Oskar Kjellin Jan 19 '12 at 14:00 — Oskar Kjellin, Jan 19 '12 at 14:00
Thanks - Edit the title to reflect to correct meaning – Fixer Jan 19 '12 at 14:15 — Fixer, Jan 19 '12 at 14:15
Well formatted question. Easy to understand. – Pavan Kumar Apr 27 '14 at 17:55 — Pavan Kumar, Apr 27 '14 at 17:55

score 59 · Accepted Answer · edited Aug 01 '16 at 08:47

59

modelBuilder.Entity<Account>()
            .HasMany(a => a.Products)
            .WithMany()
            .Map(x =>
            {
                x.MapLeftKey("Account_Id");
                x.MapRightKey("Product_Id");
                x.ToTable("AccountProducts");
            });

edited Aug 01 '16 at 08:47

Ali Seyedi

1,758
1
19
24

answered Jan 19 '12 at 14:09

Slauma

175,098
59
401
420

Thanks for this - Will mark this as the accepted answer as this allows me to set up a uni-directional association without needing to add an (unneeded) association in the Product class – Fixer Jan 19 '12 at 14:30
What if I want to follow additional data on the correlation table, like date_added, relation type etc. Can I define a POCO for AccountProducts? And how would you do the mapping in this case? – hazimdikenli Feb 09 '13 at 15:57
@hazimdikenli: You can't use a many-to-many relationship then. You must model this with two one-to-many relationships. Details are here: http://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table/7053393#7053393 – Slauma Feb 09 '13 at 17:10
4

This worked for me, but only after I added the equivalent of `p => p.Accounts` inside the `.WithMany()`. Without doing that, it was creating a weird property inside Accounts. – Christopher Feb 19 '14 at 01:26
@Chris: Yes, if you have an `Accounts` collection in `Product` you must do that. Without such a collection (like in the question's model) `WithMany()` without parameter is correct. – Slauma Feb 19 '14 at 01:34
1

I'm not sure if this is expected behavior or not-- but in my case my `public virtual ICollection Products { get; set; }` is removing duplicates. So if there's 2 rows with `(Account_ID, Product_ID)` as `(1, 1)`, my `Products` collection.ToList() only contains one record with id 1 – Don Cheadle Dec 21 '15 at 01:40
2

@Chris without that random comment, and me randomly reading it, I would've been quite stuck. Thank you! – Don Cheadle Dec 21 '15 at 01:53

score 7 · Answer 2 · answered Jan 19 '12 at 14:04

7

What EF has suggested is a one-to-many relationship.

One Account can have many Products, i.e. each Product has an Account_Id

If you want a many to many relationship (and create an intermediary table), the following should work

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Account> Accounts { get; set; }
}

class Account
{        
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

answered Jan 19 '12 at 14:04

NinjaNye

7,046
1
32
46

Oh that was easy - But why do we need to bi-directional association here? In the domain model we don't want to access the accounts via each product. – Fixer Jan 19 '12 at 14:14
If a product does not know what account it is attached to, there is no way of defining the relationship. If your using viewModels, for instance, you can always exclude the accountId and only map the data you need – NinjaNye Jan 19 '12 at 14:17
Do you know of any way to hide (access modifier?) the bi-directional association? – Fixer Jan 19 '12 at 14:23
See my comment above. In your viewModel, or DTO object or whatever you are using, exclude the AccountId from the mappings, this will then get hidden for anyone that uses your class. If you want to remove it from the db, that can't be done. The database needs to have this column to define the relationships – NinjaNye Jan 19 '12 at 14:26
@NinjaNye What should I do if I need to define a separate key to the joining table? – Geethanga Feb 13 '13 at 10:23

score 2 · Answer 3 · answered Jan 19 '12 at 14:05

Code first is creating tables in right relational way. When

One Account can have many Products.

Products table should store key for its Account, as it actually does now.

What you are trying to see in db, is many-to-many relationship, not one to many. If you want to achieve that with code first, you should redesign your entities

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Account> Accounts { get; set; }
}

class Account
{        
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

In this case, one product can have many accounts, and one account can have many products.

score 1 · Answer 4 · answered Jan 19 '12 at 14:07

        public AccountProductsMap()
    {
        this.ToTable("AccountProducts");
        this.HasKey(cr => cr.Id);

        this.HasMany(cr => cr.Account)
            .WithMany(c => c.Products)
            .Map(m => m.ToTable("AccountProducts_Mapping"));
    }

How to configure many to many relationship using entity framework fluent API

4 Answers4

Linked