Row_number over (Partition by yyy) in Entity Framework

1、过滤每组中的第一行

SELECT *
    FROM (
       SELECT sf.SerialFlowsId
                     ,sf.GoodsSerialId
                     ,d.FormTypeId
                     , d.GoodsId
                     ,ROW_NUMBER() OVER (PARTITION BY d.GoodsId, sf.GoodsSerialId ORDER BY sf.Date DESC)row
       FROM sam.SerialFlows sf
       INNER JOIN sam.Detail d ON d.DetailId = sf.DetailId
       )z
WHERE z.row =1 
       AND z.FormTypeId=7
       AND z.GoodsId=51532

转成Linq：

首先按 PARTITION BY 子句中的内容对它们进行分组，按日期对每个组进行排序。然后投影每个组以包含每个条目及其索引。然后 SelectMany 将所有组展平，然后应用过滤器，最后投影您想要的结果。

以下代码将仅过滤每组中的第一行：

var goodsSerials = context.SerialFlows
                          .Where(e => e.Detail.GoodsID == ‘51532’&&
                                      e.Detail.FormTypeID == 7)
                          .GroupBy(x => x.GoodsSerialId)
                          .Select(g => g.OrderByDescending(e => e.Date).Take(1))
                          .SelectMany(e => e).ToList();

2、检索每个组的最新记录

SELECT
    ListingId,
    NewFlagValueId AS ValueId
FROM
    (SELECT
        ListingFlagValues.ListingId,
        NewFlagValueId,
        [Timestamp],
        MAX([Timestamp]) OVER (PARTITION BY  ListingFlagValues.ListingId) AS MaxTimestamp
    FROM
        ListingFlagValues        
    WHERE 
        FlagId = 1) as FlagValues
WHERE [Timestamp] = [MaxTimestamp]

一种方法：

db.ListingFlagValues
  .GroupBy(x => x.Listing)
  .Select(x => new { Group = x, MaxTimestamp = x.Max(y => y.Timestamp) })
  .SelectMany(x => x.Group.Select(y => new { y.ListingId, ValueId = y.NewFlagValueId, y.Timestamp, x.MaxTimestamp }))
  .Where(x => x.Timestamp == x.MaxTimestamp);

另一种方法：

必须使用 Distinct 手动进行分组键的查询和值的相关子查询。

from listingId in db.ListingFlagValues.Select(x => x.ListingId).Distinct()
from lfv in db.ListingFlagValues
    .Where(x => x.ListingId == listingId)
    .OrderByDescending(e => e.Timestamp)
    .Take(1)
select lfv

3、实例

取每组的前三项

void Main()
{

//sql语句
//select t.id, t.Lineid, t.Createtime, t.ErrorCode, t.name
//from(select a.*, b.name, row_number() over(partition by a.Lineid order by a.Createtime desc) rid
//     from dbo.zhusuMachineStatus a
//          left
//     join zsEroorConfig b on a.ErrorCode = b.Code
//
//     where convert(varchar(20), a.Createtime, 102) > convert(varchar(20), getdate() - 10, 102)) as t
//where t.rid <= 3;



//1、用子查询,先查询出所有的lineID
from listingId in ZhusuMachineStatuses.Select(x => x.Lineid).Distinct()
from lfv in ZhusuMachineStatuses
 .Where(x => x.Lineid == listingId && x.Createtime.Value.Date > DateTime.Today.AddDays(-10))
    .OrderByDescending(e => e.Createtime)
    .Take(3)

select lfv

//表达式
ZhusuMachineStatuses
  .Select(x => x.Lineid)
  .Distinct()
  .SelectMany(
     listingId =>
        ZhusuMachineStatuses
           .Where(x => ((x.Lineid == listingId) && (x.Createtime.Value.Date > DateTime.Today.AddDays(-10))))
           .OrderByDescending(e => e.Createtime)
           .Take(3),
     (listingId, lfv) => lfv
  )

//2、使用GroupBy
    ZhusuMachineStatuses
    .Where(x => x.Createtime.Value.Date > DateTime.Today.AddDays(-10))
    .AsEnumerable()//可以取消此句
    .GroupBy(x => x.Lineid)
    .Select(g => g.OrderByDescending(e => e.Createtime).Take(3))//选择每组中的前三行
    .SelectMany(e => e.Select(t => t))

//3、选择每组中的最大日期
ZhusuMachineStatuses
  .Where(x => x.Createtime.Value.Date > DateTime.Today.AddDays(-10))
  .AsEnumerable()//可以取消此句
  .GroupBy(x => x.Lineid)
  .Select(x => new { Group = x, MaxTimestamp = x.Max(y => y.Createtime) })
  .SelectMany(x => x.Group.Select(y => new { y.Createtime, ValueId = y.ErrorCode, y.Lineid, x.MaxTimestamp }))
  .Where(x => x.Createtime == x.MaxTimestamp)
}

注意有关联查询的情况：

void Main()
{

    //sql语句
    //select t.id, t.Lineid, t.Createtime, t.ErrorCode, t.name
    //from(select a.*, b.name, row_number() over(partition by a.Lineid order by a.Createtime desc) rid
    //     from dbo.zhusuMachineStatus a
    //          left
    //     join zsEroorConfig b on a.ErrorCode = b.Code
    //
    //     where convert(varchar(20), a.Createtime, 102) > convert(varchar(20), getdate() - 10, 102)) as t
    //where t.rid <= 3;



    //1、用子查询
    from listingId in ZhusuMachineStatuses.Select(x => x.Lineid).Distinct()
    from lfv in (from lfv1 in ZhusuMachineStatuses
                 join b in ZsEroorConfigs on lfv1.ErrorCode.ToString() equals b.Code into rt
                 from b in rt.DefaultIfEmpty()
                 select new
                 {
                     Createtime = lfv1.Createtime,
                     ErrorCode = lfv1.ErrorCode,
                     Lineid = lfv1.Lineid,
                     Id = b.Id,
                     Name = b.Name
                 })
     .Where(x => x.Lineid == listingId && x.Createtime.Value.Date > DateTime.Today.AddDays(-10))
        .OrderByDescending(e => e.Createtime)
        .Take(3)
    select lfv

//表达式
    ZhusuMachineStatuses
   .Select(x => x.Lineid)
   .Distinct()
   .SelectMany(
      listingId =>
         ZhusuMachineStatuses
            .GroupJoin(
               ZsEroorConfigs,
               lfv1 => lfv1.ErrorCode.ToString(),
               b => b.Code,
               (lfv1, rt) =>
                  new
                  {
                      lfv1 = lfv1,
                      rt = rt
                  }
            )
            .SelectMany(
               temp0 => temp0.rt.DefaultIfEmpty(),
               (temp0, b) =>
                  new
                  {
                      Createtime = temp0.lfv1.Createtime,
                      ErrorCode = temp0.lfv1.ErrorCode,
                      Lineid = temp0.lfv1.Lineid,
                      Id = b.Id,
                      Name = b.Name
                  }
            )
            .Where(x => ((x.Lineid == listingId) && (x.Createtime.Value.Date > DateTime.Today.AddDays(-10))))
            .OrderByDescending(e => e.Createtime)
            .Take(3),
      (listingId, lfv) => lfv
   )




//2、使用GroupBy
    ZhusuMachineStatuses
     .Join(
           ZsEroorConfigs,
           lfv1 => lfv1.ErrorCode.ToString(),
           b => b.Code,
           (lfv1, b) =>
              new
              {
                  Createtime = lfv1.Createtime,
                  ErrorCode = lfv1.ErrorCode,
                  Lineid = lfv1.Lineid,
                  Id = lfv1.Id,
                  Name = b.Name
              }
        )
    .Where(x => x.Createtime.Value.Date > DateTime.Today.AddDays(-10))
    .AsEnumerable()//可以取消此句
    .GroupBy(x => x.Lineid)
    .Select(g => g.OrderByDescending(e => e.Createtime).Take(3))//选择每组中的前三行
    .SelectMany(e => e.Select(t => t))

//3、选择每组中的最大日期
    ZhusuMachineStatuses
    .Join(
           ZsEroorConfigs,
           lfv1 => lfv1.ErrorCode.ToString(),
           b => b.Code,
           (lfv1, b) =>
              new
              {
                  Createtime = lfv1.Createtime,
                  ErrorCode = lfv1.ErrorCode,
                  Lineid = lfv1.Lineid,
                  Id = lfv1.Id,
                  Name = b.Name
              }
        )
  .Where(x => x.Createtime.Value.Date > DateTime.Today.AddDays(-10))
  .AsEnumerable()//可以取消此句
  .GroupBy(x => x.Lineid)
  .Select(x => new { Group = x, MaxTimestamp = x.Max(y => y.Createtime) })//选择每组中的最大日期
  .SelectMany(x => x.Group.Select(y => new { y.Createtime, ValueId = y.ErrorCode, y.Lineid, x.MaxTimestamp }))
  .Where(x => x.Createtime == x.MaxTimestamp)
}