LINQ and EF6 to generate a top ten list


I recently added a top ten musicians of the week to my PracticeTime application.

Normally, I would have used some sort of view or stored procedure for this.  However, those strategies are hard to unit test and modify. I need data in the database to test if I use either database-centric approach.

LINQ and EF6 provides an easy way to achieve the same thing but allows for maintainable code. Also, because it’s C# the unit tests against it can be included in code coverage reports.

The end result is to a list of UserData objects:

The business rules are this:

  1. Only consider practice sessions from the last 7 days
  2. Sum all the practice sessions from that period
  3. Only show the top ten
  4. Rank by time in descending order
Honestly, it took me a while to get this.  The gotcha for me is that GroupBy returns a lists of lists, List<string,Session> in this instance. From that, it’s just a matter of using select to return a new UserData object populated with the UserName and summation of Time values. Lastly, sort and take the top ten.

Note that I have a foreign key between User (AspNetUser actually) and Session. This is required if you’re going to use this type of approach.

The unit tests is very simple. I add dummy data to the database and execute the method:

Notice, I wrap everything up in a TransactionScope? This lets me keep the database clean.

Key Points:

  • Use LINQ to simplify you data access queries
  • A data model with good referential integrity is important for LINQ
  • LINQ is easy to test and allows for measuring coverage