"No SQL translation support" after deserializing an IQueryable expression

Question

I am working on creating a JsonConverter for JSON.NET that is able to serialize and deserialize expressions (System.Linq.Expressions). I have reached the last 5% of my work, and I am having problems with the ability to run a LINQ-to-SQL query created from a 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 fairly simple grouping query in LINQ-to-SQL. TestQuerySource is an implementation of System.Data.Linq.DataContext . Bundle , BundleItem , Product - all LINQ-to-SQL objects decorated with TableAttribute and other other matching attributes. Their respective datacontext properties are Table<T> properties as normal. In other words, nothing remarkable is visible here.

However, when I try to run the query after deserializing the expression, 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 that the expression does cannot be translated into SQL by the LINQ-to-SQL query provider. It seems like this has something to do with creating an anonymous type as part of the request, for example, as part of a join statement. This assumption is confirmed 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} 

A problem occurs when access to a non-primitively typed property of an anonymous type is required. In this case, the bi property is available to access the BundleItem ProductID property.

I can’t understand what the difference is: why access to the property in the original expression will work fine, but not in the deserialized expression.

I suppose the problem is with some information about the anonymous type that gets lost during serialization, but I'm not sure where to look for it or even what to look for.

---

Other examples:

It's worth noting that simpler expressions like this one work just fine:

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

Also, work is done on grouping (without a connection):

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

Simple work with connections:

 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(); 

Choosing 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, an anonymous type is created using properties, and in the broken case, it is created using the constructor.

L2S accepts during query conversion that if you assign a specific value to a property, the property will return that value.

L2S does not assume that the ctor abc parameter names initialize the Abc property. The idea here is that ctor can do anything at all, as long as the property simply stores the value.

Remember that anonymous types do not differ from custom DTO classes (literally L2S cannot distinguish them).

In your examples, you either a) do not use anonymous types (it works) b) using only ctor only in the final projection (it works - everything works like a final projection, even calls to arbitrary methods. L2S is cool.) Or c) using ctor in sql - parts of the request (broken). This confirms my theory.

Try the following:

 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 will work, the first will not.

---

(Update from Daniel)

When restoring a deserialized expression, be sure to use the correct Expression.New overload if the properties must be set using 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 is used, the arguments are passed only to the constructor, and not assigned to the properties.