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 > 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.

Try this one:

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

        List bestResults = new List();            
        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;

StackExchange Code Review Q#4008, answer score: 3