title: Row_number over (Partition by yyy) in Entity Framework
body: <p>I want to load data by Row_number over Partition by using EF.</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>this query is my expectance.</p>
  
  <p>I try to use this expression but unfortunately Zip extension method not recognize in ef</p>
  
  <pre><code>var goodsSerials = context.SerialFlows.OrderByDescending(x =&gt; x.Date).GroupBy(x =&gt; new { x.Detail.GoodsID, x.Date })
                      .Select(g =&gt; new {g})
                      .SelectMany(z =&gt; z.g.Select(c =&gt; c)).Zip(m, (j, i) =&gt; new { GoodSerial=j,j.Detail.FormTypeID,j.Detail.GoodsID,rn=i })
                      .Where(x =&gt; x.rn== 1 &amp;&amp; x.GoodsID== goodsId &amp;&amp; x.FormTypeID==7).Select(x =&gt; x.GoodSerial).ToList();
  </code></pre>
  
  <p>I have more than 20000000 records in SerialFlows table.</p>
  
  <p>**Edited</p>
  
  <pre><code>  var goodsSerials = context.SerialFlows
                                            .Where(e =&gt; e.Detail.GoodsID == goodsId )
                                            .GroupBy(x =&gt; x.GoodsSerialID)
                                            .Select(g =&gt; g.OrderByDescending(e=&gt;e.Date).Take(1))
                                            .SelectMany(e =&gt; e.Where(x=&gt;x.Detail.FormTypeID==7).Select(z=&gt;z.GoodsSerial)).ToList();
  </code></pre>
  
  <p>*** Solved by this Query</p>
  
  <pre><code>        var goodsSerials = context.SerialFlows
                                            .Include(x =&gt; x.Detail)
                                            .Where(e =&gt; e.Detail.GoodsID == goodsId)
                                            .GroupBy(x =&gt; x.GoodsSerialID)
                                            .Select(g =&gt; g.OrderByDescending(e =&gt; e.Date).Take(1).Where(x=&gt;x.Detail.FormTypeID==7))
                                            .SelectMany(e =&gt; e.Select(z =&gt; z.GoodsSerial)).ToList();
  </code></pre>