LINQ of the Day

One of the delights of LINQ to SQL is that it's an extension of LINQ to objects, but it doesn't implement everything that LINQ to objects implements. The constraint on LINQ to SQL is that the expression must be translatable into T-SQL. If you write LINQ expressions that don't translate to T-SQL, your code will compile cleanly, but will fail when LINQ is ready to do the translation. (The exception and call stack can best be described as "educational.") Let's look at an example that fixes the problem.

public static List<MultiplierDefinition> GetAllMultipliers()
{
    using (FSP dc = FSP.DC())
    {
        return GetMultipliers(dc)
            .AsEnumerable()
            .OrderBy(m => m, new MultiplierDescComparer<MultiplierDefinition>())
            .ToList();
    }
}

Before tearing into this, let's take a quick look at GetMultpliers, which is a compiled LINQ-to-SQL query. There's nothing strange about it; I write stuff like this routinely. The thing to note is that it returns an IQueryable<MultiplierDefinition>.

private static Func<FSP, IQueryable<MultiplierDefinition>> GetMultipliers = 
    CompiledQuery.Compile(
    (FSP dc) => from m in dc.Pm_Multipliers
                where m.RecordDeleted != true
                select new MultiplierDefinition
                {
                    MultiplierID = m.MultiplierID,
                    Description = m.Description,
                    DiscountPercent = m.DiscountPercent,
                    CreatedByUser = m.CreatedByUser,
                    DateCreated = m.DateCreated,
                    ModifiedByUser = m.ModifiedByUser,
                    DateModified = m.DateModified,
                    RecordDeleted = m.RecordDeleted
                });

So my method GetAllMultipliers() starts by getting a DataContext, and calling a method to get a LINQ-to-SQL expression tree. But the next thing I need to do is sort the data, using a custom sorting algorithm contained in an IComparer<T> object called MultiplierDescComparer<MultiplierDefinition>. There's no way that T-SQL can sort using a C# object! Once the sorting is done, I simply return a generic List<MultiplierDefinition> to my client code.

There are two secrets to making this work. One is that you can add LINQ expressions to a compiled query; they'll be added to the expression tree before the query runs. The second secret, and the important one, is that innocuous little method, .AsEnumerable(). Hey! IQueryable<T> implements IEnumerable<T>, so we're already enumerable. Why do we have to this explicit... Conversion? Reminder? Expositional countersinking? No-op?

It's a conversion, and here's what it means. Everything before .AsEnumerable() will translate to T-SQL and run in the database. Everything after .AsEnumerable() is a LINQ to objects expression, and will run on the data returned from the database. (That's always a core difference between IQueryable<T> and IEnumerable<T>, and that's why you don't want to store a LINQ-to-SQL query as an IEnumerable<T>, unless you want to force any subsequent LINQ expressions to be run in memory.)

None of this forces immediate execution; it's all still deferred execution, right up to the .ToList() method. Evaluating and understanding the entire code and execution path is a bit convoluted, but definitely enlightening. .AsEnumerable() is always useful when you want to use LINQ that doesn't have a counterpart in T-SQL (for example, .SkipWhile() and .TakeWhile()).