Implement Sql Server Rank and DenseRank Functions in 'LINQ Expressions'


In a class there are 100 students, and in a talent test they got some marks. I want to rank those students. To implement this, SQL Server provides two functions called Rank() and Dense_Rank(). Briefly, Rank() Returns the rank of each row in the result set of partitioned column. Dense_Rank() is same as RANK() function. Only difference is returns rank without gaps. Here i am providing those functions implementation in C# using LINQ expressions.

In data base, there is a table "tableToRankStudents" and contains Three columns
Id,
Name,
Marks

and accessing this using entity data model(EDMX)

dbContext is Entity model context.
Below is the code snippet.


#region Rank

var RestTable = from o in dbContext.tableToRankStudentses
select o;
var limitedUsers = from o in dbContext.tableToRankStudentses
orderby o.Marks descending
select new
{
Name = (from n in dbContext.tableToRankStudentses
where n.Marks == o.Marks & n.Id == o.Id
select n.Name).FirstOrDefault(),
TotalSales = o.Marks,
Position = RestTable.Count(s2 => s2.Marks > o.Marks) + 1
};
//Binding to Grid view
GrdRank.DataSource = limitedUsers.ToList();
GrdRank.DataBind();
#endregion

#region DenseRank
var limitedUsers = from o in dbContext.tableToRankStudentses
group o by new { o.Marks, o.Id }
into g
orderby g.Key.Marks descending
select new
{
Name =(from o in dbContext.tableToRankStudentses
where o.Marks == g.Key.Marks && o.Id == g.Key.Id
select o.Name).FirstOrDefault(),
TotalSalary = g.Key.Marks,
Position = (from o in dbContext.tableToRankStudentses
group o by o.Marks into l
select l).Count(s => s.Key > g.Key.Marks) + 1
};


OutPut


Attachments

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: