如何将PostgreSQL查询转换为C# ASP.NET Core项目可用的LINQ或Lambda表达式

完全可以用LINQ或Lambda表达式实现和你原生PostgreSQL逻辑完全一致的查询，以下是可行实现方案：

前提假设：你已在项目DbContext中定义了映射DeliveryFinances表的DbSet：public DbSet<DeliveryFinance> DeliveryFinances { get; set; }，且DeliveryFinance实体包含与表字段对应的DriverId、OrderNo属性。

1. LINQ查询语法写法 #

// 子查询：获取每个DriverId对应的最小OrderNo
var minOrderNoQuery = from d in _context.DeliveryFinances
                      group d by d.DriverId into g
                      select new
                      {
                          DriverId = g.Key,
                          MinOrderNo = g.Min(x => x.OrderNo)
                      };

// 关联主表执行查询
var result = (from t1 in _context.DeliveryFinances
             join t2 in minOrderNoQuery on t1.DriverId equals t2.DriverId
             group t1 by new { t1.DriverId, t2.MinOrderNo } into g
             select new
             {
                 DriverId = g.Key.DriverId,
                 OrderNo = g.Key.MinOrderNo,
                 Count = g.Count()
             })
             .Take(25)
             .ToList();

2. Lambda方法语法写法 #

var result = _context.DeliveryFinances
    .Join(
        // 内嵌子查询逻辑
        _context.DeliveryFinances
            .GroupBy(d => d.DriverId)
            .Select(g => new { DriverId = g.Key, MinOrderNo = g.Min(x => x.OrderNo) }),
        t1 => t1.DriverId,
        t2 => t2.DriverId,
        (t1, t2) => new { t1.DriverId, t2.MinOrderNo }
    )
    .GroupBy(x => new { x.DriverId, x.MinOrderNo })
    .Select(g => new
    {
        DriverId = g.Key.DriverId,
        OrderNo = g.Key.MinOrderNo,
        Count = g.Count()
    })
    .Take(25)
    .ToList();

补充说明 #

• 可以调用IQueryable对象的ToQueryString()方法，直接输出生成的PostgreSQL语句和你原有SQL对比验证逻辑一致性
• 如果需要强类型接收结果，自定义DTO类替换上述代码中的匿名类即可

内容的提问来源于stack exchange，提问作者M.N. Waleedh