LINQ SQL rank for objects

Question

I have an SQL query that evaluates participants in multiple fields. I need to convert this to LINQ, which, as I understand it, does not have a rank function. Can someone help in converting this?

If that helps, here is what he does. This query pulls the participants out of the position table and evaluates them based on the fields listed by RANK() OVER (ORDER BY W desc, L asc, RW asc, RL desc, HP desc, TB desc) AS RANK . Then I take only those that have been ranked 1 or 2 Where q1.RANK in ('1','2') , and see if there are links for these two rankings Having count(q1.ParticipantID) > 1

 Select q1.RANK, count(q1.ParticipantID) as 'Count' From ( Select Distinct ParticipantID, RANK() OVER (ORDER BY W desc, L asc, RW asc, RL desc, HP desc, TB desc) AS RANK From vGroupStandings Where CompetitionID = 8 and GroupNumber = 1 and EventID = 6 ) as q1 Where q1.RANK in ('1','2') Group By q1.RANK Having count(q1.ParticipantID) > 1 

UPDATE

Here is the data that selects all fields

Here is an example of what the filtered data looks like in a subquery. From this set, I look to see if there are more than 1 entries ranked in rank 1 or rank 2.

REACTION

Thanks for the answers, I'll let you know when I can try them. Here's another question. Would it be better to call the stored procedure from the controller? That way I can leave the SQL query as it is. I have a number of large queries that I will need to run involving the rank. Interestingly, it would be easier than rewriting everything in LINQ.

Answer 1

This is pretty ugly, but works for me using this class of patterns.

 public class Participant { public int Id { get; set; } public int Score1 { get; set; } public int Score2 { get; set; } public int ExpectedRank { get; set; } } 

In this collection:

 var participants = new Participant[] { new Participant { Id = 1, Score1 = 2, Score2 = 5, ExpectedRank = 6 }, new Participant { Id = 2, Score1 = 10, Score2 = 8, ExpectedRank = 1 }, new Participant { Id = 3, Score1 = 7, Score2 = 2, ExpectedRank = 4 }, new Participant { Id = 4, Score1 = 7, Score2 = 4, ExpectedRank = 3 }, new Participant { Id = 5, Score1 = 7, Score2 = 2, ExpectedRank = 4 }, new Participant { Id = 6, Score1 = 7, Score2 = 7, ExpectedRank = 2 }, }; 

By executing the following pretty ugly LINQ query:

 var ranked = participants .OrderByDescending(p => p.Score1) .ThenByDescending(p => p.Score2) .Select((p, i) => new { Order = 1 + i, Participant = p }) .GroupBy(p => new { p.Participant.Score1, p.Participant.Score2 }) .SelectMany(g => g.Select(p => new { Id = p.Participant.Id, Rank = g.Min(x => x.Order), ExpectedRank = p.Participant.ExpectedRank })); foreach (var p in ranked) Console.WriteLine(p); 

Which produces the following output:

 { Id = 2, Rank = 1, ExpectedRank = 1 } { Id = 6, Rank = 2, ExpectedRank = 2 } { Id = 4, Rank = 3, ExpectedRank = 3 } { Id = 3, Rank = 4, ExpectedRank = 4 } { Id = 5, Rank = 4, ExpectedRank = 4 } { Id = 1, Rank = 6, ExpectedRank = 6 } 

Answer 2

Something like this ... this is pseudo-code .... I have not tested.

If you provided example data and the expected result, I would do it

 var inner = datasource .OrderByDesc(x => xW) .ThenBy(x => xL) // etc for all orders you need .GroupBy(new { W = W, L = L, RW = RW, RL = RL, HP = HP, TB = TB }) .First(2); if (inner[0].Count > 1 || inner[1].Count[1] > 1) { Console.Writeline("1 "+inner[0].Count.ToString()); Console.Writeline("2 "+inner[1].Count.ToString()); }