1

I'm currently creating an MVC / AngularJS application using EF database first method. After implementing my database, I realize that I cannot reference many-to-many relations, using the Database-First approach.

An example could be Table1 has a many-to-many relation to Table2 through a third table called Table3. Table3 contains two primary keys: the PK of Table1 and the PK of Table2. Updating my EDMX model, I still can't add this table, which makes sense, since it's jsut a many-to-many relation, and this property should be set on both the Table1 class model and the Table2 class.

Problem
Using AngularJS as my front end, I need to convert the data I'm retrieving from the database, and into the HttpResponse (IHttpActionResult). I've had a problem like this before (using code-first) and simply disabled lazy loading (removed the virtual keyword from the properties in my EMDX model classes).

After disabling lazy loading, I can't seem to include the relation. Here's an example of what I mean:

from t1 in _context.Table1.Include(x => x.Table2)

The Table2 reference simply isn't included, and therefore the Icollection<Table2> is just null.

Does this even have anything to do with me removingthe virtual keyword from my property, or is this something that's gone wrong with the mapping of the two?

Mohammad Reza Shahrestani
  • 1,119
  • 3
  • 17
  • 28
Detilium
  • 2,868
  • 9
  • 30
  • 65
  • You need to join table. See msdn : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b – jdweng May 30 '17 at 06:30
  • But I can't. I have three tables, 2 of them are included in my model, but the last table is not, which contains the relation since it's a many-to-many. I'm pretty sure that I cannot join like this, unless you could show me an example? – Detilium May 30 '17 at 06:32
  • Please please please, do some research before posting a question at SO. There are lots of example to do many to many, few of them are - 1) http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx 2) https://stackoverflow.com/questions/19342908/how-to-create-a-many-to-many-mapping-in-entity-framework 3) https://stackoverflow.com/questions/5434125/entity-framework-codefirst-many-to-many-relationship-with-additional-information – brainless coder May 30 '17 at 06:36
  • @brainlesscoder Perhaps you should read my question first. All links you provided are regarding to code-first. I specifically mentioned that mine is DB-first. I'm not in doubt how to create a many-to-many in code-first, but that's not my problem either. – Detilium May 30 '17 at 06:39
  • 1
    @Detilium In that case try this - https://stackoverflow.com/questions/35527175/entity-framework-database-first-many-to-many – brainless coder May 30 '17 at 06:43
  • Doesn't matter if the 3rd table is not in model. The 3rd table still has the relationship between the other two tables. So you results is going to be many to many for each key. Then you are going to enumerate through each key results to get final results. – jdweng May 30 '17 at 06:55
  • @jdweng I'm not sure what you mean, perhaps you could elaborate? I would've thought that my `Include()` would work, am I not correct in that? – Detilium May 30 '17 at 07:18
  • Include doesn't specify which columns to join. See posting : https://stackoverflow.com/questions/26661771/what-does-include-do-in-linq – jdweng May 30 '17 at 10:37

3 Answers3

0

Since your Table3 only contains the Ids and no extra information, EF does not generate an entity for the joining table. That is the correct behavior and that's what you should expect.

Check this answer for more information. Entity Framework Database First many-to-many

samithagun
  • 664
  • 11
  • 25
  • I'm aware that EF doesn't create the intermediate table, but I do not understand why I cannot include the 2nd table. This answer doesn't really answer my question, just explains why the intermediate table doesn't exist in my EDMX model – Detilium May 30 '17 at 07:20
0

SOLVED

I have a query somewhat like this one:

from t1 in _context.Table1
select new SomeModel() 
{
    Table1 = t1,
    Table2 = t1.Table2
}

For me to actually include the Table2 property on t1, I can simply do a new select statement, like so:

from t1 in _context.Table1
select new SomeModel() 
{
    Table1 = t1,
    Table2 = from t2 in t1.Table2 select t2
}

I'm not sure whether this is best practice, but at least it works as expected for me. Proposals for better solutions are much welcome.

Detilium
  • 2,868
  • 9
  • 30
  • 65
  • 2
    Where is 3rd table? – jdweng May 30 '17 at 07:44
  • This is automatically mapped by EF, and the 3rd table is not in the EDMX model. This actually goes for all relations, even if it's a many-to-many relation or a simple many-to-one – Detilium May 30 '17 at 10:10
0

The simpliest solution I have found is to merely add a dummy third column to the join table. With that, EF will consider it important by itself, and make it an explicit entity.

James Curran
  • 101,701
  • 37
  • 181
  • 258