实体框架中的Row_number（由yyy分区）

I want to load data by Row_number over Partition by using EF.

我想通过使用EF在Row_number上分区加载数据。

    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

this query is my expectance.

这个问题是我的期待。

I try to use this expression but unfortunately Zip extension method not recognize in ef

我尝试使用这个表达式,但遗憾的是Zip扩展方法无法在ef中识别

var goodsSerials = context.SerialFlows.OrderByDescending(x => x.Date).GroupBy(x => new { x.Detail.GoodsID, x.Date })
                    .Select(g => new {g})
                    .SelectMany(z => z.g.Select(c => c)).Zip(m, (j, i) => new { GoodSerial=j,j.Detail.FormTypeID,j.Detail.GoodsID,rn=i })
                    .Where(x => x.rn== 1 && x.GoodsID== goodsId && x.FormTypeID==7).Select(x => x.GoodSerial).ToList();

I have more than 20000000 records in SerialFlows table.

我在SerialFlows表中有超过20000000条记录。

**Edited

  var goodsSerials = context.SerialFlows
                                          .Where(e => e.Detail.GoodsID == goodsId )
                                          .GroupBy(x => x.GoodsSerialID)
                                          .Select(g => g.OrderByDescending(e=>e.Date).Take(1))
                                          .SelectMany(e => e.Where(x=>x.Detail.FormTypeID==7).Select(z=>z.GoodsSerial)).ToList();

*** Solved by this Query

***通过此查询解决

        var goodsSerials = context.SerialFlows
                                          .Include(x => x.Detail)
                                          .Where(e => e.Detail.GoodsID == goodsId)
                                          .GroupBy(x => x.GoodsSerialID)
                                          .Select(g => g.OrderByDescending(e => e.Date).Take(1).Where(x=>x.Detail.FormTypeID==7))
                                          .SelectMany(e => e.Select(z => z.GoodsSerial)).ToList();

1 个解决方案

#1

From your SQL query, I think you need to first group them all by what in PARTITION BY clause, order each group by Date. Then project each group to include each entry with its index. Then SelectMany to flatten all groups, then apply filter and finally project the result you want. You can see that we don't need the so-called Zip at all.

从您的SQL查询中,我认为您需要首先按PARTITION BY子句中的内容对它们进行分组,按日期对每个组进行排序。然后对每个组进行投影以包含每个条目及其索引。然后SelectMany展平所有组,然后应用过滤器,最后投影所需的结果。你可以看到我们根本不需要所谓的Zip。

Edit: because you need to filter on row number but looks like the Select method accepting an Expression<Func<T,int,TResult>> is not supported (as well as the Zip method in Linq To Entity). I don't think it's the problem of building the Expression tree, meaning even building it manually, it still won't be supported. I think you can use some work-around in which you can still filter the row you want using Skip and Take instead.

编辑:因为您需要对行号进行过滤,但看起来像接受表达式 >(以及Linq To Entity中的Zip方法)。我不认为这是构建表达式树的问题,这意味着即使手动构建它,它仍然不会被支持。我认为你可以使用一些解决方法,你仍然可以使用Skip和Take来过滤你想要的行。

The following code will filter just the first row in each group (equivalent to the condition rn == 1):

以下代码将仅过滤每组中的第一行(相当于条件rn == 1):

var goodsSerials = context.SerialFlows
                          .Where(e => e.Detail.GoodsID == goodsId &&
                                      e.Detail.FormTypeID == 7)
                          .GroupBy(x => new { x.Detail.GoodsID, x.GoodsSerialId })
                          .Select(g => g.OrderByDescending(e => e.Date)
                                        .Take(1))
                          .SelectMany(e => e).ToList();

The Where filters for just 1 value of GoodsID so the GroupBy does not need to include the GoodsID into the key, so it would be simpler like this:

Where只过滤了1个GoodsID值,因此GroupBy不需要将GoodsID包含在密钥中,因此它会更简单:

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

I hope you understand the idea of using Skip and Take to apply in various cases.

我希望你理解在各种情况下使用Skip和Take的想法。

#1