Join table for all kinds of relationship multiplicity

Question

Hi,
We have an enterprise database where all relationships between entities go via a single join table. For simplicity, say we have a TPT scheme with Base abstract class in table B and derived classes C, U, and S in tables C, U, S. There is also a single join table R that holds all the relationships.

The relationship between C and U is many-to-one (computer has a primary user. Same user can be primary user for multiple computers). The relationship between C and S is many-to-many (say computer has installed software).

The table R has other fields so EF will not see it as a join table. In order to make it appear like a join table for the C-->U relationship, I used a DefiningQuery for the EntitySet in SSDL that maps to C->U relationship. The problem I'm running into is that, unless I define the multiplicity of both ends of the C-->U relationship to be "*" (i.e. model it as many-many), I get errors at runtime similar to

SM.msl(30,6) : error 3003: Problem in mapping fragments starting at line 30:Given the cardinality of Association End Member Computer, it should be mapped to key columns of the table ComputerPrimaryUserQuery. Either fix the mapping or change the multiplicity of this end.

Is there any solution to this? I'd hate to put the cardinality=* because it gives the wrong impression programming against the generated classes.

Thanks,
Darshat

---

Darshat Shah -Senior Developer, System Center Service Manager Team

Answer 1

Modulo creating views that join together the tables with the join table, I do not believe that there is a walkthrough. But, one thing you could do is remove the navigation property on the "incorrect" many end, if that matches your programming model well, or at least declare it as private...

---

This posting is provided "AS IS" with no warranties, and confers no rights.