Relinq-SqlBackened Generating Invalid SQL

[John Bacon]

I've run into an issue w/ the Relinq-SqlBackened and I'm trying to figure out if this is a known limitation/issue within Relinq or if perhaps there is a work around. I'm a bit stuck and would appreciate any input if at all possible.

I've setup a test project w/ two DevExpress Pivot grids. These grids have the ability to work in something called LinqServerMode which essentially generates LINQ statements on the fly depending on user input. The first user grid is bound to a LinqToSql datasource and the other uses the Relinq-SqlBackened. Both are pointing to a Northwinds database, specifically the Orders table.

If I attempt to do get a summation on a numeric field within orders (in this case Freight) by dragging the Freight column into the data item section of the pivot grid I'm getting invalid SQL from Relinq-SqlBackeend. LinqToSql doesn't seem to have any trouble with it. Unfortunately DevEx doesn't give me a good way to see the LINQ that is actually generated behind the scenes. 

I realize this isn't much to work with but any insight would be much appreciated. 

One more thing worth mentioning:

Initially I was getting a MemeberInitExpression error until I used this patch from Guillaume Lecomte. This allowed me to use the Pivot Grid for some types of queries. 

https://groups.google.com/forum/#!msg/re-motion-dev/dMIw3XTQWi0/Q5jP1B7jHKUJ

I'm thinking this may just be another case of this issue: https://www.re-motion.org/jira/browse/RMLNQSQL-43

Re-Linq Generate QueryModel & Command Text

queryModel

{from IGrouping`2 <generated>_1 in {value(Remotion.Linq.LinqToSqlAdapter.RelinqQueryable`1[Remotion.Linq.IntegrationTests.Common.TestDomain.Northwind.Order]) => GroupBy(0, [<generated>_3]) => Take(1)} select new MS362794_C1`1() {P0 = {from Order elem in [<generated>_1] select [elem].Freight => Sum()}}}

    _mainFromClause: {from IGrouping`2 <generated>_1 in {value(Remotion.Linq.LinqToSqlAdapter.RelinqQueryable`1[Remotion.Linq.IntegrationTests.Common.TestDomain.Northwind.Order]) => GroupBy(0, [<generated>_3]) => Take(1)}}

    _selectClause: {select new MS362794_C1`1() {P0 = {from Order elem in [<generated>_1] select [elem].Freight => Sum()}}}

    _uniqueIdentifierGenerator: {Remotion.Linq.UniqueIdentifierGenerator}

    BodyClauses: Count = 0

    MainFromClause: {from IGrouping`2 <generated>_1 in {value(Remotion.Linq.LinqToSqlAdapter.RelinqQueryable`1[Remotion.Linq.IntegrationTests.Common.TestDomain.Northwind.Order]) => GroupBy(0, [<generated>_3]) => Take(1)}}

    ResultOperators: Count = 0

    ResultTypeOverride: {Name = "IQueryable`1" FullName = "System.Linq.IQueryable`1[[DevExpress.Data.Linq.Helpers.MS362794_C1`1[[System.Nullable`1[[System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], DevExpress.Data.v14.2, Version=14.2.6.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a]]"}

    SelectClause: {select new MS362794_C1`1() {P0 = {from Order elem in [<generated>_1] select [elem].Freight => Sum()}}}

CommandText : SELECT [q2].[a0] AS [P0] FROM (SELECT TOP (1) [q1].[key] AS [key], SUM([q1].[element_Freight]) AS [a0] FROM (SELECT [t0].[value] AS [key] FROM [Orders] AS [t3] CROSS APPLY (SELECT @1 AS [value]) AS [t0] GROUP BY [t0].[value]) AS [q1]) AS [q2]

LinqToSql Generated SQL Query

SELECT TOP (1) [t2].[value] AS [P0]

FROM (

    SELECT SUM([t1].[Freight]) AS [value]

    FROM (

        SELECT @p0 AS [value], [t0].[Freight]

        FROM [dbo].[Orders] AS [t0]

        ) AS [t1]

    GROUP BY [t1].[value]

    ) AS [t2]

[Michael Ketting]

Hello John!

I've reverse engineered the used LINQ statement (or an equivalent statement that results in the same LinqToSql SQL):

DB.Orders.GroupBy (o => 0).Select (o => o.Sum (i => i.Freight)).Take(1);

With the LinqToSqlAdapter I get the following SQL:
SELECT TOP (1) [q1].[a0] AS [value]
FROM (
    SELECT [t0].[value] AS [key], SUM([t2].[Freight]) AS [a0]
    FROM [Orders] AS [t2]
    CROSS APPLY (SELECT 0 AS [value]) AS [t0]

    GROUP BY [t0].[value]
) AS [q1]

It gives the same result as the LinqToSql version and is markedly different from the result generated by the patched version you used. So, I think the problem lies with the patch you used. In particular, the way 'Freight' is being printed as 'element_Freight' sounds like there's a problem with the references in the expression tree.

As for MemberInitExpressions: Yeah, those are the biggest hurdle when it comes to various client-libraries. Contributions are still open on that and since we're now on GitHub, much easier to maintain and review than previously with SVN :)

Best regards, Michael