CODEWITHSUNDEEP
c#linqdense-rank
Mike Turner
Mike Turner

Reputation: 471

implement dense rank with linq

Using the following linq code, how can I add dense_rank to my results? If that's too slow or complicated, how about just the rank window function?

var x = tableQueryable
    .Where(where condition)
    .GroupBy(cust=> new { fieldOne = cust.fieldOne ?? string.Empty, fieldTwo = cust.fieldTwo ?? string.Empty})
    .Where(g=>g.Count()>1)
    .ToList()
    .SelectMany(g => g.Select(cust => new {
        cust.fieldOne
    ,   cust.fieldTwo
    ,   cust.fieldThree
    }));

Upvotes: 6

Views: 7663

Answers (3)

Mohammad Komaei
Mohammad Komaei

Reputation: 9656

var denseRanks = myDb.tblTestReaderCourseGrades
.GroupBy(x => new { x.Grade })
.OrderByDescending(g => g.Key.Grade)
.AsEnumerable()
.Select((g, i) => new { g, i })
.SelectMany(x =>
    x.g.Select(y => new
    {
        y.Serial,
        Rank = x.i + 1,
    }
));

enter image description here

Upvotes: 0

Jeff Mercado
Jeff Mercado

Reputation: 134571

So if I understand this correctly, the dense rank is the index of the group it would be when the groups are ordered.

var query = db.SomeTable
    .GroupBy(x => new { x.Your, x.Key })
    .OrderBy(g => g.Key.Your).ThenBy(g => g.Key.Key)
    .AsEnumerable()
    .Select((g, i) => new { g, i })
    .SelectMany(x =>
        x.g.Select(y => new
        {
            y.Your,
            y.Columns,
            y.And,
            y.Key,
            DenseRank = x.i,
        }
    );

Upvotes: 4

Gilad Green
Gilad Green

Reputation: 37281

This does a dense_rank(). Change the GroupBy and the Order according to your need :) Basically, dense_rank is numbering the ordered groups of a query so:

var DenseRanked = data.Where(item => item.Field2 == 1)
    //Grouping the data by the wanted key
    .GroupBy(item => new { item.Field1, item.Field3, item.Field4 })
    .Where(@group => @group.Any())

    // Now that I have the groups I decide how to arrange the order of the groups
    .OrderBy(@group => @group.Key.Field1 ?? string.Empty)
    .ThenBy(@group => @group.Key.Field3 ?? string.Empty)
    .ThenBy(@group => @group.Key.Field4 ?? string.Empty)

    // Because linq to entities does not support the following select overloads I'll cast it to an IEnumerable - notice that any data that i don't want was already filtered out before
    .AsEnumerable()

    // Using this overload of the select I have an index input parameter. Because my scope of work is the groups then it is the ranking of the group. The index starts from 0 so I do the ++ first.
    .Select((@group , i) => new
    {
       Items = @group,
       Rank = ++i
    })

    // I'm seeking the individual items and not the groups so I use select many to retrieve them. This overload gives me both the item and the groups - so I can get the Rank field created above
    .SelectMany(v => v.Items, (s, i) => new
    {
       Item = i,
       DenseRank = s.Rank
    }).ToList();

Another way is as specified by Manoj's answer in this question - But I prefer it less because of the selecting twice from the table.

Upvotes: 8

Related Questions