ASP.Net MVC Linq Grouping Query

Question

I am getting the last 20 updated records in the database by using the following

var files = (from f in filesContext.Files
                         join ur in filesContext.aspnet_Roles on f.Authority equals ur.RoleId
                         join u in filesContext.aspnet_Users on f.Uploader equals u.UserId
                         orderby f.UploadDate descending
                         select new FileInfo { File = f, User = u, UserRole = ur }).Take(20);

I am then splitting the results in my view:

<%foreach(var group in Model.GroupBy(f => f.UserRole.RoleName)) {%>
//output table here

This is fine as a table is rendered for each of my roles. However as expected I get the last 20 records overall, how could I get the last 20 records per role?

So I end up with:

UserRole1

//Last 20 Records relating to this UserRole1

UserRole2

//Last 20 Records relating to this UserRole2

UserRole3

//Last 20 Records relating to this UserRole3

Answer 1

You could either count the elements and skip the first (length - 20) elements or just reverse/take 20/reverse.

foreach (var group in Model.GroupBy(f => f.UserRole.RoleName))
{
    // draw table header
    foreach (item in group.Reverse().Take(20).Reverse())
    {
        // draw item
    }

    // Or
    int skippedElementCount = group.Count() - 20;
    if (skippedElementCount < 0) skippedElementCount = 0;

    foreach (item in group.Skip(skippedElementCount))
    {
        // draw item
    }

    // draw table footer
}

Answer 2

I can think of three possible ways to do this. First, get all the roles, then perform a Take(20) query per role, aggregating the results into your model. This may or may not be a lot of different queries depending on the number of roles you have. Second, get all the results, then filter the last 20 per role in your view. This could be a very large query, taking lots of time. Third, get some large number of results that will likely have at least 20 entries per role (but is not guaranteed) and then filter the last 20 per role in your view. I would probably use the first or third options depending how important it is to get 20 results.

var files = (from f in filesContext.Files
             join ur in filesContext.aspnet_Roles on f.Authority equals ur.RoleId
             join u in filesContext.aspnet_Users on f.Uploader equals u.UserId
             orderby f.UploadDate descending
             select new FileInfo { File = f, User = u, UserRole = ur })
           .Take(2000);


<% foreach (var group in Model.GroupBy( f => f.UserRole.RoleName,
                                     (role,infos) =>
                                         new {
                                               Key = role.RoleName,
                                               Selected = infos.Take(20)
                                             } ))                                           { %>

   <%= group.Key %>
   <% foreach (var selection in group.Selected)
      { %>
        ...