ASP.Net MVC Linq Grouping Query
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
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)
{ %>
...
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
}
精彩评论