LinqPad and Entity Framework

Posted 31 May 2011, 16:36 | by | | Perma-link

I'm a massive fan of LINQPad - it's much more lightweight than SQL Management Studio if you want to muck around with your data, and allows me to work against SQL with C#, rather than having to remember how to use cursors and the like when I've got some otherwise tedious data re-mapping to do.

That said, I'd never really pointed it at an Entity Framework EDM before, but I'd been wondering about the best way to tidy up the code for the tag clouds in the albums section of this site, so thought that I should see what I could do with the entity model as it stood, without resorting to stored procs.

Firing up the "Add Connection" wizard, I selected "Entity Framework" from the "Use typed data context from your own assembly", selected the website library, selected the (only) EDM from the library, and confirmed the provider.

LINQPad then created the data context correctly, so I pulled up a query to start working against it, only to get the following error message:

An assembly with the same identity 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' has already been imported. Try removing one of the duplicate references.

This was slightly confusing - obviously both my project and LINQPad are referencing the System.Data.Entity namespace, and if I remove the reference from my project, I won't be able to build it. How do I remove it from LINQPad then?

It turns out that I don't have to do either. The clue (for me) lay in the notes section of the "Using LINQPad with Entity Framework" page:

If your assembly references other assemblies, LINQPad will pick up these, too, providing they live in the same folder.

Note the bit in bold from the original page - it would appear that my reference to the System.Data.Entity library was set to "Copy Local". Setting that to false and restarting LINQPad removed the error message, allowing me to clean up the tag code somewhat.

Filed under: Entity Framework, Fixes, LINQ, Tools

Implementing DataAnnotations

Posted 26 March 2010, 00:29 | by | | Perma-link

Update: As of Entity Framework 4.1, the MinLengthAttribute is no longer needed as it's now part of the framework, but the length is the second parameter in the string format argument list, with the field name in position {0}. 06/06/2011

As I hinted at in my post "Upgrading to ASP.NET MVC 2.0", one of the main remaining changes I wanted to make after upgrading to version 2.0 was to move to using the DataAnnotations that gained first class support.

I've now done this, which meant that I've removed all the code based on the Nerd Dinner validation patterns, and switched to using the Validation Attributes classes.

I started with the basic RequiredAttribute to ensure that all required fields had a value, but then wanted to do a bit more - on my Contact form for example wanted to ensure that the name supplied was at least 3 characters long, that the email address used my rather more rigorous regex than ScottGu's, and that the message was at least 10 characters long.

A simple answer was to use the RegularExpressionAttribute, with settings such as @".{3,}" for the minimum length, and the lengthy pattern for emails, however I wasn't happy with this for a number of reasons:

  1. I wanted to use the "minimum length" validator on multiple properties, with different minimum lengths but very similar error messages including details of the length.
  2. Because my email pattern is compiled using string.Format to make it more manageable, I can't use it in a attribute as it needs to be a "compile time constant".

I also wanted something similar to the IntegerValidator in the Configuration namespace that allows me to specify just a MinValue, rather than the Range Validator, where I needed to supply an arbitrary high value to meet my needs.

As I'm not yet using the .NET 4 framework, I don't have access to the CustomValidatorAttribute, that's no bad thing in my mind, as I'm not a big fan of the "Magic String" form of configuration that I would need to use.

To that end I've created three validators:

public class IsEmailAttribute : RegularExpressionAttribute
{}
public class MinValueAttribute : ValidationAttribute
{}
public class MinLengthAttribute : ValidationAttribute
{}

IsEmailAttribute just supplies the email pattern (built in a read only property) to the base constructor, while MinValueAttribute and MinLengthAttribute both implement the IsValid method, and override the FormatErrorMessage to enable me to include the set value of the validator.

The full code for the MinLengthAttribute is:

[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)]
public class MinLengthAttribute : ValidationAttribute
{
  public int MinLength { get; set; }

  public MinLengthAttribute(int minLength) {
    MinLength = minLength;
  }

  public override bool IsValid(object value) {
    bool isValid = false;

    var stringValue = value as string;

    if (null != stringValue
        && MinLength <= stringValue.Length) {
      isValid = true;
    }

    return isValid;
  }

  public override string FormatErrorMessage(string name){
    return string.Format(ErrorMessageString, MinLength, name);
  }
}

The one big problem I've had with doing it this way is that the automation of the client-side validation doesn't work, but as it doesn't plug into the Validation Summary area, this is no great loss, as I'd have to redesign my forms more than I'd like to implement that.

The other major change I had to make to my code was to move to more of a ViewModel pattern - on some of my admin screens I was taking a FormsCollection rather than an explicit model which allowed me to have a photo form with text boxes for Caption, Order and Tags (which holds a comma seperated list of tags), but this doesn't map nicely to the Photo model, where Tags is a collection of Tag models. Writing a ViewModel for editing photos, all the annotations wired up nicely, and gave me much better control of what was being sent to the view.

One thing that was needed uploading photos that I did need to do was handle the case of a user not including an image - thankfully, the previous code set me in good stead:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult UploadPhoto(int id,
                                EditPhoto editPhoto,
                HttpPostedFileBase ImageData) {
  ViewData["Title"] = "Create Photo";
  ViewData["Message"] = "Upload a new photo";

  if (ModelState.IsValid
      && null != ImageData
      && ImageData.ContentLength != 0) {
    // Persist to database
    // Return appropriate view based on Request.IsAjaxRequest
  }

  if (null == ImageData || ImageData.ContentLength != 0) {
    // ImageData is missing
    ModelState.AddModelError("ImageData", "You must supply a photo.");
  }

  // Return appropriate view based on Request.IsAjaxRequest
}

So we can still add additional errors to the ModelState property, allowing us to highlight additional fields that aren't part of the ViewModel.

Where I haven't created a ViewModel, I've used the MetadataTypeAttribute on the partial classes created for the Entity Framework classes:

[MetadataType(typeof(AlbumMetadata))]
public partial class Album {
  public string Description {
    get {
      return string.Format(
        "Photos from the album \"{0}\" on http://www.doodle.co.uk/Albums.aspx/{1}/{2}",
        Caption, AlbumId, Caption.CreateSlug());
    }
  }
}

public class AlbumMetadata {
  [Required(ErrorMessage = "You must supply a caption that is at least 3 characters long.")]
  [MinLength(3, ErrorMessage = "The caption must be at least {0} characters long.")]
  public string Caption { get; set; }
}

Next time: Implementing FlickrNet.

Filed under: .Net, Entity Framework, Site Updates

Moving from LINQ to SQL to the Entity Framework

Posted 02 February 2010, 00:50 | by | | Perma-link

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 Wink

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:

Showing the relationship between Album, Photo, Tag, and TagsPhoto tables

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?

Entity Framework tables, showing Photos, Albums and Tags

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 Wink

Filed under: Entity Framework, LINQ