Entity Framework: Never call GroupBy().ToDictionary()

Entity Framework generally works pretty well, but every once in a while it’ll throw something strange at you. Here’s one I experienced a while ago that caused an unexpected performance hit.

ToDictionary And GroupBy Don’t Mix

Let’s say I have a table containing a bunch of products, and I want to find out how many products of each size there are. This is trivial in SQL – it’s a group by, selecting the count and the name. Now let’s say I want to stick the results into a Dictionary<string, int> for further processing. The code might look something like this:

 var productSizeCounts = db.Products.GroupBy(p => p.Size)
                         .ToDictionary(p => p.Key, p => p.Count());

Not too bad, right? Here’s the generated SQL:

SELECT 
 [Project2].[C1] AS [C1], 
 [Project2].[Size] AS [Size], 
 [Project2].[C2] AS [C2], 
 [Project2].[Id] AS [Id], 
 [Project2].[Name] AS [Name], 
 [Project2].[Size1] AS [Size1]
 FROM ( SELECT 
 [Distinct1].[Size] AS [Size], 
 1 AS [C1], 
 [Extent2].[Id] AS [Id], 
 [Extent2].[Name] AS [Name], 
 [Extent2].[Size] AS [Size1], 
 CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
 FROM (SELECT DISTINCT 
 [Extent1].[Size] AS [Size]
 FROM [dbo].[Products] AS [Extent1] ) AS [Distinct1]
 LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON ([Distinct1].[Size] = [Extent2].[Size]) OR (([Distinct1].[Size] IS NULL) AND ([Extent2].[Size] IS NULL))
 ) AS [Project2]
 ORDER BY [Project2].[Size] ASC, [Project2].[C2] ASC;

Huh? What the heck is that? Where’s our GROUP BY? Where’s the COUNT? Calling ToDictionary definitely did NOT do what we expected it to.

Let’s try again. This time we’ll project the values we want onto an anonymous type, and then call ToDictionary on that, instead of on our grouping:

 var productSizeCounts = db.Products.GroupBy(p => p.Size)
                     .Select(g => new { g.Key, Count = g.Count()})
                     .ToDictionary(p => p.Key, p => p.Count);

That gives us the following SQL:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Size], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[Size] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[Products] AS [Extent1]
        GROUP BY [Extent1].[Size]
    )  AS [GroupBy1];

Now that’s more like it!

But why?

ToDictionary() is an extension method on the Enumerable class in System.Linq. It doesn’t know anything about Entity Framework – all it can do is enumerate an IEnumerable. So when you call ToDictionary on your IQueryable<IGrouping<,>> that the GroupBy method returned, it simply materializes the query.

But why does Entity Framework generate such a strange query in the first place?

If you call GroupBy(…) and then enumerate the GroupBy(), Entity Framework has no way of knowing what you are going to do with the results. For all it knows, you are going to end up extracting the entities themselves from the GroupBy.

// I'm so sneaky... I'm gonna trick EF...or am i?
var productSizeCounts = db.Products.GroupBy(p => p.Size).ToList();
var firstProductWithEachSize = productSizeCounts.
                                  Select(p => p.FirstOrDefault());

If it had tried to be smart, and turned the query into the GROUP BY you were expecting, it wouldn’t have the necessary information available to materialize the entities you are asking for!

Explicitly selecting the fields you want lets EF generate the GROUP BY query you would expect, because it knows that’s all the information you’ll need.

ERROR_CANNOT_CONVERT_VIRTUAL_DIRECTORY_TO_APPLICATION

While trying to deploy an instance of BlogEngine.NET to an Azure virtual directory, I ran into a bizarre error:

Web deployment task failed. (The server experienced an issue processing the request. Contact the server administrator for more information. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672# ERROR_CANNOT_CONVERT_VIRTUAL_DIRECTORY_TO_APPLICATION.)

Ok, no problem; I’ll just Google the error and… no results! Go figure. ERROR_CANNOT_CONVERT_VIRTUAL_DIRECTORY_TO_APPLICATION simply doesn’t exist according to Google. Bing was no help either, as it decided to ignore the underscores and search for the individual words, even when the entire string was quoted.

It turned out to be an easy fix, though. In the website’s Configuration page in your Azure portal, head down to the ‘virtual applications and directories’ section. All you have to do is check the ‘Application’ check box for your virtual directory.

Azure virtual directory Application checkbox

 

Save your changes, and redeploy the web site. This time it’ll work!

iOS App Postmortems: Workout Tip Jar

Workout Tip Jar is now available on the app store.

Overview:

A workout tip jar is a jar that you deposit money into every time you work out. Once you reach your goal ($50, $100, etc.), you take the money out and treat yourself to something The Workout Tip Jar app is that jar, in app form!

Workout Tip Jar was a collaboration between myself and my wife. You can read about her thoughts on the app creation process over at fitnessprogrammer.com. Working with someone was a nice change from my other, primarily solo apps.

Development Time:

Probably around 8 hours. Half of that was spent writing the app, and the other half was spend creating launch images, screenshots, and icons….

Review Time (time spent in the iOS app submission queue):

8 days. To be fair, this was over the New Year’s Eve and New Year’s Day.

New technologies I learned and used:

This was my first app that involved audio. Fortunately, playing a small sound is an incredibly easy process.

What went right:

The best thing about Workout Tip Jar is that it was an untapped market (albeit a small one). It’s only been a few days since it was released, but it’s already #1 in a Google search for ‘workout tip jar’.

From a development perspective, it’s hard to pinpoint anything that went surprisingly ‘right’ because the application was so small and straightforward.

What went wrong:

We decided to release Workout Tip Jar as an iPhone-only application. In retrospect, I wish we would’ve taken the extra time to add an iPad version too. It probably wouldn’t have been that big of a deal.

Future plans:

Conceptually, Workout Tip Jar is pretty small in scope, so there’s not too much else to be done here in terms of new features. There are a couple of things that we still have to do though: adding an iPad version is #1 on the list, and improving the graphics and UI design is #2. We might also explore some ‘social’ features like a ‘Share on Facebook’ button when you reach your goal.

If there’s something you’d like to see in Workout Tip Jar, let me know!

iOS 7′s Subtle UITableView View Hierarchy Changes

While updating one of my oldest apps to iOS 7, I noticed that I was getting an exception when trying to grab a UITableViewCell from a UITableView. The code looked something like this:

- (IBAction)OnSomeReallyImportantStepperChanged:(id)sender
{
    UIStepper* stepper = sender;
 
    UITableViewCell* cell = (UITableViewCell*)[[stepper superview] superview];
    UITableView* table = (UITableView *)[cell superview];
    NSIndexPath* pathOfTheCell = [table indexPathForCell:cell]; // ***BOOM***

This code worked in iOS 6 and below, but threw an exception in iOS 7:

[UITableViewCell indexPathForCell:]: unrecognized selector sent to instance

What’s going on here? Well, a quick debugging session made it quite clear. cell is actually a UITableViewCellScrollView, not a UITableViewCell, and table is really a UITableViewCell, so naturally it didn’t have an indexPathForCell method.

The moral of the story is – don’t make too many assumptions about the view hierarchy, because it may change!

Announcing ReminderHero.com – Automatic Email and SMS Reminders

You may have noticed that the volume of posts – and in particular, iOS-related posts – has slowed down over the past month or so. I’ve been busy working on a side project which is now in the public beta phase, and I’d love your feedback!

Reminder Hero is a web app that lets you create intelligent reminders simply by sending an email or a text message. Just describe what you want to be reminded of and when – in plain english. Reminder Hero will email or text you back a reminder when it’s time!

Here are some examples of the kind of things you might ask Reminder Hero to remind you about:

Call Bob in 2h

Pay my credit card bill Tuesday night

Take out the trash every Friday at 8am

So, head on over to http://reminderhero.com, check out the site, and subscribe to the Beta mailing list! Don’t forget to send us your feedback!

“Could not load file or assembly FSharp.Core, Version=4.0.0.0″ In An ASP.NET Application

If you’ve ever tried to use an F# class library from an ASP.NET application, you may find that although things work perfectly when you are running locally, your web app won’t load at all in production.

Could not load file or assembly ‘FSharp.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.

Fortunately, the fix is easy. All you need to do is add a reference to FSharp.Core to your web project, and set its Copy Local property to True. Not familiar with how to do that? Here’s a quick overview.

First, go to the Solution Explorer and right-click on your web project, or on your web project’s References folder, and select Add Reference…

add-reference

In the Reference Manager window, select Assemblies, and the find the FSharp.Core, version 4.3 reference. Check it, and click OK.

add-fsharp-dot-core-reference

 

Finally, in the Solution Explorer, find the FSharp.Core reference in your project’s References folder. Select it, then open the Properties window (or tab, or pane, depending on how you have Visual Studio configured), and set Copy Local to True.

copy-local

That’s it! Deploy your web app to your server, and the error will be gone.