Database of competition information

Question

I have the following (extremely simplified) database structure:

• Table: Competitions
  • Id: string, unique

• Table: Persons

  • Id: string, unique
  • Gender: string

• Table: Results

  • CompetitionId: string, references Id on the Competitions table
  • PersonId: string, references Id on the Persons table
  • EventId: string
  • RoundId: string
  • Average: int

Any Competition may hold n Results. Each Result is assigned to one Person.

I would like to get all Results (filtered for Average > 0, EventId == "333", RoundId == "f", such that it is the one with the lowest Average within the other Results having the same CompetitionId. Furthermore, I only want to get the Results of which the according person is female (gender == "f").

I currently use a weird mixed LINQ construct that is both, ugly and inefficient. The query takes around 3 minutes on my machine (local MySQL database, Results row count is something close to 200k).

I know there are elegant and efficient ways to use one LINQ query, creating temporary tables, joining and such. I am not that much into it, thus I coded this ugly piece:

var femalePersonIds =
 from p in Persons
 where p.Gender == "f"
 select p.Id;
var results333 =
 from r in Results
 where (r.Average > 0) && (r.EventId == "333") && (r.RoundId == "f")
 orderby r.Average
 select r;
foreach (var c in Competitions) {
 var results =
 from r in results333
 where (r.CompetitionId == c.Id)
 select r;
 if (results.Count() > 0) {
 var bestCompResult = results.First();
 if (femalePersonIds.Contains(bestCompResult.PersonId)) {
 bestCompResult.Dump();
 }
 }
}

(This is LINQPad 4 compliant)

I would love to see any efficiency, elegance and shortening hints, in case you have some.

• John Kraft: If I'm reading this correctly, you want to throw out the lowest female score in each competition?
• fjdumont: Exactly. Low average is good in this case, don't get me wrong ;)

Answer 1

I'm very confident a couple calls to ToList will solve your problems. Keep in mind that each interval of the loop will re-evaluate the two queries outside of the loop.

var femalePersonIds =
 (from p in Persons
 where p.Gender == "f"
 select p.Id).ToList();
var results333 =
 (from r in Results
 where (r.Average > 0) && (r.EventId == "333") && (r.RoundId == "f")
 orderby r.Average
 select r).ToList();
foreach (var c in Competitions) {
 var results =
 (from r in results333
 where (r.CompetitionId == c.Id)
 select r).ToList();
 if (results.Count() > 0) {
 var bestCompResult = results.First();
 if (femalePersonIds.Contains(bestCompResult.PersonId)) {
 bestCompResult.Dump();
 }
 }
}

• fjdumont: Whoa, I didn't even know it re-evaulates the queries each time. That's pretty much it, thanks! Other improvements would be great too - I would love to see just one LINQ query that does the job...
• Ed Swangren: Abstractions that leak like a sieve...

Answer 2

Try this one:

 var filteredResults =
 (from r in results 
 where r.Average > 0 &&
 r.EventID == "333" &&
 r.RoundID == "f"
 select r).ToList();
 List<Results> bestResults = new List<Results>(); 
 var resultsInCompenitions = filteredResults.GroupBy(r => r.CompetitionID);
 foreach (var resultsInCompetition in resultsInCompenitions)
 {
 var bestResultInCompetition = resultsInCompetition.OrderBy(r => r.Average).FirstOrDefault();
 if (bestResultInCompetition != null)
 {
 bestResults.Add(bestResultInCompetition); 
 } 
 }
 var femaleBestResults = 
 from r in bestResults
 join p in persons on r.PersonID equals p.ID
 where p.Gender == "f"
 select r; 
 return femaleBestResults; 

• fjdumont: Unfortunately, this one gets the best female competitors result from each competition. I'm looking for the best competitor who is female. I think I didn't make it clear enough in the thread, let me add some information...
• Ivan: I've changed the code, and just out of curiosity - is it correct now?
• fjdumont: Yep, works like a charm!

Answer 3

I may not totally understand what you're trying to do, but here's a go at it.

Results
 .Where(r => r.Average > 0 && r.EventId == "333" && r.RoundId == "f")
 .GroupBy(r => r.CompetitionId)
 .Where(r => r.Any(z => z.Person.Gender == "f"))
 .Dump();