Using a MVC 4 Code First project I noticed that my intermediate table between a many to many relationship was missing. In other words, Table 1 has many Table 3, and Table 3 has many Table 1. In the database schema, Table 2 is my intermediate table:
- Table 1 has many Table 2 and Table 2 has only one Table1
- Table 3 has many Table 2 and Table 2 has only one Table3, as follows:
However, as I stated earlier, I did not have a model class for Table 2. At first I was miffed, but once I was able to craft the query, I was impressed at how easy EF made this. I wanted to get a listing of Table 3 fields for a specific ProjectID (pID) in Table 1. Here’s the query :)
var Table3Qry = from t3 in db.Table3
from t2 in t3.Table1.Where(x => x.ProjectID == pID)
select new { t3.foo1, t3.foo2, t3.foo3 };