连接2个表以检索名称并计算linq Entity Framework [英] Join 2 tables to retrieve name and count linq Entity Framework

问题描述

我有2个表Orders和Items，我正在尝试查询这2个表以进行统计信息检索.

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

• Orders具有列OrderID[PK]，ItemID[FK]，OrderStatus等.
• Items具有列ItemID[PK]，ItemName，ItemPrice等.

• 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.

下面是我的StatisticsResponse.cs返回响应.

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;}
}

这就是我在2个日期之间检索Orders的方式.

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()
    }
}

现在，在类型为Dictionary<string,int>的ItemOrders属性中，我想使用名称和 count 对每个项目进行分组.我的orders列表中有ItemID，我想加入2个表以在存储前获取名称.

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.

我尝试如下使用GroupBy，但是完全陷入了如何在分组后获取商品名称的问题

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
}

我也读过有关GroupJoin的文章，但不能完全确定它是否适合这里.

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

有人可以让我知道如何连接这两个表以根据他们的ID获得他们的名字吗?

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

推荐答案

因此，我可以通过GroupJoin通过以下各种在线示例来实现此目标:

So, I was able to achieve this with GroupJoin through various online example as below:

if (orders != null)
{
    var items = _unitOfWork.ItemRepository.GetAll();
    var itemOrders = items.GroupJoin(orders,
                                     item => item.ItemID,
                                     ord => ord.ItemID,
                                     (item, ordrs) => new
                                     {
                                         Orders = ordrs,
                                         itemName = item.ItemName
                                     });
            StatisticsResponse statsResponse = new StatisticsResponse()
            {
                 //...
            };
            foreach (var item in itemOrders)
            {
                statsResponse.ItemOrders.Add(item.itemName, item.Orders.Count());
            }
            return statsResponse;
}

这篇关于连接2个表以检索名称并计算linq Entity Framework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！