CODEWITHSUNDEEP
linqrow-numberpartition
Jerker Pihl
Jerker Pihl

Reputation: 137

Multiple Row_number in LINQ

I tried to translate 

select * 
from (
  select *, rng = row_number() over (partition by grp order by id)
  from (
    select *, grp = row_number() over (order by id) - row_number() over (partition by Name, Status, DateFinished order by id)
    from tooling ) g
  ) gn
where rng = 1
order by id

from an earlier question (Grouping with partition and over in TSql)

With help with Row_number over (Partition by xxx) in Linq? I got the solution to translate ONE of the row_number s but seems I'am out of luck to succesfully translate the entire question?
My attempt:

Tooling.OrderBy( x => x.Id)
    .GroupBy( x => new {x.Name,x.Status,x.DateFinished} )
    .Select( group => new { Group = group, Count = group.Count() } )
    .SelectMany( groupWithCount =>
        groupWithCount.Group.Select( b => b)
        .Zip(
            Enumerable.Range( 1, groupWithCount.Count ),
            ( j, i ) => new { j.Name,j.Status, j.DateFinished, RowNumber = i }
        )
    )

Upvotes: 1

Views: 166

Answers (1)

valex
valex

Reputation: 24144

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

Upvotes: 1

Related Questions