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 http://code.msdn.microsoft.com/LinqPersonalWeb.

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))
      connection.Open()
      Dim result As Object = command.ExecuteScalar
      Try
        Return New MemoryStream(CType(result, Byte()))
      Catch
        Return Nothing
      End Try
    End Using
  End Using
End Function

The corresponding Stored Procedure is as follows:

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 _
              HttpContext.Current.User.IsInRole("Administrators"))
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.