Row_number over (Partition by yyy) in Entity Framework

Question

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

this query is my expectance.

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

I have more than 20000000 records in SerialFlows table.

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

Answer 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.

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.

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

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

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