c# - Join 2 tables to retrieve name and count linq Entity Framework

Question

I have 2 tables Orders and Items and I am trying to query these 2 tables for statistics retrieval.

• Orders has columns OrderID[PK], ItemID[FK], OrderStatus etc.
• Items has columns ItemID[PK], ItemName, ItemPrice etc.

I am fetching list of orders based on date range and then I am returning their counts based on their status.

Below is my StatisticsResponse.cs to return the response.

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<string,int> ItemOrders { get; set;}
}

This is how I am retrieving Orders between 2 dates.

var orders = _unitOfWork.OrderRepository
                        .GetMany(x => (x.OrderStatus == "Pending"
                                      && x.OrderDate.Value.Date >= dtStartDate 
                                      && x.OrderDate.Value.Date < dtEndDate) || 
                                      ((x.OrderStatus == "Completed" || x.OrderStatus == "Cancelled") 
                                        && x.DeliveryDate.Date >= dtStartDate || x.DeliveryDate.Date < dtEndDate) || (x.LastUpdated.Value.Date >= dtStartDate || x.LastUpdated.Value.Date < dtEndDate)).ToList();

if (orders != null)
{
    return new StatisticsResponse()
    {
          TotalOrders = orders.Count(),
          CancelledOrderCount = orders.Where(x => x.OrderStatus == "Cancelled").Count(),
          CompletedOrderCount = orders.Where(x => x.OrderStatus == "Completed").Count(),
          InProgressOrderCount = orders.Where(x => x.OrderStatus != "Completed" && x.OrderStatus != "Cancelled").Count()
    }
}

Now, in the ItemOrders property, which is of type Dictionary<string,int>, I want to group each item with their name and count. I have ItemID in my orders list, and I would like to join 2 tables to get the name before storing.

I have tried to use GroupBy as below but am totally stuck on how to get the name for the Item after grouping

ItemOrders = new Dictionary<string, int>
{
    orders.GroupBy(x=>x.ItemID)// Stuck here
}

I also read about GroupJoin but couldn't quite make sure whether it can fit in here.

Could someone please let me know how I can join these 2 tables to get their name based on their ID?

Answer 1

You can use something along this:

using System.Entity.Data; //to use Include()

...

Dictionary<string,int> itemOrders = dbContext.Orders.Include(o=> o.Item)
                        .GroupBy(o=> o.Item)
                        .ToDictionary(g => g.Key.Name, g => g.Count());

This is assuming:

• There is a navigation property set up from Order to Item
• Each Order has one Item