实体框架中的行数超过(按 yyy 分区) [英] Row_number over (Partition by yyy) in Entity Framework

问题描述

我想使用 EF 按 Row_number 通过 Partition 加载数据.

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

    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

这个查询是我的期望.

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

I try to use this expression but unfortunately Zip extension method not recognize in 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();

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

I have more than 20000000 records in SerialFlows table.

**已编辑

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

*** 此查询已解决

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

推荐答案

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

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.

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

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.

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

The following code will filter just the first row in each group (equivalent to the condition 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();

Where 仅过滤 GoodsID 的 1 个值，因此 GroupBy 不需要包含 GoodsID进入密钥，所以它会更简单:

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:

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

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

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

这篇关于实体框架中的行数超过(按 yyy 分区)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！