25

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
Improve this question
3
  • 1
    Isn't that which you want a many to many? Commented Jan 19, 2012 at 14:00
  • Thanks - Edit the title to reflect to correct meaning Commented Jan 19, 2012 at 14:15
  • 1
    Well formatted question. Easy to understand. Commented Apr 27, 2014 at 17:55

4 Answers 4

Reset to default
59 
modelBuilder.Entity<Account>()
            .HasMany(a => a.Products)
            .WithMany()
            .Map(x =>
            {
                x.MapLeftKey("Account_Id");
                x.MapRightKey("Product_Id");
                x.ToTable("AccountProducts");
            });
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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
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?
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.
I'm not sure if this is expected behavior or not-- but in my case my public virtual ICollection<Product> 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
@Chris without that random comment, and me randomly reading it, I would've been quite stuck. Thank you!
|
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; }
}
Improve this answer

5 Comments

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.
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
Do you know of any way to hide (access modifier?) the bi-directional association?
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 What should I do if I need to define a separate key to the joining table?
2

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.

Improve this answer

Comments

1 
        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"));
    }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.