implement dense rank with linq

Question

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
    }));

Your GroupBy method doesn't make sense. Shouldn't you be specifying a Func to extract the key? — Timothy Ghanem, Jul 17 '16 at 15:31
@TimothyGhanem Can you explain pls? i followed same syntax as http://www.codeducky.org/sql-queries-in-linq/#group-by — Mike Turner, Jul 17 '16 at 15:35
@user3185569 dense_rank() is an sql window function - https://msdn.microsoft.com/en-us/library/ms173825.aspx — Mike Turner, Jul 17 '16 at 15:36
@TimothyGhanem do you have a cleaner way of doing the group by, I'd love to see — Mike Turner, Jul 17 '16 at 15:40
@TimothyGhanem is it possible to do with a Func? if yes, would you post what you have in mind pls? — Mike Turner, Jul 17 '16 at 15:43
If your intention is to group by multiple columns, then your GroupBy is perfectly valid. I was just referring to grouping by a single column. — Timothy Ghanem, Jul 17 '16 at 15:46
@TimothyGhanem yes, that's part of my requirements, any idea about how to get the dense_rank function implemented? — Mike Turner, Jul 17 '16 at 15:49

Gilad Green · Accepted Answer · 2018-04-17T06:10:48.747

7

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.

edited Apr 17 '18 at 06:10

answered Jul 17 '16 at 17:20

Gilad Green

36,708
7
61
95

@MikeTurner - Edited to have more of your fields. You don't need to add the `AsQuerable` at the end and you should then have the `AsEnumerable()` after all the filters of your `Where`s. This example should work. If not then try and first have a list of dummy items of your class and see if it does the logic you need. Only at the end really get the data from the database and see – Gilad Green Jul 17 '16 at 18:37
@GiladGreen this code looks nice. Can you pls add some comments to help understand your logic? – Jul 18 '16 at 01:44
@Mike - ya you can play with it - each of these functions get an anonymous function - so here they are one liners but for each you can open a scope and do whatever you want :) However I think that if you add more logic to this query to determine your keys/order then might be better to take this logic out to suitable classes specifically for that. Will keep code cleaner, more testable and more extendable – Gilad Green Jul 18 '16 at 04:36
@MikeTurner - for the linq to entities `order by` to work you need to use it the way I wrote above. Here is [a question](http://stackoverflow.com/questions/13745812/ordering-in-linq-to-entities-multiple-columns) specifically about that. Now if you want to do this a more generic `dense_rank` function that will contain these `group by` and `order by` [see this](https://msdn.microsoft.com/en-us/library/mt693056.aspx) – Gilad Green Jul 18 '16 at 13:46
1

@MikeTurner - That is one way that can be fine, but I'd prefer to have an interface with specific implementations and then the class that runs the above code will get the interface as a dependency and use it to properly execute. [See Dependency Injection](https://en.wikipedia.org/wiki/Dependency_injection) - This is a more advanced concept so I'd start with the extensions and then try and do this. Linq.Dynamic can also be fine but I didn't work with it much yet :) – Gilad Green Jul 18 '16 at 13:54
Does this handle the distinct requirement of `DENSE_RANK` properly? – NetMage Jun 23 '17 at 20:45

Jeff Mercado · Answer 2 · 2021-03-16T08:46:58.100

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,
        }
    );

M Komaei · Answer 3 · 2022-11-21T18:37:56.973

0

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,
    }
));

edited Nov 21 '22 at 18:37

answered Nov 21 '22 at 18:31

M Komaei

7,006
2
28
34

implement dense rank with linq

3 Answers3

Linked