C#(037):Row_number over (Partition by yyy) in Entity Framework(C#(037):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)
    }
————————

1. Filter the first row in each group

    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

Convert to LINQ:

First, they are grouped according to the content in the partition by clause, and each group is sorted by date. Each group is then projected to contain each entry and its index. Then selectmany
Flatten all groups, then apply filters, and finally project the results you want.

The following code will filter only the first line in each group:

    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. Retrieve the latest records for each group

    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]

One method:

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

Another method:

Queries for grouping keys and related sub queries for values must be performed manually using 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. Examples

Take the first three items of each group

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

Note that there are associated queries:

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