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