I have the following (extremely simplified) database structure:Table: CompetitionsId: string, uniqueTable: PersonsId: string, uniqueGender: stringTable: ResultsCompetitionId: string, references Id on the Competitions tablePersonId: string, references Id on the Persons tableEventId: stringRoundId: stringAverage: intAny 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.
Database of competition information
c#;performance;linq;database
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();        }    }}