Code Optimization

I had a devil of a time getting a LINQ situation to work efficiently. This is how I corrected it.

I am still new to LINQ and so I ran into a small issue when I was finding a way to tabulate some stats. Here was the original Question: How do I gather the top 10 goal scorers so far this season? Here is how I answered the question initially:

    My old code here

This works, however it is big, cumbersome and comparatively difficult to maintain. Question 2 is now: How do I make this better? After some searching on the internet I found:

Goals = await (from p in _context.FixtureActivityTb 
                    where p.ActivityType.Trim() == "G" 
                    group p by p.PlayerId into x 
                    join j in _context.PlayerTb on x.Key equals j.PlayerId 
                        select new Stats { Name = j.GivenName, pID = j.PlayerId, TeamId = j.TeamId, Count = x.Count() })
    .OrderByDescending(s => s.Count)
    .ThenBy(s => s.Name)
    .Take(10)
    .ToListAsync();

Because of limited data, I can’t tell if this is any faster than before, however it is clear(er), and should be a bit simpler to maintain going forward. Still this seems like a lot of code just to answer “Who are the top 10 goal scorers?”.

My new question is this: I should be able to do this using method syntax only. If you have any suggestions, please contact me using any of the methods above.

Here is the answer:

Goals = await _context.FixtureActivityTb
        .Where(p => p.ActivityType.Trim() == "G")
        .GroupBy(p => p.PlayerId)
        .Join(_context.PlayerTb, x => x.Key, j => j.PlayerId, (x, j)
            => new Stats
                {
                Name = j.GivenName,
                pID = j.PlayerId,
                TeamId = j.TeamId,
                Count = x.Count()
                })
        .OrderByDescending(s => s.Count)
        .ThenBy(s => s.Name)
        .Take(10)
        .ToListAsync();

Update: This no longer works as of EFCore 3.0 because not all of the query can be evaluated on the server.

Links