title: Join 2 tables to retrieve name and count linq Entity Framework
body: <p>I have 2 tables <code>Orders</code> and <code>Items</code> and I am trying to query these 2 tables for statistics retrieval.</p>
  
  <ul>
  <li><code>Orders</code> has columns <code>OrderID[PK]</code>, <code>ItemID[FK]</code>, <code>OrderStatus</code> etc.</li>
  <li><code>Items</code> has columns <code>ItemID[PK]</code>, <code>ItemName</code>, <code>ItemPrice</code> etc.</li>
  </ul>
  
  <p>I am fetching list of orders based on date range and then I am returning their counts based on their status.</p>
  
  <p>Below is my <code>StatisticsResponse.cs</code> to return the response.</p>
  
  <pre><code>public class StatisticsResponse
  {
      public int CancelledOrderCount { get; set; }
      public int CompletedOrderCount { get; set; }
      public int InProgressOrderCount { get; set; }
      public int TotalOrders { get; set; }
      public Dictionary&lt;string,int&gt; ItemOrders { get; set;}
  }
  </code></pre>
  
  <p>This is how I am retrieving <code>Orders</code> between 2 dates.</p>
  
  <pre><code>var orders = _unitOfWork.OrderRepository
                          .GetMany(x =&gt; (x.OrderStatus == "Pending"
                                        &amp;&amp; x.OrderDate.Value.Date &gt;= dtStartDate 
                                        &amp;&amp; x.OrderDate.Value.Date &lt; dtEndDate) || 
                                        ((x.OrderStatus == "Completed" || x.OrderStatus == "Cancelled") 
                                          &amp;&amp; x.DeliveryDate.Date &gt;= dtStartDate || x.DeliveryDate.Date &lt; dtEndDate) || (x.LastUpdated.Value.Date &gt;= dtStartDate || x.LastUpdated.Value.Date &lt; dtEndDate)).ToList();
  
  if (orders != null)
  {
      return new StatisticsResponse()
      {
            TotalOrders = orders.Count(),
            CancelledOrderCount = orders.Where(x =&gt; x.OrderStatus == "Cancelled").Count(),
            CompletedOrderCount = orders.Where(x =&gt; x.OrderStatus == "Completed").Count(),
            InProgressOrderCount = orders.Where(x =&gt; x.OrderStatus != "Completed" &amp;&amp; x.OrderStatus != "Cancelled").Count()
      }
  }
  </code></pre>
  
  <p>Now, in the <code>ItemOrders</code> property, which is of type <code>Dictionary&lt;string,int&gt;</code>, I want to group each item with their <strong>name</strong> and <strong>count</strong>. I have <code>ItemID</code> in my <code>orders</code> list, and I would like to join 2 tables to get the name before storing.</p>
  
  <p>I have tried to use <code>GroupBy</code> as below but am totally stuck on how to get the name for the Item after grouping</p>
  
  <pre><code>ItemOrders = new Dictionary&lt;string, int&gt;
  {
      orders.GroupBy(x=&gt;x.ItemID)// Stuck here
  }
  </code></pre>
  
  <p>I also read about <code>GroupJoin</code> but couldn't quite make sure whether it can fit in here.</p>
  
  <p>Could someone please let me know how I can join these 2 tables to get their name based on their <code>ID</code>?</p>