title: Displaying Many to Many Relationship in MVC view
body: <p>I am creating a database driven application in MVC, my application is a "data dictionary" which shows reports and what Database tables are used in those reports. I am using a database first approach for MVC.</p>
  
  <p>My database structure is as follows</p>
  
  <pre><code>*tblReport*
  fldReportID
  fldReportName
  fldReportPurpose
  fldOriginalSignOffDate
  
  *tblDBTables*
  fldTableID
  fldTableName
  
  *tblDBTablesUsed*
  fldDBTablesUsedID
  fldReportID
  fldTableID
  </code></pre>
  
  <p>tblDBTablesUsed is the many-to-many link table IE each report has many tables and each table can be used in many reports.</p>
  
  <p>i want to create a view page in MVC which displays the basic report information (held in tblReport) and also the information about the tables used in that report.</p>
  
  <p>I have created a model for each table as follows:</p>
  
  <pre><code>public partial class tblReport
  {
  
      public int fldReportID { get; set; }
      public string fldReportName { get; set; }
      public string fldReportPurpose { get; set; }
      public Nullable&lt;System.DateTime&gt; fldOriginalSignOffDate { get; set; }
  
  }
  
  
  public partial class tblDBTable
  {
  
      public int fldTableID { get; set; }
      public string fldTableName { get; set; }
  
  }
  
  public partial class tblDBTablesUsed
  {
      public int fldTablesUsedID { get; set; }
      public Nullable&lt;int&gt; fldReportID { get; set; }
      public Nullable&lt;int&gt; fldTableID { get; set; }
  
      public virtual tblDBTable tblDBTable { get; set; }
      public virtual tblReport tblReport { get; set; }
  }
  </code></pre>
  
  <p>and my Dbcontext class:</p>
  
  <pre><code>public partial class DataDictionaryEntities : DbContext
  {
  
      public virtual DbSet&lt;tblDBTable&gt; tblDBTables { get; set; }
      public virtual DbSet&lt;tblDBTablesUsed&gt; tblDBTablesUseds { get; set; }
      public virtual DbSet&lt;tblReport&gt; tblReports { get; set; }
  
  }
  </code></pre>
  
  <p>I've created a viewmodel to hold the two entities I want to display in the view:</p>
  
  <pre><code>public class ReportViewModel
  {
  
      public tblReport report = new tblReport();
      public IEnumerable&lt;tblDBTable&gt; dbtables;
  
  }
  </code></pre>
  
  <p>and finally, in my reports controller, I have created the details action method</p>
  
  <pre><code>    public ActionResult Details(int? id)
      {
  
          ReportViewModel rvm = new ReportViewModel();
  
          tblReport Report = db.tblReports.Find(id);
  
          //here we have an Ienumerable of the link table which gives us the table ID's used in this report
          IEnumerable&lt;tblDBTablesUsed&gt; DBTablesUsed = db.tblDBTablesUseds.Where(x =&gt; x.fldReportID == Report.fldReportID);
  
  
          //how to get the tables!?
          //DBTables = ??
  
          rvm.report = Report;
          rvm.dbtables = DBTables;
  
          return View(rvm);
      }
  </code></pre>
  
  <p>I am struggling to get the relevant tables- IEnumerable DBTablesUsed holds their IDs, but how do I get the records with those IDS from tblReport?</p>