3

Tables in a many-to-many relationship are best handled by using a relationship (linking) table that only contains a Foreign Key to each table in the relationship. The relationship table itself should not have a Primary Key.

Start Edit (eesh 2017-06-18)

The above statement about the primary key is not true. A primary key should be used. The answer to the question is stated below. I have also changed the Title of this question to better reflect the problem.

Answer to Question: The linking table should have a primary key. The primary key should not be a unique generated Id column as is commonly used for other tables. Instead, it should contain a primary key that is a composite CK (candidate key) made up of the the two foreign keys that are the links for the Many-To-Many relationship. Please see the Stack Overflow question Creating composite primary key in SQL Server

Making this change causes the EF 6.0 to correctly generate the linking table as a table and not a view in the .edmx file. This change fixes the problem I was asking about and the question is answered. Thanks to Ivan Stoev and philipxy for pointing me in the right direction.

Everything below here is part of the original post which is resolved by simply creating a composite CK key for the linking table in SSMS as described above.

End Edit (eesh 2017-06-18)

When created in this fashion the relationship table does not appear in the .edmx diagram, but it is present in the edmx file. Configuring the tables in this fashion makes it easy to query the tables as each table in the relationship has a simple navigation property relating it to the other table.

Some examples of this can be found in the following links:

Entity Framework - querying a many-to-many relationship table

Entity Framework: Queries involving many to many relationship tables

Inserts and Updates should be straightforward as described in the following SO post:

Insert/Update Many to Many Entity Framework . How do I do it?

However, I found when I tried this I got the following error when trying to insert into a model that has a PackageManifest table, a Package table, and a PackageManifestAssignment table that links the two tables:

"Unable to update the EntitySet 'PackageManifestAssignment' because it has a DefiningQuery and no element exists in the element to support the current operation."

PackageManifestAssignment in the above is the linking table that links the PackageManifest table with the Package table. It only contains foreign keys for the PackageManifest and Package tables. There are no no other fields in the PackageManifestAssignment table.

Apparently this works fine when query existing relationships, but attempting to insert fails because EF 6.0 treats tables without Primary Keys as Views and, inserts are not allowed on views. Even though the association table isn't exposed to the programmer in the diagram view, it is present in the .edmx file and EF must insert a new entry in the association table for each new relationship created.

See links below for cause of error:

Entity Framework Error on SaveChanges()

It has a DefiningQuery but no InsertFunction element

Unable to update the EntitySet Table because it has a DefiningQuery and no InsertFunction element exists in the ModificationFunctionMapping element to support the current operation

In the above links an alternate solution is presented to creating a primary key for the table. Adding a primary key to the linking table would complicate CRUD for the tables in the relationship and creating relationship links. Hence, the preferred solution is to modify the .edmx file and make EF think that the table is not a view but is a table (which it is). This works. The instructions are:

  1. Right click on the edmx file, select Open with, XML editor
  2. Locate the entity in the edmx:StorageModels element
  3. Remove the DefiningQuery entirely
  4. Rename the store:Schema="dbo" to Schema="dbo" (otherwise, the code will generate an error saying the name is invalid)
  5. Remove the store:Name property

In my particular case the change looked like: Before Change:

<EntitySet Name="PackageManifestAssignment" EntityType="Self.PackageManifestAssignment" store:Type="Tables" store:Schema="dbo">
    <DefiningQuery>SELECT 
    [PackageManifestAssignment].[PackageManifestId] AS [PackageManifestId], 
    [PackageManifestAssignment].[PackageId] AS [PackageId]
    FROM [dbo].[PackageManifestAssignment] AS [PackageManifestAssignment]
    </DefiningQuery>
</EntitySet>

After Change (Working Version):

<EntitySet Name="PackageManifestAssignment" EntityType="Self.PackageManifestAssignment" store:Type="Tables" Schema="dbo">  
</EntitySet>

The drawback to manually making this change is that any time any table in the model is updated in the database and that change is carried over to EF using the .edmx "Update from Database/Refresh" option, the generated file (.edmx) file will overwrite the above changes to fix the error. Those changes will be required to be made manually again. This is both a cumbersome but more importantly fragile. If the change is not made future inserts for entries in the tables that use the linking table will fail. Developers making changes made many months or years down the line could easily forget this step.

Hence, the question is how to be able to keep the desired "easy to use" many-to-many relationship edit made to the .edmx file, without having to modify the .edmx file manually every time the model is updated from the database. Or, alternately is their another technique (marking the table in a certain way) or using a third party library to achieve this?

eesh
  • 1,384
  • 2
  • 13
  • 25
  • 3
    *Adding a primary key to the linking table would complicate CRUD for the tables in the relationship and creating relationship links* Not sure what you mean by that. Linking tables usually do (and should) have a composite PK containing the FK columns. – Ivan Stoev Jun 16 '17 at 17:58
  • What do you mean, "The relationship table itself should not have a Primary Key"? The columns of associated entity ids form a (composite) CK (candidate key) ie PK. Do you mean, not have an added unique id column? – philipxy Jun 17 '17 at 05:44
  • Please read & act on [mcve]. – philipxy Jun 17 '17 at 06:05
  • Ivan, how and where is the composite PK defined? I see no way to do that in SSMS and EF 6.0 does not treat the FKs as a composite PK. If I create a linking table that includes a generated primary key (explicit key in table that is desginated as the Identity Specification) then EF 6.0 generates an entity that requires significantly more involved queries to add entries to the linking table (see examples in my post). If I do not create a generated primary key, then EF 6.0 does not designate or combine the two foreign keys as a candidate key or primary key in the generated .edmx file. – eesh Jun 17 '17 at 13:42
  • Philip, yes I mean "not have an added unique id column." See my comment above answering Ivan on EF 6.0 treatment of the FKs when generating the .edmx file and entity for the linking table. – eesh Jun 17 '17 at 13:45
  • Philip, What do you believe I need to add to make this Minimal, Complete, and Verifable? The examples I linked to in my post show clear examples of three tables with one of the tables being a linking/association table. If you create those three tables in SQL, and then use EF 6.0 to create the entities you will recreate the issue I encounter. You will not be able to insert items into the linked tables without manually manipulating the generated .edmx as explained in other links I included in my post. This works well but every time the entities are generated the .edmx file is overwritten. – eesh Jun 17 '17 at 13:51
  • I found a SO post that shows how to designate two columns as a Primary Key. When I get a chance I will save all my current work and then remove all three tables, recreated them, and generate the entities with the linking table having a CK made up of the two foreign keys. I will see if that generates an .edmx file that allows for inserts and keeps the CRUD operations via code using the entities simple. I'll post results of my test. https://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio – eesh Jun 17 '17 at 14:18
  • You can define PK *constraint* in both SSMS table designer or with plain DDL SQL (`ALTER TABLE ..` as shown in one of the answers from your link). Once you do that, the EF will correctly identify the link table as *table* :) – Ivan Stoev Jun 17 '17 at 18:01
  • Ivan. I did that and everything worked. Thanks for your assistance. I wish I could award half the bounty points to you, but the rules for SO only allow awarding of points to an answer not a comment and philipxy had a good answer that was to the point. – eesh Jun 18 '17 at 21:55

1 Answers1

1

The relationship table itself should not have a Primary Key.

Every base table should have all CKs (candidate keys) declared, ie any column set(s) that have unique subrow values and that don't contain any smaller column set(s) that have unique subrow values. We can pick one as PK (primary key) and we declare any others as UNIQUE NOT NULL (which is the constraint that PK gives).

The entity id columns of an n-ary relationship/association table, aka linking/association/join table, form its PK, which, consisting of more than one column, is called composite. Per this answer:

HasKey(PackageManifestAssignment => new {
    PackageManifestAssignment.PackageManifestId,
    PackageManifestAssignment.PackageId
    });

PS

Tables in a many-to-many relationship are best handled by using a relationship (linking) table that only contains a Foreign Key to each table in the relationship.

In general relationships/associations are n-ary. They can have attributes of their own. CKs/PKs can include entity or relationship/association (associative entity) CK/PK columns and attribute columns.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Philip I am generating my entities from an existing database. I am not using EF 6.0 with Code First. I am not sure if doing so with the suggestion above would resolve the issue I described but it is not an option for this project/problem. This is a long running project with dozens of tables and existing code. Among other tables I added two tables that required a many-to-many relationship and a linking/association table was the obvious answer. When I updated the entities from the DB I ran into issues. – eesh Jun 17 '17 at 14:00
  • philipxy, As I noted in a comment on my post I found out how to designate the two FKs in my linking table as a composite PK. Whether this fixes the problem I don't know yet. I will need to test this out and see how EF 6.0 treats this when generating the .edmx file and entities. – eesh Jun 17 '17 at 14:27