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.

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

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;   

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();