14

We are learning Entity Framework 6.1 (from NuGet) as we move away from Linq2Sql. We have a small handful of tables that associate two separate tables like shown below.

EF6 Database First generation

DB Diagram:

enter image description here

Schema Overview:

enter image description here

When in Visual studios, blank class library, doing a Database First EF6 EDMX file, the diagram only generates TableA and TableC -- the TableB does not get generated.

enter image description here

Visual Studios View:

enter image description here

You can see that only TableA and TableC are created. Technically TableB should have been created, because you would want to be able to manage those references.

The Association between A and C shown in the diagram:

enter image description here

I feel like I am missing an option, or misunderstanding a key concept of Entity Framework. Any idea how to have the missing TableB generated with the T4? The EDMX file does show it, but for some reason it doesn't get generated into a .CS file with the two properties indicating the relationship.

The primary reason we need this, is we extended the EF6 T4 template to add some factory patterns to match our existing models. Because it doesnt generate a class for TableB, we dont get the autogenerated code that we are looking for.

Thoughts / suggestions? Thanks.

TravisWhidden
  • 2,142
  • 1
  • 19
  • 43

3 Answers3

7

Weak entities or join tables will not be generated by EF, you need to configure the relationships manually thru fluent API or using data annotations

As stated on Microsoft's website: under Relationship's convention:

Note: If you have multiple relationships between the same types (for example, suppose you define the Person and Book classes, where the Person class contains the ReviewedBooks and AuthoredBooks navigation properties and the Book class contains the Author and Reviewer navigation properties) you need to manually configure the relationships by using Data Annotations or the fluent API. For more information, see Data Annotations - Relationships and Fluent API - Relationships.

Refer to this link for more information

UPDATED

A workaround will work in case of EDMX ( but it cost maintenance) as follows:

  1. Remove the foreign keys from the join table in the database
  2. Update the EDMX from database
  3. Recreate the foreign keys in the join table

this workaround will work as long as you will not update your Model from the database again.

Recommended solution, keep everything as it was generated by EDMX and learn more about how to use crud operation for this case using the following links that were reported "helpful" by the user '@TravisWhidden'

  1. Insert/Update Many to Many Entity Framework . How do I do it?
  2. https://www.youtube.com/watch?v=uMQwORSTGX4 ( video)
Petr Kolínek
  • 19
  • 4
Monah
  • 6,714
  • 6
  • 22
  • 52
  • That looks like it would be helpful if I had went with the code first method --- but because we are doing "EF Designer from Database", I cant find in the EDMX xml where data fluent api or data annotations can be set. – TravisWhidden Jul 20 '15 at 22:07
  • @TravisWhidden you can make a workaround, remove the primary keys from the join table, and after generating the edmx, put them back, and if the model didn't work because the table has no primary key, remove one of them and try – Monah Jul 20 '15 at 22:12
  • The removal of the primary key didn't help, but the removal of the foreign key constraints did. However, after putting them back, and seeing what happens when you press "update from database", the associations in XML return, and when you go to add an entity to the table, after SaveChanges() it throws an exception due to the assoc constraint. It wouldn't be reasonable to expect any dev on the team to remember to manually delete all the associations with the couple tables if they ever had to 'update' the EDMX file. So, though a possible solution, still holding out for a more maintainable one. – TravisWhidden Jul 20 '15 at 22:30
  • @TravisWhidden you might leave everything as the EDMX was generated and to learn how to make crud operations on many-to-many relation, check this link : http://stackoverflow.com/questions/4253165 – Monah Jul 20 '15 at 22:35
  • @TravisWhidden also you can watch this video to learn more about Many to many relationship : https://www.youtube.com/watch?v=uMQwORSTGX4 – Monah Jul 20 '15 at 22:37
  • OK, I see it now. The answer in that link for Many-to-Many gave me a clue. Some refactoring to support the EF6 model is probably better then trying to shoehorn our legacy designs in. tableA.TableC.Add(tableC); accomplishes the same thing for us, without having to manage any CRUD factories. In some ways, this may be simpler (though the refactor will take some time). I would like to give more credit to you on this, but the written answer isn't really the solution. The comments really provide the better direction. Revise the answer to redirect to that link as a hint to others. – TravisWhidden Jul 20 '15 at 23:47
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83812/discussion-between-traviswhidden-and-hadi-hassan). – TravisWhidden Jul 20 '15 at 23:47
  • @TravisWhidden i will update my solution to include the link – Monah Jul 21 '15 at 05:26
3

As mentioned by @Hadi Hassan, EF will not “Expose” or recognize Relational Tables that are composed exclusively of other Entities.

Work Around:
If you only need to ‘READ’ the data you can

  1. Define a view in your Schema for TableB.
  2. Then do a Model (.EDMX) Update from DB (select the Update Views)
  3. You will now be able to query your TableB data using your EF Context.

If you need to modify (Create,Update,Destroy) records in your TableB

  1. Create Stored Procedures in your Schema, accordingly.

  2. Import your Procs as Function into Your EF Model

  3. You can now call those Functions from your model Context for the rest of your CRUD operations.

DaniDev
  • 2,471
  • 2
  • 22
  • 29
0

The easiest way I've found around this is to add an autoincrement field to the table and make this sole field the primary key. Then add a unique constraint consisting of the two foreign keys. You might need to drop the table and recreate it in order to set it up this way because the database will not like dropping the existing PK relationships.

Then in the Entity model delete the table and also any navigation properties Entity may have created related to the relationships in this table. Save your model, rebuild, and then update From Database in the diagram. Your table should now appear to Entity as a regular table.

Bert Whetstone
  • 1