Many-to-many relations in Breeze

Question

i am defining some of my models atm using EF5 Code First. I have these three sample classes with a many-to-many relationship:

public class Team
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Team_Id { get; set; }
        [MaxLength(150)]
        public string TeamName { get; set; }

        public virtual ICollection<User> Users { get; set; }
        [public virtual ICollection<Role> Roles { get; set; }  // 1

        [Timestamp]
        public byte[] TimeStamp { get; set; }
    }

public class User
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int User_Id { get; set; }
        [MaxLength(150)]
        public string LoginName { get; set; }
        [MaxLength(150)]
        public string Nachname { get; set; }
        [MaxLength(150)]        
        public string Vorname { get; set; }

        public virtual ICollection<Team> Teams { get; set; } 
        public virtual ICollection<Role> Roles { get; set; }   // 2

        [Timestamp]
        public byte[] TimeStamp { get; set; }
    }

public class Role
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Role_Id { get; set; }
        [MaxLength(50)]
        public string RoleName { get; set; }

        [Timestamp]
        public byte[] TimeStamp { get; set; }
    }

As you can see there is a many-to-many relationship between teams and users. I am trying to get this to work for hours. I always get an JS Exception with the error message "bad nav properties" in VS 2012. At first i thought it was the ICollections Teams/Users in Users/Teams-Class but it wasnt. The problem seems to be the two calls 1) and 2). I f i remove either one of them it works. Renaming one and keeping both fields active still throws the error. Maybe anyone has an idea what is going on.

Many thanks

This question was posted by SirSmackalot on our IdeaBlade forums. I am reposting the question and answer here since I think it will be useful to the Breeze Stack Overflow community.

Fulminous

Answer 1

Breeze doesn't yet support many-to-many relations where the mapping table is hidden. The problem is that Breeze depends on a "foreign key" concept to keep track of relationships, and this is not available for many-to-many relations defined with no payload in the Entity Framework.

What does work is to change the many-to-many relationship into two 1-to-many relations with a linking entity. Basically, just expose the mapping table as another entity type. For example:

Team -- TeamUser (1-to-many)

User -- TeamUser (1-to-many)

We do plan to support Entity Framework's official many-to-many relationship in a later release but we need to prioritize this. So please add/vote for this feature request using the feedback mechanism on the website (https://breezejs.uservoice.com/forums/173093-breeze-feature-suggestions/filters/top). This helps us decide which features to focus on next.

Fulminous

I advise people to avoid many-to-many implementations. The concept is fine but in practice it almost always falls apart. In my experience, the mapping table eventually acquires a column. Maybe it's a link-date or an userId or some other fact describing how the two sides came together. The moment I add that "payload", the many-to-many falls apart and I have to make the mapping/linking entity explicit. I have to scramble to track down all the code that depended on m-to-m ... code that can be hard to find. I always wish I had made them 1-to-m + mapping entity in the first place. – Renfroe

how would a query in breeze look with such an linking entity to get all users from a team? – Jampack

I did exactly that (used two 1-to-m relationships) but I'm still getting error that the navigation property is not recognized. When I looked into the breeze source code, I found this comment: // TODO: Revisit this later - right now we just ignore many-many and assocs with missing constraints. In this case the relationship is not m-to-m, but it does not have a constraint though, so that's probably why it's being ignored. The thing is I'm not sure why ignore an assoc with no constraints? Can't you just infer from the metadata that the relationship is 1-to-m from the multiplicity on each end? – Adapter

Same issue in Breeze.Sharp. Breeze frustrates me to the point I want to remove it. – Remainder