时间戳

首页 > 解决方案 > 涉及 Max() 的 SQL 到 LINQ 转换

c# - 涉及 Max() 的 SQL 到 LINQ 转换

问题描述

我想检索具有最新订单和总金额的前 5 位商家。但是，我似乎无法获得总金额。我认为问题出在LastOrderGrandTotal= x.FirstOrDefault().GrandTotal. 语法下面的代码采用 LINQ 方法语法

var list = _ApplicationDbContext.OrderTransaction
          .Where(x => x.Status != 0)
          .GroupBy(x => x.MerchantUserId)
          .Select(x => new DashboardActiveMerchantStore { MerchantUserId = x.Key, CreatedDate = x.Max(c => c.CreatedDate), LastOrderGrandTotal= x.FirstOrDefault().GrandTotal })
          .OrderByDescending(x => x.CreatedDate)
          .Take(5)
          .ToList();

为进一步说明，上述查询的 SQL 语法为

select TOP(5) MerchantUserId,MAX(CreatedDate),GrandTotal from OrderTransaction 
group by MerchantUserId
order by CreatedDate desc

我有一个类来存储从 LINQ 语法中检索的数据

public class DashboardActiveMerchantStore
    {
        public string MerchantName { get; set; }
        public double LastOrderGrandTotal { get; set; }
        public Guid MerchantUserId { get; set; }
        public DateTime CreatedDate { get; set; }

    }

标签： c#mysqlentity-frameworklinq

解决方案

当您使用实体框架时，一种完全不同的、更自然的方法是可能的。您可以使用virtual ICollection<...>.

我想检索具有最新订单和总金额的前 5 位商家。

实体框架中的类

Merchants和之间似乎存在一对多的关系OrderTransactions：每个 Merchant 都有零个或多个 OrderTransaction，并且每个 OrderTransaction 都是恰好发生在 Merchant 时的交易，即外键 MechantId 所指的 Merchant。

如果您遵循实体框架约定，您将拥有类似于以下内容的类：

public class Merchant
{
    public Guid Id {get; set;}      // primary key
    public string Name {get; set;}
    ...

    // Every Merchant has zero or more TransactionOrders (one-to-many)
    public virtual ICollection<TransactionOrder> TransactionOrders {get; set;}
}

public class TransactionOrder
{
    public Guid Id {get; set;}      // primary key
    public DateTime CreatedDate {get; set;}
    public int Status {get; set;}
    public double GrandTotal {get; set;}
    ...

    // every TransactionOrder is an order of a Merchant, using foreign key
    public Guid MerchantId {get; set;}
    public virtual Merchant Merchant {get; set;}
}

当然还有 DbContext：

public class OrderDbContext : DbContext
{
    public DbSet<Merchant> Merchants {get; set;}
    public DbSet<TransactionOrder> TransactionOrders {get; set;}
}

这就是实体框架检测表、表的列以及表之间的一对多关系所需的全部内容。只有当你偏离约定时，比如你想使用不同的属性名，或者不同的表名，你才需要属性或流畅的 API。

在实体框架中，表的列由非虚拟属性表示。虚拟属性表示表之间的关系（一对多、多对多）

获得前 5 名商家

如果你想要一些关于“商家......以及一些关于他们的 OrderTransactions 的信息”的信息，你可以从dbContext.Merchants;

如果您想要一些关于“OrderTransactions that ...，每个都有一些关于他们的商家的信息”的信息，你从dbContext.OrderTransactions.

我要查询最近订单和总金额前5位的商户

所以你想要Merchants. 从这些商家中，您至少想了解他们的Id和他们的Name，以及关于他们的一些信息OrderTransactions。

不是所有的 OrderTransaction，只有关于它们最后一个 OrderTransaction 的信息，其状态为非零。从最后一个 OrderTransaction 中，您需要 CreatedDate 和 GrandTotal。

既然您已经获得了具有最后一个非零状态订单的商家，您不需要所有这些商家，您只需要具有最新 CreatedDate 的五个商家。

我希望以上是您的要求。这不是您的 SQL 所说的，但您的 SQL 没有获取 Merchants，它获取了 TransactionOrders 组。

var result = dbContext.Merchants
    .Select(merchant => new
    {
        Id = merchant.Id,
        Name = merchant.Name,

        // from Every Merchant, get the Date and Total of their last
        // non-zero status Order
        // or null if there is no such order at all
        LastOrder = merchant.OrderTransactions
            .Where(orderTransaction => orderTransaction.Status != 0)
            .OrderByDescending(orderTransaction => oderTransaction.CreatedDate)
            .Select(orderTransaction => new
            {
                CreatedDate = orderTransaction.CreatedDate,
                GrandTotal = orderTransaction.GrandTotal,
            })
            .FirstOrDefault(),
    })

实体框架知道您的一对多关系。因为您使用它，virtual ICollection<...>它会自动为您创建 (Group-)Join。

现在您不想要所有商家，您不想要没有 LastOrder 的商家。他们没有任何非零状态的订单。从剩余的商户中，您只需要具有最新 LastOrder.CreatedDate 的五个商户。

因此，删除所有没有 LastOrders 的商家，按 LastOrder.CreatedDate 降序排列，然后取前 5 名。

继续 LINQ：

.Where(merchant => merchant.LastOrder != null) // at leas one Order with non-zero Status
    .OrderbyDescending(merchant => merchant.LastOrder.CreatedDate)
    .Take(5).

您将拥有“Merchants (Id and Name) with their LastOrder (CreatedData and GrandTotal)”，如果需要，添加一个额外的 Select 以将其转换为五个DashboardActiveMerchantStores

推荐阅读