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:

public class UserData
{
 public string UserName { get; set; }
 public int TimeThisWeek { get; set; }
 public int RankThisWeek { get; set; }
}

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
   var allFromLastWeek = context.Sessions.AsNoTracking()
    .Include(x => x.User)
    .Where(x => x.SessionDateTimeUtc > lastWeek)
    .GroupBy(x => x.User.UserName)
    .Select(group => new UserData()
    {
     UserName = group.Key,
     TimeThisWeek = group.Sum(x => x.Time)
    }).OrderByDescending(x => x.TimeThisWeek).Take(10).ToList();

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:

using (TransactionScope scope = new TransactionScope())
{
 UserStore<ApplicationUser> store = new UserStore<ApplicationUser>(new PracticeTimeContext());
 ApplicationUser user = store.FindByNameAsync("student").Result;
 ApplicationUser user2 = store.FindByNameAsync("student2").Result;
 SessionRepository repo = new SessionRepository();
 Session retval1 = repo.Add(new Session()
 {
  C_InstrumentId = 1,
  SessionDateTimeUtc = DateTime.UtcNow,
  Time = 300,
  TimeZoneOffset = 300,
  Title = "blah",
  UserId = user.Id
 });
.....
 var retval = repo.GetTopUsersThisWeek();
 Assert.AreEqual(2,retval.Count);
 Assert.AreEqual(1,retval[0].RankThisWeek);
 Assert.AreEqual(2, retval[1].RankThisWeek);
 Assert.AreEqual(400, retval[0].TimeThisWeek);
 Assert.AreEqual(100, retval[1].TimeThisWeek);
 Assert.AreEqual("student",retval[0].UserName);
}

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
DMCA.com Protection Status