use Expression<Func> and not Func when adding abstraction layers for linq-to-sql or linq-to-entities

I was doing some testing on the staging site of an asp.net web app I’m working on and notice some of the pages taking an inordinately long amount of time to load.  Since it’s easy to run and problems tend to jump out quickly, I ran the SQL Server Profiler.  Indeed, some queries were jumping out at me as they were taking ~16 seconds (!) to run.  Looking at the queries, it was pretty clear why they were taking so long – they lacked any where clause – they were getting all the rows of the table – about 250k rows in that table – medium-sized, but enough that fetching the whole thing from a remote machine takes a little while.

I used IntelliTrace in VS 2010 and just turned on the ADO.NET category to see who the offender was, and was kind of surprised.

So, rewind a couple weeks to me doing some various refactorings.  There were some places I was fetching rows by PK and just used .Single(row => row.PK == someId) like you do.  I was fine with Single and the exceptions it throws in the non-1 cases, but when it was hit by a co-worker during some testing, it wasn’t clear to them what the cause was, so I figured I’d do a little wrapper to make it more clear.

private static T GetIdentity<T>(Table<T> table, Func<T, bool> identitySearch)
    where T : class
{
    var matchingIdentities = table.Where(identitySearch).ToList();
    switch (matchingIdentities.Count)
    {
        case 1:
            return matchingIdentities[0];

        case 0:
            throw new ApplicationException(
                String.Format("Database problem: Failed to find matching row from {0}",
                              table.ToString()));

        default:
            throw new ApplicationException(
                String.Format("Database problem: Found {0} matching rows from {1}",
                              matchingIdentities.Count, table.ToString()));
    }
}

(Yeah, I know, don’t use ApplicationException, yadda yadda yadda)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Then my “fetch by PK” methods just turned into things like:

public SomeItem GetAppraiser(int someItemId)
{
    return GetIdentity(this.SomeItems, row => row.ID == someItemId);
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Since the runtime behavior (specifically, the lambda/func that gets passed in) didn’t change, I didn’t expect to have a runtime behavior in the SQL query generated.  So much so that I didn’t test it to make sure (doh!)

To test this in isolation, we make a console app and:

  • add in a linq-to-sql class for one of the asp.net tables (membership)
  • add in a linq-to-entities class for another of the asp.net tables (users)
  • convert the “non-method” version to .Where(clause).Single()
  • convert the GetIdentity method to just .Where(clause).Single() to make it easier to compare with the “inline” / “non-method” version
  • convert the first param of GetIdentity to IQueryable<T> so it works for both our linq-to-sql and linq-to-entities tests
  • add a “this” in front of the IQueryable<T> first param and move the method to a static class so we can use it as an extension method
  • rename GetIdentity to GetIdentityFunc
  • add a new GetIdentityExpression that only “wraps” the clause method param with Expression<T>

This results in a Program.cs that looks like:

using System;
using System.Linq;
using System.Linq.Expressions;

namespace GetIdentityTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var userIdToFind = new Guid("0c332b31-7c10-42c2-a33f-5f1fac59a291");
            using (var linqToSql = new DataClasses1DataContext())
            {
                var row1 = linqToSql.aspnet_Memberships.Where(row => row.UserId == userIdToFind).Single();
                var row2 = linqToSql.aspnet_Memberships.GetIdentityExpression(row => row.UserId == userIdToFind);
                var row3 = linqToSql.aspnet_Memberships.GetIdentityFunc(row => row.UserId == userIdToFind);
            }
            using (var linqToEntities = new SomeEntities())
            {
                var row1 = linqToEntities.aspnet_Users.Where(row => row.UserId == userIdToFind).Single();
                var row2 = linqToEntities.aspnet_Users.GetIdentityExpression(row => row.UserId == userIdToFind);
                var row3 = linqToEntities.aspnet_Users.GetIdentityFunc(row => row.UserId == userIdToFind);
            }
        }
    }

    public static class Extensions
    {
        public static T GetIdentityExpression<T>(this IQueryable<T> table, Expression<Func<T, bool>> identitySearch)
            where T : class
        {
            return table.Where(identitySearch).Single();
        }

        public static T GetIdentityFunc<T>(this IQueryable<T> table, Func<T, bool> identitySearch)
            where T : class
        {
            return table.Where(identitySearch).Single();
        }
    }
}

With the “inline” .Where(clause).Single() and the GetIdentityExpression versions, we got the expected where clause:

image

But with GetIdentityFunc, we don’t get that where clause – it asks for the entire table and filters on the client side.  Apparently not being an Expression, linq-to-sql can’t “see into” it, so it has to just pass it off.

image

Given how the runtime query generation works, I guess this shouldn’t have surprised me, but it’s quite the little gotcha 🙂

Side note: when you run this test, you’ll only see 5 ADO.NET events, not 6, because the Expression version for linq-to-sql doesn’t do a call, but instead apparently just returns the cached version.  Neat, although a little surprising that Entities didn’t do the same (it repeats the ‘top 2’ query), although I’m guessing (or at least hoping) there’s some setting I could change to have it do so.

image

Advertisements