Doodle Graphics

As I said in my last post, I've rebuilt the site using ASP.NET MVC - other view engines are available, might be better, or at least better suited to your ideology, but this was the impetus I needed to actually start looking beyond the realm of Webforms.

The other main shift was from LINQ to SQL to using the Entity Framework (LINQ to Entities) as the data layer - once again, other ORMs are available, and don't have the baggage associated with being produced by Microsoft, but they also don't have the visual tooling - and while this site is primarily a place for me to play with cool new shiny toys, I do have a life, and more importantly a family, and didn't have all the time to learn how to configure them correctly or use them.

What I'm going to talk about today is my experiences moving from LINQ to SQL, some of the reasons behind why I moved, the benefits I saw, and the pain points I encountered - and to be honest, from what I've seen at the last two Tech.Eds, and read online, it looks like a lot of those pain points have been removed - although I'm sure there will a future post where I go over the "issues" I've encountered when upgrading

One of the key things that I disliked about LINQ to SQL was the fact that there was an absolute mapping between the tables in your database, and the classes in your model:

This is an image from my LINQ to SQL DBML file, and it looks exactly the same as the ERD that I'd see in SQL Management Studio's diagram of the database - finding the text for all the tags for a given image required code such as:

For each (var tag in photo.siteContent_TagsPhoto.siteContent_Tags) {

  tag.Text;

}

Which (regardless of the naming convention) was painful, and unpleasant, so what I ended up doing was creating a set of helper objects, with sensible names, that had the right collection of properties on them, including a list of tags, that I would then use as:

public static List<Photo> GetPhotos(int AlbumID) {

  var zhpCtx = new ZhaphCoreContentDataContext([...]);



  bool? filter = !(HttpContext.Current.User.IsInRole("Editors") ||

                   HttpContext.Current.User.IsInRole("SiteAdmin"));



  var photos = from p in zhpCtx.siteContent_Photos

               join a in zhpCtx.siteContent_Albums on p.AlbumID equals a.AlbumID

               where p.AlbumID == AlbumID && (a.IsPublic == filter || a.IsPublic)

               orderby p.PhotoID descending

               select new {p.PhotoID, p.AlbumID, p.Caption};



  var results = new List<Photo>(photos.Count());



  foreach (var photo in photos) {

    results.Add(new Photo(photo.PhotoID,

                          photo.AlbumID,

                          photo.Caption,

                          GetTags(photo.PhotoID, zhpCtx)));

  }



  return results;

}



private static List<string> GetTags(int photoID,

                                    ZhaphCoreContentDataContext zhpCtx) {

  return (from tp in zhpCtx.siteContent_TagsPhotos

          join t in zhpCtx.siteContent_Tags on tp.TagID equals t.TagID

          where tp.PhotoID == photoID

          select t.TagText).ToList();

}

There are some nice things in there - I can easily select just the elements from siteContent_Photos that I want - so I'm not pulling back the actual image data from the photo objects when I don't need it, however I've had to jump through all those hoops to get the text of the tags into the results list.

So what did I end up with Entity Framework?

Notice that I've lost the object representing the mapping table between Photos and Tags - great! I can now go directly from a Photo to its Tags, and from a Tag to the Photos.

BUT, this wasn't without its issues.

There were two main issues I had with Entity Framework version 1:

1. I was unable to lazy load properties in Entity Framework as I had done in LINQ to SQL
2. It was a lot harder to filter on complex CLR entities in Entity Framework than it was in LINQ to SQL.

Taking those points in greater detail then.

Lazy Loading Properties

As I showed above, I was able to easily select just those properties from an object that I wanted to load, projecting them out into a new class:

var photos = from p in zhpCtx.siteContent_Photos

             [...]

             select new {p.PhotoID, p.AlbumID, p.Caption};

So photos contains a set of implicitly typed objects, that have three properties: PhotoID, AlbumID and Caption - this is effectively the same as using the following SQL statement:

SELECT [t0].[PhotoID], [t0].[AlbumID], [t0].[Caption]

FROM [siteContent_Photos] AS [t0]

All that is retrieved from the database then is those three columns.

In Entity Framework I would say (method name has changed, but the result was the same, an object with a number of photos in it):

public Album GetAlbum(int albumId, bool loadPhotos) {

  ObjectQuery<Album> albums = loadPhotos ?

                       m_DoodleContent.Albums.Include("Photos").Include("Photos.Tags") :

                       m_DoodleContent.Albums;



  return albums.FirstOrDefault(a => a.AlbumId == albumId);

}

Which ended up loading all the content for each photo in the album - the reason I had to do it this way was that Entity Framework doesn't expose the AlbumID property of the Photo object either - so there was no way to say "give me all the photos with an AlbumID equal to x" as I had done in LINQ to SQL.

This also shows one futher issue that I've not fully resolved - as the TagsPhoto table is hidden from my object model, I can't query directly into that to build up the Tag Cloud that I had previously - were I'd been able to perform a GROUP in the SQL and get the COUNT of the tag ids from the TagsPhotos table, and then find the TagText for each tag.

Filtering on complex CLR types

Now, I've read a few explainations of why this behaviour exists, but none of them really convinced me fully.

In LINQ to SQL I could write the following, basically saying "get a blog post where the path (after the date) is equal to "path", and the Date the post was published on is equal to the date part of "publishedDate"":

BlogPosts post = (from blogs in blogPosts 

                  where blogs.PostPath == path &&

                        blogs.Published.Date == publishedDate.Date

                  select blogs).SingleOrDefault();

However, that wouldn't work with Entity Framework - changing "SingleOrDefault" to "FirstOrDefault" allowed it to compile, but the site would throw nasty exceptions at runtime, along the lines of "System.NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities".

I initially worked around this by searching for all posts with a given path, calling ToList() on the collection, and then searching that for date, but I didn't like that either, as it (obviously) loaded all the potentially matching blogs before it found just the one I wanted.

What finally worked for me was:

BlogPost post = (from blogs in blogPosts

                 where blogs.PostPath == path

                       && blogs.Published.Year == publishedDate.Year

                       && blogs.Published.Month == publishedDate.Month

                       && blogs.Published.Day == publishedDate.Day

                 select blogs).FirstOrDefault();

As Entity Framework did know how to pull out the Year, Month and Day properties of a DateTime object.

Overall neither of these were particularly big issues, but they caused some of the delays in getting the latest version of this site live - especially as most of the work on it is done late at night

Filed under: Entity Framework, LINQ