3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ If I'm reading this correctly, you want to throw out the lowest female score in each competition? \$\endgroup\$ – John Kraft Aug 10 '11 at 14:41
  • \$\begingroup\$ Exactly. Low average is good in this case, don't get me wrong ;) \$\endgroup\$ – fjdumont Aug 10 '11 at 19:31
2
\$\begingroup\$

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();
        }
    }
}
\$\endgroup\$
  • \$\begingroup\$ 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... \$\endgroup\$ – fjdumont Aug 10 '11 at 18:54
  • \$\begingroup\$ Abstractions that leak like a sieve... \$\endgroup\$ – Ed S. Aug 11 '11 at 0:52
3
\$\begingroup\$

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;   
\$\endgroup\$
  • \$\begingroup\$ 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... \$\endgroup\$ – fjdumont Aug 10 '11 at 19:00
  • \$\begingroup\$ I've changed the code, and just out of curiosity - is it correct now? \$\endgroup\$ – Ivan Aug 10 '11 at 20:20
  • \$\begingroup\$ Yep, works like a charm! \$\endgroup\$ – fjdumont Aug 11 '11 at 6:17
0
\$\begingroup\$

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();
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.