Entity Framework 4.1 Code First - Define many-to-many using data annotations only

Question

Is it possible to define a many-to-many relationship in Entity Framework 4.1 (Code First approach) using Data Annotations only, without model builder?

For example, something like:

Product = { Id, Name, ... }
Category = { Id, Name, ... }
ProductCategory = { ProductId, CategoryId }

You get the picture.

I don't want to have an intermediate entity ProductCategory in the context with two many-to-ones since I don't have any additional data, just the two FKs. Also, I should be able to define table name for the intermediate table for use with an existing database.

Ladislav Mrnka · Accepted Answer · 2011-06-08T22:40:35.063

12

It is possible to define many-to-many with default conventions or with data annotations but it is not possible to change mapping to junction table (table's name and columns) without model builder. Simple many-to-many:

public class Product
{
    public int Id { get; set; }
    public virtual ICollection<Category> Categories { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

For using annotations you can use:

public class Product
{
    [Key]
    public int Id { get; set; }
    [InverseProperty("Products")]
    public virtual ICollection<Category> Categories { get; set; }
}

public class Category
{
    [Key] 
    public int Id { get; set; }
    [InverseProperty("Categories")]
    public virtual ICollection<Product> Products { get; set; }
}

If you need to control mapping of junction table to existing database you need modelBuilder. Data annotations are not as powerful as fluent API.

edited Jun 08 '11 at 22:40

answered Jun 08 '11 at 06:03

Ladislav Mrnka

360,892
59
660
670

Thank you, I did as you suggested but it still doesn't work. For some reason I get an Exception `Invalid column name 'Category_Id'.\r\nInvalid column name 'Product_Id'.` when I try to evaluate `product.Categories`. This is using convention only, i.e. no annotations or fluent API. My database table `ProductsCategories` has `ProductId` and `CategoryId` without the underscore in between. Why the underscore? It isn't documented anywhere as a convention AFAIK. EF4.1 doesn't need underscores for 1-M and M-1 relations (at least it's not documented). – Boris B. Jun 08 '11 at 21:09
Product_Id and Category_Id are default names. If you have existing database and you have different names you must use model builder to correctly map the relation to your own table. – Ladislav Mrnka Jun 08 '11 at 21:47
You are right. However, elsewhere where 1-M relations are used it expects FK field *without* the underscore (i.e. `ProductId`). That makes **no sense**. In fact, that means that if I later add another field to the ProductCategories intermediate table ("Ord" for example) it will become a true entity, and then the convention will complain that it can't find the field "ProductId". – Boris B. Jun 08 '11 at 22:19
You have stated in your answer that `It is possible to define many-to-many ... with data annotations`. Could you please just post which annotations apply for many-to-many (just the list) so that I can accept the answer? – Boris B. Jun 08 '11 at 22:23
It never expects key without underscore. If it uses FK without underscore it means that your entity defines such column but if you omit the column it will create / expect FK with underscore. Obviously if you change your table, your mapping can fail. – Ladislav Mrnka Jun 08 '11 at 22:23
I'm sorry but that is not what I'm observing. If I create a class like `class Product{ int Id; int CategoryId; }` and have table `TABLE Products (Id int NOT NULL, Category_Id int NOT NULL)` (note the underscore in FK field), then retrieving a product via DbContext fails with `Invalid column name 'CategoryId'`, it clearly expects DB field without the underscore. – Boris B. Jun 08 '11 at 22:42
@LadislavMrnka I didn't get it. Can I make a code-first junction table (i.e. `ProductCategory`) with annotations (I don't care what's in the database, I use database initialization, it's a small project)? – Shimmy Weitzhandler May 11 '12 at 04:02
@Shimmy: If you don't care what is the name of the junction table in the database or what are names of its columns and if you have navigation properties on both sides of the relation you even don't need to use any data annotations or fluent api at all. EF will map it with default conventions. – Ladislav Mrnka May 11 '12 at 13:36
No, I do need some columns in that junction table. – Shimmy Weitzhandler May 12 '12 at 21:39
1

@Shimmy: If you need to name colums in the junction table you have to use Fluent api. – Ladislav Mrnka May 13 '12 at 06:53
I mean, I wanna add some columns, I don't care what are their names in the database, look as it as `Customer`, `Order` and `CustomerOrder` where I wanna add properties to the `CustomerOrder` class (I only really care about classes/props, not about tables/columns), Do I have to use fluent? Or can I achieve this with attributes? – Shimmy Weitzhandler May 13 '12 at 08:48
1

@Shimmy: If you want to add additional properties to CustomeOrder it is not junction table anymore so create a new class for this entity and decompose your many-to-many relation between Customer and Order into two separate one- to-many relations. You can use data annotations for that. – Ladislav Mrnka May 13 '12 at 13:22
I've posted a new question. See [here](http://stackoverflow.com/questions/10573284/relationship-mapping-table-with-additional-properties) – Shimmy Weitzhandler May 13 '12 at 16:22
Is this still applies to EF6.1? Do I still have to use fluent API to specify my junction table name? – Rosdi Kasim Apr 08 '14 at 05:33

Entity Framework 4.1 Code First - Define many-to-many using data annotations only

1 Answers1

Linked