27

I have a many to many relationship and I want to store extra data in the couple-table, using Code First Fluent API.

How can this be achieved ?

My model:

A user can have 1 or more badges (optional), a badge can belong to one or more users (optional). I want to store an extra field (called B) for this relation to be stored. The table should be named UserBadges with the following fields: UserId, BadgeId, B

(I have seen this earlier in StackOverflow here, but I the model is somewhat complex and no answer has been given correctly yet)

Community
  • 1
  • 1
Patrick Peters
  • 9,456
  • 7
  • 57
  • 106

1 Answers1

47

You cannot map it as many-to-many directly. If you add additional field to the junction table and you want to access that field in the application you need to promote your junction table to entity instead and use two one-to-many relations:

public class Badge {
    ...
    public virtual ICollection<UserBadge> UserBadges { get; set; }
}

public class User {
    ...
    public virtual ICollection<UserBadge> UserBadges { get; set; }
}

public class UserBadge {
    public int UserId { get; set; }
    public int BadgeId { get; set; }
    public string B { get; set; }
    public virtual Badge Badge { get; set; }
    public virtual User User { get; set; }
}

The default conventions should define the mapping correctly except the key for UserBadge table which must be done either in Fluent-API or data annotations.

modelBuilder.Entity<UserBadge>().HasKey(e => new { e.UserId, e.BadgeId });
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 1
    Thx, but I doubt if this works, because you define UserId and BadgeId as a compound PK. It should be possible to have 2 records with the same UserId and BadgeId in it, the B value does make the distinct value of it (it is a FK to another table). – Patrick Peters Mar 01 '13 at 20:30
  • @PatrickPeters: In such case you must define PK differently but the point of the answer is still the same - you need to expose the junction table as entity. – Ladislav Mrnka Mar 04 '13 at 07:43
  • Yes Ladislav, you are totally right. I fixed this according to your reply and it works. – Patrick Peters Mar 04 '13 at 11:50
  • 3
    I know this is really old, but isn't the collections in UserBadge wrong? A single UserBadge should only have a single Badge and a single User? – NiklasJ Aug 22 '14 at 09:28
  • 2
    "which must be done either in Fluent-API or data annotations." Could you share how to do that by annotations, please? – Yoda May 30 '15 at 16:38
  • @mmcrae: If you want to have additional properties / columns in junction table and access them through entity framework than yes, you need to expose it as entity. If you are happy with pure junction table containing only keys from many-to-many relation, EF will hide the table for you. – Ladislav Mrnka Dec 15 '15 at 17:11
  • 1
    Please excuse my ignorance, but does that mean that I now have to maintain another (join) entity in the code? The last time I worked with EF 5.0, EF was able to handle everything using Navigation Properties for the M:N scenario. – Vaibhav Aug 06 '16 at 12:47
  • 2
    @Vaibhav: EF is able to handle everything using navigation properties for the M:N scenario if your join table contains only keys for related tables. If you need any more records you need a separate join entity to have access to them. – Ladislav Mrnka Aug 09 '16 at 11:52
  • My "join table" contains the keys for each of the 2 related tables, and also an IDENTITY(1,1) "id" column (just my habit to always create "id" column); so I have 3 columns in total. 1) Must I include my 3-column join table in my Entity Framework? 2) Must I get a User's badges using this: `User.UserBadges.Select(ub=> ub.Badge)?` or can I create a property (navigation property?) called `User.Badges` which can do it directly? – Nate Anderson Aug 09 '16 at 17:47
  • @TheRedPea: You can try to use navigation property as identity field is not assigned by EF but you must cheat in EF by telling it in mapping that other two keys form a primary key of the table. – Ladislav Mrnka Aug 10 '16 at 09:39
  • Thanks @LadislavMrnka , I wonder if that is shown in example somewhere. At the moment your comment isn't clear to me, but I'm new to EF and I seldom create navigation properties / look at mappings. I simply "Update Model from Database" – Nate Anderson Aug 11 '16 at 12:50
  • 1
    @TheRedPea: If you use "Update model from Database", don't use additional identity Id for junction table. EF generator will not handle such table as junction for M:N relation and instead creates a new entity. – Ladislav Mrnka Aug 11 '16 at 17:12