title: LINQ query to MVC Viewmodel multiple tables and lists
body: <p>I am trying to make one query to hit the database for 5 different tables to send to my view in one viewmodel. Here are my data classes.</p>
  
  <pre><code>public partial class Table_Parts_Warehouse
  {
      public int CQPN { get; set; }
      public int CATEGORY { get; set; }
      public int SUBCATEGORY { get; set; }
      public string DESCRIPTION { get; set; }
      public int QTY { get; set; }
      public Nullable&lt;int&gt; MINSTK { get; set; }
      public Nullable&lt;int&gt; MAXSTK { get; set; }
      public Nullable&lt;decimal&gt; COST { get; set; }
  }
  
  public partial class Table_Parts_Order
  {
      public int ORDERID { get; set; }
      public System.DateTime DATE { get; set; }
      public bool STATUS { get; set; }
      public string PONO { get; set; }
      public string INVOICENO { get; set; }
      public string QUOTENO { get; set; }
      public int COMPANYID { get; set; }
      public Nullable&lt;decimal&gt; COST { get; set; }
  }
  
  public partial class Table_Parts_OrderPart
  {
      public int ORDERPARTID { get; set; }
      public int ORDERID { get; set; }
      public int CQPN { get; set; }
      public int ORDERQTY { get; set; }
      public Nullable&lt;int&gt; RECVQTY { get; set; }
      public string PARTNO { get; set; }
  }
  
  public partial class Table_Parts_Vendor
  {
      public int COMPANYID { get; set; }
      public string PHONENO { get; set; }
      public string COMPANYNAME { get; set; }
  }
  
  public partial class Table_Parts_Part
  {
      public int PARTID { get; set; }
      public int CQPN { get; set; }
      public string PARTNO { get; set; }
      public decimal COST { get; set; }
      public int COMPANYID { get; set; }
      public int QTY { get; set; }
  }
  </code></pre>
  
  <p>My viewmodel class is here, all of these classes, the order class is a single order entry but the rest are all lists for the associated parts and data for that order.</p>
  
  <pre><code>public class OrderDetailsViewModel
  {
      public Table_Parts_Order tb_parts_order { get; set; }
      public List&lt;Table_Parts_OrderPart&gt; tb_parts_orderpart { get; set; }
      public List&lt;Table_Parts_Vendor&gt; tb_parts_vendor { get; set; }
      public List&lt;Table_Parts_Warehouse&gt; tb_parts_warehouse { get; set; }
      public List&lt;Table_Parts_Part&gt; tb_parts_part { get; set; }
  }
  </code></pre>
  
  <p>In my view controller, I am trying to do 1 query for all of this and select them in one go.  I can do this each individually with the same query and select the table and then .ToList() which works fine, but I am hitting the database 5 times instead of once.  Need help to select lists within this query.</p>
  
  <pre><code>public ActionResult PartsOrderDetails(int? id)
  {
      if (id == null)
          return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
  
  
      OrderDetailsViewModel vm = new OrderDetailsViewModel();
  
      vm = (from a in db.Table_Parts_Warehouse
                join b in db.Table_Parts_OrderPart
                on a.CQPN equals b.CQPN
                join c in db.Table_Parts_Part
                on b.PARTNO equals c.PARTNO
                join d in db.Table_Parts_Vendor
                on c.COMPANYID equals d.COMPANYID
                join e in db.Table_Parts_Order
                on b.ORDERID equals e.ORDERID
                orderby a.CQPN ascending
                where b.ORDERID == id
                select new OrderDetailsViewModel
                {
                    tb_parts_order = e,
                    tb_parts_orderpart = b, //(.ToList)
                    tb_parts_vendor = d, //(.ToList)
                    tb_parts_warehouse = a, //(.ToList)
                    tb_parts_part = c //(.ToList)
                });
  
      return View(vm);
  
  }
  </code></pre>
  
  <p>This obviously does not work because I cannot get lists like this.  I have seen where people make another query in select new but I dont want to repeat the query 5 times.  Whats the best way to achieve this?</p>