Row_number over (yyy section) in Entity Framework

Question

I want to load data using Row_number over Partition 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 request is my expectation.

I am trying to use this expression, but unfortunately the zip extension method is not recognized in ef

 var goodsSerials = context.SerialFlows.OrderByDescending(x => x.Date).GroupBy(x => new { x.Detail.GoodsID, x.Date }) .Select(g => new {g}) .SelectMany(z => zgSelect(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 over 20,000,000 entries in the 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 request

  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 is in PARTITION BY , sort each group by date. Then project each group onto each record with its index. Then SelectMany to smooth out all the groups, then apply the filter and finally project the desired result. You can see that we do not need the so-called Zip .

Edit : because you need to filter the line number, but it looks like this: the Select method accepting Expression<Func<T,int,TResult>> not supported (as well as the Zip method in Linq To Entity), I don’t think this is a creation problem expression tree, which means even creating it manually, but it still will not be supported. I think you can use some work in which you can still filter the desired line using Skip and Take .

The following code will filter only the first line 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 filters for only 1 GoodsID value, so GroupBy does not need to include GoodsID in the key, so it would be easier:

 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 for use in various cases.