Querying across relations with LINQ using lambdas

Here is an interesting problem, try writing the bellow Linq query using lambda expressions.

var usersWithBigOrders =
  from usr in context.Users
  from ordr in usr.Orders
  where ordr.Total > 10
  select usr;

I ran into this interesting Linq question when writing yesterday's post. To figure out what the above query actually does I used Reflector. The code bellow is what the C# compiler will generate (when reverted back from IL to C#):

context.Users.SelectMany(
Expression.Lambda<Func<User, IEnumerable<Order>>>(
  Expression.Property(CS$0$0000 = Expression.Parameter(typeof(User), "user"),
   (MethodInfo) methodof(User.get_Orders)), new ParameterExpression[] { CS$0$0000 }),
    Expression.Lambda(
      Expression.New((ConstructorInfo) methodof(<>f__AnonymousType0<User, Order>..ctor,
       <>f__AnonymousType0<User, Order>), 
       new Expression[] { CS$0$0000 = Expression.Parameter(typeof(User), "user"), 
       CS$0$0002 = Expression.Parameter(typeof(Order), "order") }, 
       new MethodInfo[] { (MethodInfo) methodof(<>f__AnonymousType0<User, Order>.get_user, 
       <>f__AnonymousType0<User, Order>), 
       (MethodInfo) methodof(<>f__AnonymousType0<User, Order>.get_order, 
       <>f__AnonymousType0<User, Order>) }), 
       new ParameterExpression[] { CS$0$0000, CS$0$0002 }))
.Where(Expression.Lambda(Expression.GreaterThan(
  Expression.Property(Expression.Property(
    CS$0$0000 = Expression.Parameter(typeof(<>f__AnonymousType0<User, Order>),
     "<>h__TransparentIdentifier0"),
      (MethodInfo) methodof(<>f__AnonymousType0<User, Order>.get_order,
       <>f__AnonymousType0<User, Order>)), (MethodInfo) methodof(Order.get_Total)), 
       Expression.Convert(Expression.Constant(10, typeof(int)), typeof(int?))), 
       new ParameterExpression[] { CS$0$0000 }))
        .Select(Expression.Lambda(Expression.Property(
          CS$0$0000 = Expression.Parameter(typeof(<>f__AnonymousType0<User, Order>),
           "<>h__TransparentIdentifier0"), 
           (MethodInfo) methodof(<>f__AnonymousType0<User, Order>.get_user,
            <>f__AnonymousType0<User, Order>)), 
            new ParameterExpression[] { CS$0$0000 })).ToList<User>();

It is quite surprising how much code that the compiler actually generates. The reason for the amount of code above is because the LinqToSql table class implements IQueryable. When the compiler detects this interface instead of generating IL that performs the query it will generate IL that builds up an expression tree that describes the query.

So in order to understand what the above code actually describes I wrote the same Linq query but instead of querying LinqToSql I queried a normal .NET collection. The code that Reflector generates now looks like this:

List<User> users = new List<User>();
if (CS$<>9__CachedAnonymousMethodDelegate6 == null)
{
    CS$<>9__CachedAnonymousMethodDelegate6 = delegate (User usr) {
        return usr.Orders;
    };
}
if (CS$<>9__CachedAnonymousMethodDelegate7 == null)
{
    CS$<>9__CachedAnonymousMethodDelegate7 = delegate (User usr, Order ordr) {
        return new { usr = usr, ordr = ordr };
    };
}
if (CS$<>9__CachedAnonymousMethodDelegate8 == null)
{
    CS$<>9__CachedAnonymousMethodDelegate8 = delegate (<>f__AnonymousType0<User, Order> <>h__TransparentIdentifier0) {
        return <>h__TransparentIdentifier0.ordr.Total > 10;
    };
}
if (CS$<>9__CachedAnonymousMethodDelegate9 == null)
{
    CS$<>9__CachedAnonymousMethodDelegate9 = delegate (<>f__AnonymousType0<User, Order> <>h__TransparentIdentifier0) {
        return <>h__TransparentIdentifier0.usr;
    };
}
IEnumerable<User> usersWithBigOrders = users
  .SelectMany(CS$<>9__CachedAnonymousMethodDelegate6, CS$<>9__CachedAnonymousMethodDelegate7)
  .Where(CS$<>9__CachedAnonymousMethodDelegate8)
  .Select(CS$<>9__CachedAnonymousMethodDelegate9);

This is a lot more understandable, but still it took a while to figure out exactly what the above code was doing. Here are both queries, one using Linq syntax the other using the lambda syntax, they are equivalent.

var usersWithBigOrders =
  from usr in context.Users
  from ordr in usr.Orders
  where ordr.Total > 10
  select usr;

var usersWithBigOrders = context.Users
  .SelectMany(user => user.Orders,(user, order) => new {User = user, Order = order})
  .Where(anonType => anonType.Order.Total > 10)
  .Select(anonType => anonType.User);