LINQ and using GroupBy For For Web Formatting
In an upcoming post for the WebGallery2 project, I needed a method to return a group of galleries and files based on last visit date (as compared to the creation date of the gallery item). In TSQL, that’s easy enough like:
SELECT w.Name, w.GalleryId, g.Name from WebFiles w, Galleries g
WHERE w.DateCreated >= ’01/01/2008′
AND w.GalleryId = g.Id
GROUP BY g.Name, w.GalleryId, w.Name
From there, you can iterate through the results, and build your controls.
Now, how would you do that in LINQ? The GroupBy extension method.
The GroupBy extension method returns a result of IGrouping<K, V>, which you can then loop through in your application, create lists, etc.
For example, using the same logic as the TSQL above:
public List<IGrouping<Gallery, WebFile>> GetAllSinceLastVisit(
DateTime lastVisitDate, IPrincipal user)
var availableGalleries = GetGalleriesByRole(user);
.Where(i => i.DateCreated >= lastVisitDate &&
.GroupBy(i => i.Gallery)
This returns a List of IGrouping<K,V> with Galleries as the key and the WebFiles as values. To demonstrate how to build a quick list, we can write out an unordered list of “new” files since 1/1/2008. (the GetGalleriesByRoles ensures that its only parsing the galleries that the user has access to).
foreach (var result in db.GetAllSinceLastVisit(new DateTime(2008, 1, 1)))
Response.Write(result.Key.Name + “<br/>”);
foreach (var file in result)
Response.Write(“<li>” + file.Name + “</li>”);
With anonymous types, the var’s automagically detect what type of underlying object (Type) the key and values are and intellisense automatically picks up the properties and methods.
Since we have full access to the properties of each web file (and gallery), we could easily make these into hyperlinks or thumbnails by modifying the values loop (foreach var file in results):