"No supported translation to SQL" after deserializing IQueryable expression

Question

I'm working on creating a JsonConverter for JSON.NET that is capable of serializing and deserializing expressions (System.Linq.Expressions). I'm down to the last 5% or so of the work, and I'm having problems being able to run a LINQ-to-SQL query generated from the deserialized expression.

Here is the expression:

Expression<Func<TestQuerySource, Bundle>> expression = db => (
    from b in db.Bundles
    join bi in db.BundleItems on b.ID equals bi.BundleID
    join p in db.Products on bi.ProductID equals p.ID
    group p by b).First().Key;

This is a pretty straightforward grouping query in LINQ-to-SQL. TestQuerySource is an implementation of System.Data.Linq.DataContext. Bundle, BundleItem, Product, are all LINQ-to-SQL entities decorated with TableAttribute and other other mapping attributes. Their corresponding datacontext properties are all Table<T> properties as normal. In other words, nothing spectacularly notable here.

However, when I attempt to run the query after the expression has been deserialized, I get the following error:

System.Reflection.TargetInvocationException:
Exception has been thrown by the target of an invocation. --->
    System.NotSupportedException: The member '<>f__AnonymousType0`2[Bundle,BundleItem].bi' has no supported translation to SQL.

I understand that this means that something the expression is doing cannot be translated to SQL by the LINQ-to-SQL query provider. It appears that it has something to do with creating an anonymous type as part of the query, like as part of the join statement. This assumption is supported by comparing the string representation of the original and deserialized expressions:

Original (working):

{db => db.Bundles
.Join(db.BundleItems,
    b => b.ID,
    bi => bi.BundleID,
    (b, bi) => new <>f__AnonymousType0`2(b = b, bi = bi))
.Join(db.Products,
    <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.bi.ProductID,
    p => p.ID,
    (<>h__TransparentIdentifier0, p) =>
        new <>f__AnonymousType1`2(<>h__TransparentIdentifier0 = <>h__TransparentIdentifier0, p = p))
.GroupBy(<>h__TransparentIdentifier1 =>
    <>h__TransparentIdentifier1.<>h__TransparentIdentifier0.b,
    <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.p)
.First().Key}

Deserialized (broken):

{db => db.Bundles
.Join(db.BundleItems,
    b => b.ID,
    bi => bi.BundleID,
    (b, bi) => new <>f__AnonymousType0`2(b, bi))
.Join(db.Products,
    <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.bi.ProductID,
    p => p.ID,
    (<>h__TransparentIdentifier0, p) => new <>f__AnonymousType1`2(<>h__TransparentIdentifier0, p))
.GroupBy(<>h__TransparentIdentifier1 =>
    <>h__TransparentIdentifier1.<>h__TransparentIdentifier0.b,
    <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.p)
.First().Key}

The problem seems to occur when a non-primitively typed property of an anonymous type needs be accessed. In this case the bi property is being accessed in order to get to BundleItem's ProductID property.

What I can't figure out is what the difference would be - why accessing the property in the original expression would work fine, but not in the deserialized expression.

I'm guessing the issue has something to do with some sort of information about the anonymous type getting lost during serialization, but I'm not sure where to look to find it, or even what to be looking for.

---

Other Examples:

It is worth noting that simpler expressions like this one work fine:

Expression<Func<TestQuerySource, Category>> expression = db => db.Categories.First();

Even doing grouping (without joining) works as well:

Expression<Func<TestQuerySource, Int32>> expression = db => db.Categories.GroupBy(c => c.ID).First().Key;

Simple joins work:

Expression<Func<TestQuerySource, Product>> expression = db => (
    from bi in db.BundleItems
    join p in db.Products on bi.ProductID equals p.ID
    select p).First();

Selecting an anonymous type works:

Expression<Func<TestQuerySource, dynamic>> expression = db => (
    from bi in db.BundleItems
    join p in db.Products on bi.ProductID equals p.ID
    select new { a = bi, b = p }).First();

Here are the string representations of the last example:

Original:

{db => db.BundleItems
.Join(db.Products,
    bi => bi.ProductID,
    p => p.ID,
    (bi, p) => new <>f__AnonymousType0`2(a = bi, b = p))
.First()}

Deserialized:

{db => db.BundleItems
.Join(db.Products,
    bi => bi.ProductID,
    p => p.ID,
   (bi, p) => new <>f__AnonymousType0`2(bi, p))
.First()}

Answer 1

I think the difference is that in the working example the anonymous type is constructed using properties and in the broken case it is instantiated using a constructor.

L2S assumes during query translation that if you assign to a property a certain value, the property will return just that value.

L2S does not assume that a ctor parameter names abc will initialize a property called Abc. The thinking here is that a ctor could do anything at all while a property will just store a value.

Remember that anonymous types are no different than custom DTO classes (literally! L2S cannot tell them apart).

In your examples, you are either a) not using anonymous types (works) b) using a ctor in the final projection only (works - everything works as a final projection, even arbitrary method calls. L2S is awesome.) or c) using a ctor in the sql part of the query (broken). This confirms my theory.

Try this:

var query1 = someTable.Select(x => new CustomDTO(x.SomeString)).Where(x => x.SomeString != null).ToList();
var query2 = someTable.Select(x => new CustomDTO() { SomeString = x.SomeString }).Where(x => x.SomeString != null).ToList();

The second one will work, the first one won't.

---

(Update from Daniel)

When reconstructing the deserialized expression, make sure to use the correct overload of Expression.New if properties need to be set via the constructor. The correct overloads to use are Expression.New(ConstructorInfo, IEnumerable<Expression>, IEnumerable<MemberInfo>) or Expression.New(ConstructorInfo, IEnumerable<Expression>, MemberInfo[]). If one of the other overloads are used, the arguments will only be passed into the constructor, instead of being assigned to properties.