Row_number over (Partition by yyy) in Entity Framework

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.