Personal Web Starter Kit LINQed up by ThinqLinq

Personal Web Starter Kit LINQed up

In case anyone is interested, I have put together a sample port of the original Personal Web Starter Kit using LINQ rather than the standard ADO data tier in the PhotoManager.vb class. With this version, we can eliminate all of the stored procedures and rely on LINQ for our entire data access. In this implementation, I intentionally attempted to retain the original method signatures where possible to make migration more seamless. The project site is at

This is one of the sample projects I use in my LINQ Migration strategies talks, so if you attended that talk, check out the sample project for some more concrete examples. Because the original example is fairly basic and the tiers are separated out neatly, doing a migration really only requires replacing code in one file: PhotoManager.vb. Let's take a look at a couple of the refactorings we did for this example.

The meat of the original project is a solution to group images into albums and store them in a database. To start, we create a new mapping file by creating a new LINQ to SQL Classes file. Onto this surface, drag the Album and Photo tables to generate the entity classes and associated mappings. With that in place, we can move our attention to the PhotoManager class which abstracts all of the data access.

The PhotoManager class has separate methods to GetPhoto, GetPhotos, AddPhoto, EditPhoto and RemovePhoto. The same is true for albums. Each of these maps to corresponding stored procedures. Let's compare the original implementation of GetPhoto with the LINQ enabled version. In the original, we see familiar code to create a datareader and Fetch a scalar result from the function.

Public Overloads Shared Function GetPhoto(ByVal photoid As Integer, ByVal size As PhotoSize) As Stream
  Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString)
    Using command As New SqlCommand("GetPhoto", connection)
      command.CommandType = CommandType.StoredProcedure
      command.Parameters.Add(New SqlParameter("@PhotoID", photoid))
      command.Parameters.Add(New SqlParameter("@Size", CType(size, Integer)))
      Dim Filter As Boolean = Not (HttpContext.Current.User.IsInRole("Friends") Or HttpContext.Current.User.IsInRole("Administrators"))
      command.Parameters.Add(New SqlParameter("@IsPublic", Filter))
      Dim result As Object = command.ExecuteScalar
Return New MemoryStream(CType(result, Byte()))
Return Nothing
End Try
End Using
End Using
End Function

The corresponding Stored Procedure is as follows:

@PhotoID int,
  @Size int,
  @IsPublic bit

IF @Size = 1
    SELECT TOP 1 [BytesThumb] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
  ELSE IF @Size = 2
    SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
  ELSE IF @Size = 3
    SELECT TOP 1 [BytesFull] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
  ELSE IF @Size = 4
    SELECT TOP 1 [BytesOriginal] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)
SELECT TOP 1 [BytesPoster] FROM [Photos] LEFT JOIN [Albums] ON [Albums].[AlbumID] = [Photos].[AlbumID] WHERE [PhotoID] = @PhotoID AND ([Albums].[IsPublic] = @IsPublic OR [Albums].[IsPublic] = 1)


For those unfamiliar with the sample solution, realize that the photos table contains multiple copies of each image rendered in different resolutions. The procedure is responsible for determining which field to return based on the size parameter that is passed in. By limiting the fields returned, we can optimize the IO requirements, but this does mean that any time we want to access the photos, we need to repeat the same logic. Indeed, the same If block used in the GetPhoto procedure is copied and reused in the GetFirstPhoto procedure. This does not lead for the kind of code maintainability we would like to see.

Naturally, since your reading about it here, I'm sure you would like to see how LINQ may offer a better alternative. Starting with the GetPhoto method, we can eliminate the late bound ADO code and provide a more strongly typed version of the same method. We will also be able to refactor and reuse more pieces of the query through-out the application. Here is the code for our GetPhoto method.

Public Overloads Shared Function GetPhoto(ByVal photoid As Integer, ByVal size As PhotoSize) As Stream
  Dim dc As New PersonalDataContext
  Dim query = From p In dc.Photos _
                      Where (p.PhotoID = photoid) And _
                                 (p.Album.IsPublic Or IsFriend)
  Return GetPhotoBytes(query, size)
End Function

Get photo greatly reduces the amount of plumbing code and allows us to focus on the desired results. GetPhoto returns the actual image that corresponds to the requested ID and size. It also checks to see if the user is allowed to see that photo by checking to see if the album is marked as a public album, or if the user is considered a friend based on their login credentials. Here's the implementation of the IsFriend method.

Public Shared Function IsFriend() As Boolean
Return (HttpContext.Current.User.IsInRole("Friends") Or _
End Function

Since the user's credentials are already cached for the current user, there is no need to requery that part of the database on every fetch.  The real key to this implementation lies in the GetPhotoBytes method. In this method, we evaluate the size parameter and dynamically extend our query to project just the field we want to consume.

Private Shared Function GetPhotoBytes(ByVal source As IQueryable(Of Photo), ByVal size As PhotoSize) As Stream
  Dim imageBytes As Byte()
  Select Case size
    Case PhotoSize.Large
      imageBytes = source.Select(Function(p) p.BytesFull).SingleOrDefault
    Case PhotoSize.Original
      imageBytes = source.Select(Function(p) p.BytesOriginal).SingleOrDefault
    Case PhotoSize.Small
      imageBytes = source.Select(Function(p) p.BytesThumb).SingleOrDefault
    Case Else
imageBytes = source.Select(Function(p) p.BytesPoster).SingleOrDefault
  End Select
If imageBytes IsNot Nothing Then
Return New MemoryStream(imageBytes)
Return New MemoryStream()
  End If
End Function

Here we extend the initial query and add custom projection to it. When we issue the query to the database, the resulting SQL statement wraps the functionality we declared in the GetPhoto with the GetPhotoBytes to create a single statement which only returns the image stream that we requested. What's better is that we can now reuse this same GetPhotoBytes method in the GetFirstPhoto implementation, passing a different baseline query.

Public Shared Function GetFirstPhoto(ByVal albumid As Integer, ByVal size As PhotoSize) As Stream
  Dim dc As New PersonalDataContext
  Dim query = From p In dc.Photos _
                      Where p.AlbumID = albumid And (p.Album.IsPublic Or IsFriend()) _
                      Take 1
  Return GetPhotoBytes(query, size)
End Function

There. Nicely refactored and no more copy-paste inheritance in the database.

If you're interested in looking at this implementation further, check out the project site at Also, let me know if you would like to see other starter kits migrated to LINQ and I'll see what I can do.

Posted on - Comment
Categories: VB Dev Center - LINQ - VB -
comments powered by Disqus