CODEWITHSUNDEEP
c#sqllinqlinq-to-sql
Refracted Paladin
Refracted Paladin

Reputation: 12216

Converting SQL Rank() to LINQ, or alternative

I have the below SQL statement that works as desired/expected. However I would like to translate it into a LINQ statement(Lambda??) so that it will fit with the rest of my DAL. However I cannot see to figure out how to simulate Rank() in LINQ.

The reason I posted it here, which is maybe in error, is to see if anyone has an alternative to the Rank() statement so that I can get this switched over. Alternatively, if there is a way to represent Rank() in LINQ that would be appreciated also.

USE CMO

SELECT      vp.[PersonID] AS [PersonId]
            ,ce.[EnrollmentID]
            ,vp.[FirstName]
            ,vp.[LastName]
            ,ce.[EnrollmentDate]
            ,ce.[DisenrollmentDate]
            ,wh.WorkerCategory

FROM  [dbo].[vwPersonInfo] AS vp
            INNER JOIN 
            (
                  [dbo].[tblCMOEnrollment] AS ce
                  LEFT OUTER JOIN
                        (
                              SELECT   *
                                          ,RANK()OVER(PARTITION BY EnrollmentID ORDER BY CASE WHEN EndDate IS NULL THEN 1 ELSE 2 END, EndDate DESC, StartDate DESC) AS whrank 
                              FROM  [dbo].[tblWorkerHistory]
                              WHERE WorkerCategory = 2
                        ) AS wh 
                              ON ce.[EnrollmentID] = wh.[EnrollmentID] AND wh.whrank = 1
            ) 
                  ON vp.[PersonID] = ce.[ClientID]

WHERE (vp.LastName NOT IN ('Client','Orientation','Real','Training','Matrix','Second','Not'))
AND (
            (wh.[EndDate] <= GETDATE())
            OR wh.WorkerCategory IS NULL
      ) 
AND (
            (ce.[DisenrollmentDate] IS NULL) 
            OR (ce.[DisenrollmentDate] >= GetDate())
      )

Upvotes: 15

Views: 17112

Answers (4)

Totero
Totero

Reputation: 2574

Here's a sample that shows how I would simulate Rank() in Linq:

var items = new[]
{
    new { Name = "1", Value = 2 },
    new { Name = "2", Value = 2 },
    new { Name = "3", Value = 1 },
    new { Name = "4", Value = 1 },
    new { Name = "5", Value = 3 },
    new { Name = "6", Value = 3 },
    new { Name = "7", Value = 4 },
};
  
var q = from s in items
    orderby s.Value descending
    select new 
    { 
        Name = s.Name, 
        Value = s.Value,
        Rank = (from o in items
                where o.Value > s.Value
                select o).Count() + 1 
    };

foreach(var item in q)
{
    Console.WriteLine($"Name: {item.Name} Value: {item.Value} Rank: {item.Rank}");
}

OUTPUT

Name: 7 Value: 4 Rank: 1
Name: 5 Value: 3 Rank: 2
Name: 6 Value: 3 Rank: 2
Name: 1 Value: 2 Rank: 4
Name: 2 Value: 2 Rank: 4
Name: 3 Value: 1 Rank: 6
Name: 4 Value: 1 Rank: 6

Upvotes: 18

Daniel Kereama
Daniel Kereama

Reputation: 961

Based on answer from @Totero but with a lamda implementation. Higher score = higher rank.

var rankedData = data.Select(s => new{
                    Ranking = data.Count(x => x.Value > s.Value)+1,
                    Name = s.Key,
                    Score = s.Value});

For this input:

{ 100, 100, 98, 97, 97, 97, 91, 50 }

You will get this output:

  • Score : Rank
  • 100 : 1
  • 100 : 1
  • 98 : 3
  • 97 : 4
  • 97 : 4
  • 97 : 4
  • 91 : 6
  • 50 : 7

Upvotes: 1

Anders Abel
Anders Abel

Reputation: 69280

LINQ has rank funcionality built in, but not in the query syntax. When using the method syntax most linq functions come in two versions - the normal one and one with a rank supplied.

A simple example selecting only every other student and then adding the index in the resulting sequence to the result:

var q = class.student.OrderBy(s => s.studentId).Where((s, i) => i % 2 == 0)
.Select((s,i) => new
{
  Name = s.Name,
  Rank = i
}

Upvotes: 8

Manoj Pilania
Manoj Pilania

Reputation: 666

If you want to simulate rank then you can use following linq query.

      var q = (from s in class.student
                select new
                {
                    Name = s.Name,
                    Rank = (from o in class.student
                            where o.Mark > s.Mark && o.studentId == s.studentId
                            select o.Mark).Distinct().Count() + 1
                }).ToList();

you can use order by like: 

      var q = (from s in class.student
                orderby s.studentId 
                select new
                {
                    Name = s.Name,
                    Rank = (from o in class.student
                            where o.Mark > s.Mark && o.studentId == s.studentId
                            select o.Mark).Distinct().Count() + 1
                }).ToList();

but order by does not matter in this query.

Upvotes: 4

Related Questions