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())
)
Advantages of Using LINQ LINQ offers the following advantages: LINQ offers a common syntax for querying any type of data sources. Secondly, it binds the gap between relational and object-oriented approachs. LINQ expedites development time by catching errors at compile time and includes IntelliSense & Debugging support.
More importantly: when it comes to querying databases, LINQ is in most cases a significantly more productive querying language than SQL. Compared to SQL, LINQ is simpler, tidier, and higher-level. It's rather like comparing C# to C++.
LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it's no longer under active development.
LINQ syntax is typically less efficient than a foreach loop. It's good to be aware of any performance tradeoff that might occur when you use LINQ to improve the readability of your code. And if you'd like to measure the performance difference, you can use a tool like BenchmarkDotNet to do so.
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
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
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With