LINQ To Entities and string.Format

So I encountered something interesting today while doing a small bit of refactoring.  Got some new requirements for a grid that was showing some product data, and in determining how to display said data, I found that one of the columns contained a double which I had to convert to a string for display purposes.  So I went on my merry way updating existing code to include these new values.

Now, before I continue, I should mention that my business objects are coming from the Entity Framework, and thus any LINQ queries I execute against collections of these objects will be using LINQ to Entities, not plain LINQ to Objects/LINQ to SQL.  So here’s a snapshot of the existing code (not the exact same code, names have been changed to protect the innocent and/or my job):

// "filteredItems" is an IQueryable<Product> which is returned from an Entity Framework repository
var vms = filteredItems.Take(100).Select(x => new ViewModel
{
    Id = x.Id,
    Description = x.Description,
    Size = string.Format("{0} L", x.SizeValue)
});

Now, after running this code, I started to get this exception: “LINQ to Entities does not recognize the method ‘System.String Format(System.String, System.Object, System.Object)’ method, and this method cannot be translated into a store expression.”  Odd, I thought, I’ve never had a problem calling string.Format() on anything before.  So off to Google I go, which leads me to StackOverflow (of course, can’t pimp SO enough, great developer resource), and I found out what was going on.  As is stated in a comment on this question:

…the problem is with the ELINQ (linq 2 entities), because it translates your code to SQL, and when it comes to an inner ToString request, it doesn’t know how to translate ‘ToString’ to SQL.

Okay, I get that, makes sense I suppose.  So how do I fix it?  Well, that was almost painfully easy to do:

// "filteredItems" is an IQueryable<Product> which is returned from an Entity Framework repository
var vms = filteredItems.Take(100).ToList().Select(x => new ViewModel
{
    Id = x.Id,
    Description = x.Description,
    Size = string.Format("{0} L", x.SizeValue)
});

In case you missed it, after the “Take(100)” call, I call ToList, which iterates through the IQueryable to create the List<T>, which causes EF to execute the SQL to actually return data. After calling “ToList()”, I essentially have a List<T> of POCO objects that I can do whatever I want to, because EF has already executed its SQL.

So, if you are iterating over an IQueryable that came from Entity Framework, if you need to do anything funky with the data for display purposes, make sure you call “ToList” on your collection before doing your funkiness.

Advertisements

~ by interneth3ro on January 13, 2012.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: