Database of competition information 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 &gt; 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 &gt; 0) &amp;&amp; (r.EventId == "333") &amp;&amp; (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() &gt; 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.
Try this one:

        var filteredResults =
        (from r in results             
         where r.Average &gt; 0 &amp;&amp;
               r.EventID == "333" &amp;&amp;
               r.RoundID == "f"
         select r).ToList();

        List&lt;Results&gt; bestResults = new List&lt;Results&gt;();            
        var resultsInCompenitions = filteredResults.GroupBy(r =&gt; r.CompetitionID);
        foreach (var resultsInCompetition in resultsInCompenitions)
        {
            var bestResultInCompetition = resultsInCompetition.OrderBy(r =&gt; 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;