Row_Number Over (Partition by Xxx) in Linq

Row_number over (Partition by xxx) in Linq?

B"H

I know this is old. But why isn't the solution simply?

var o = beatles.GroupBy(x => x.inst)
               .SelectMany(g =>
                   g.Select((j, i) => new { j.inst, j.name, rn = i + 1 })
               );

Solution for complex LINQ query with ROW_NUMBER() and PARTITION BY

This was my solution:

After a long search, I found the next (magical) solution. Works like hell for me:

public IEnumerable<Showtime> MovieNext(){

  return _context.Showtime
   .FromSqlRaw("SELECT tbl.* FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "HallId" ORDER BY "StartAt") row 
    FROM myDb."Showtime" 
    WHERE "StartAt" > now()) tbl 
    JOIN myDb."Movie" M ON "MovieId" = M."Id" 
    WHERE tbl.row = 1 ORDER BY "HallId"");
}

Row_number over (Partition by yyy) in Linq?

@Adil Mammadov right, it's becouse of LINQ can't translate method to SQL.

To avoid this problem you have several solutions:

1. Specify MS SQL computed column and just grab it with LINQ
2. Write your ows translation to SQL
3. How @Matt Burland says - get result and then process it

I suppose in your situation problem can be solved like this:

var q = ctx.tblPrices.OrderByDescending(x => x.Cost)
                .ToList()
                .GroupBy(x => x.ItemID)
                .Select(g => new {g, count = g.Count()})
                .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new {j.WebPrice, j.PriceID, j.ItemID}));

Or even like this:

var q = ctx.tblPrices.OrderByDescending(x => x.Cost)
                .GroupBy(x => x.ItemID)
                .Select(g => new {g, count = g.Count()})
                .ToList()
                .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new {j.WebPrice, j.PriceID, j.ItemID}));

You also can check this my answer.

Multiple Row_number in LINQ

Try to use another way to get the result with LINQ. Get the previous record with ID < the current Id and check if all fields the same:

  var Res = Tooling.Where(x=>{ var r = Tooling.Where(y=>y.Id<x.Id).OrderByDescending(y=>y.Id).FirstOrDefault();
                                     if (r==null) return true;
                                     return !((r.Name==x.Name) && (r.Status==x.Status) && (r.DateFinished==x.DateFinished));
                                    })
                         .OrderBy( x => x.Id)
                         .Select(x=>x);

UPD: Here is a test routine:

public class TollingRecord
{

    public int Id;
    public String Name;
    public int Status;
    public DateTime? DateFinished;

}

...

private static void TestT1()
        {
            TollingRecord[] Tooling = new TollingRecord[]{ new TollingRecord() {Id=1, Name="Large", Status=0, DateFinished=null },
                            new TollingRecord()  {Id=2, Name="Large", Status=1, DateFinished=null},
                            new TollingRecord()  {Id=3, Name="Small", Status=0, DateFinished=null},
                            new TollingRecord()  {Id=4, Name="Large", Status=2, DateFinished=null},
                            new TollingRecord()  {Id=5, Name="Large", Status=2, DateFinished=null},
                            new TollingRecord()  {Id=6, Name="Large", Status=1, DateFinished=null},
                            new TollingRecord()  {Id=7, Name="Large", Status=1, DateFinished=null},
                            new TollingRecord()  {Id=8, Name="Small", Status=1, DateFinished=DateTime.Now},
                          };

            var Res = Tooling.Where(x=>{ var r = Tooling.Where(y=>y.Id<x.Id).OrderByDescending(y=>y.Id).FirstOrDefault();
                                         if (r==null) return true;
                                         return !((r.Name==x.Name) && (r.Status==x.Status) && (r.DateFinished==x.DateFinished));
                                        })
                             .OrderBy( x => x.Id)
                             .Select(x=>x);

            foreach (var a in Res)
            {
                Console.WriteLine("{0}/{1}/{2}", a.Id,a.Name,a.Status);
            }

        }

Outputs:

1/Large/0
2/Large/1
3/Small/0
4/Large/2
6/Large/1
8/Small/1

LINQ to Entities does not recognize the method (Partition/Rownumber)

Basically, it is unable to convert the Zip to SQL.

All you need to do is execute the query before using that method

IQueryable<Checksum> iQ2 = iQ.GroupBy(x => x.LastUpdateTimeStamp)
                             .Select(g => new { g, count = g.Count() })
                             .ToList() // Executes the Query
                             .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new Checksum { SortColumn = j.LastUpdateTimeStamp, Identifier = j.SigninId, Seqnum = i }));

---

Related Topics