"No supported translation to SQL" after deserializing IQueryable expression

Go To StackoverFlow.com

6

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()}
2012-04-05 20:36
by Daniel Schaffer
Just out of curiosity is this based on InterLinq - Phil Degenhardt 2012-04-05 21:04
No. I didn't realize until after I'd already gotten in pretty deep that there were projects doing this for WCF ... but I also didn't want to have to deal with anything WCF related for this - Daniel Schaffer 2012-04-05 21:06
Yeah well the serialisation stuff they've done is pretty nice. It pretty much pops straight out from the WCF stuff. WCF really only enters the picture when you get into the IQueryable implementations. It ported to Silverlight fairly cleanly. I suspect swapping out XML for JSON.NET might also be relatively straight forward. FWIW ; - Phil Degenhardt 2012-04-05 21:13
At this point, I'm really just curious to figure out what the problem is and solve it :D I'll definitely take a look at InterLinq if I can't get it though - Daniel Schaffer 2012-04-05 21:20
Update: I cleaned up the question after determining that the anonymous type constructor arguments aren't the issu - Daniel Schaffer 2012-04-05 22:01


2

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.

2012-04-05 22:43
by usr
The code listed shows both examples using a constructor of sorts, one is simply using named parameters. The op is asking whether or not that naming actually impacts the translation or not. Neither of the translated expressions is using automatic property initialization - Quintin Robinson 2012-04-05 22:46
I don't think this is just naming. It is the syntax for object initializers (expression trees have explicit support for initializers!). Remember, that expression trees do not format back to C# code. They format back to their own syntax - usr 2012-04-05 22:47
@usr: That was my initial thought too, but I have examples that work where the expression shows that same syntax. I just realized that a previous edit had removed where I'd shown the string representations, I'll updated in a moment to put them back in - Daniel Schaffer 2012-04-05 22:52
Ahh I see what you are getting at. In that case it is possible the generation code missed a step. I am curious as well because in that case it could simply be the way the parser interprets the anonymous type and generates the tree code, it might have to have specific logic for the condition instead of being generic for all cases - Quintin Robinson 2012-04-05 22:54
I added my response - usr 2012-04-05 22:58
AH you're totally right. So, by passing the arguments as parameters, that is actually saying that the anonymous type should have those properties. For some reason, my code either isn't serializing those parameters correctly, they aren't be deserialized correctly, or both - Daniel Schaffer 2012-04-05 23:16
Well, I think I made some progress... MemberBindings needed a little special attention to ensure proper serialization, which was actually preventing me from testing your "query1" test. That's done now, but I'm still getting the translation exception, so tomorrow I'll try and see if I can get a closer look at the constructor parameters - Daniel Schaffer 2012-04-06 00:06
@Daniel, it might be worth checking out the InterLinq code for clues. They seem to have quite a bit in there dedicated to handling anonymous types along with comments that they are the hardest part about ser/deser of expression trees - Phil Degenhardt 2012-04-06 00:29
@usr Actually your first example won't run regardless of serialization. Is that what you intended - Daniel Schaffer 2012-04-06 00:29
@David: Yes, that's his point. He's saying your serialization/deserialization step is converting the second expression into the first - Ben Voigt 2012-04-06 01:11
Figured it out! The part of my code that builds the deserialized expressions wasn't using the right overload of Expression.New(), which resulted in it only passing values into the constructor instead of assigning properties. Thanks so much for the help, everyone - Daniel Schaffer 2012-04-06 02:14
@degorolls I haven't looked at the InterLinq code yet, but as it turned out, I didn't need any anonymous type-specific code. The only parts that really needed special attention were the expressions themselves, MethodBinder, and a couple of the collection types used by the expressions. That said, I'm not entirely sure how efficient my solution will be compared to theirs, as it sounds like we probably took very different paths - Daniel Schaffer 2012-04-06 02:29
Also, a correction to my above "AH you're totally right" comment: This isn't specific to anonymous types - using a constructor to assign properties allows the LINQ-to-SQL provider to access properties it wouldn't otherwise have mappings for, which is sort of what @usr said in the answer and I didn't quite make the connection at the time - Daniel Schaffer 2012-04-06 02:38
Ads