Question

Model from http://support.x-tensive.com/question/5521/linq-select-translation

Sum function generate subquery

Place contains three elements { { Id = 1, Name = SomePlace }, { Id = 2, Name = OtherPlace }, { Id = 3, Name = OtherPlace2 } }

    [TestMethod]
    public void TestSum()
    {
        var query = from e in session.Query.All<Place>()
                    from i in session.Query.All<Place>()
                    select new { item = e, eq = e.Id == i.Id ? 1 : 2 };

        var keys = query.GroupBy(i => new { i.item }).Select(i => new {i.Key, sum = i.Sum(e => e.eq)});

        keys.ToList();
    }

SQL query:

SELECT  [a].[Id] ,
        [a].[TypeId] ,
        [b].[Name] AS [#b.Name] ,
        ( SELECT    CAST(SUM([c].[c01umn]) AS INTEGER)
          FROM      ( SELECT    [d].[Id] ,
                                [d].[TypeId] ,
                                [d].[Name] ,
                                [d].[#a.Id] ,
                                [d].[#a.TypeId] ,
                                [d].[#a.Name] ,
                                ( CASE WHEN ( [d].[Id] = [d].[#a.Id] ) THEN 1
                                       ELSE 2
                                  END ) AS [c01umn]
                      FROM      ( SELECT    [e].[Id] ,
                                            102 AS [TypeId] ,
                                            [e].[Name] ,
                                            [f].[Id] AS [#a.Id] ,
                                            102 AS [#a.TypeId] ,
                                            [f].[Name] AS [#a.Name]
                                  FROM      [dbo].[Place] [e]
                                            INNER JOIN [dbo].[Place] [f] ON ( CAST(1 AS BIT) <> 0 )
                                ) [d]
                      WHERE     ( ( [d].[Id] = [a].[Id] )
                                  AND ( [d].[TypeId] = [a].[TypeId] )
                                )
                    ) [c]
        ) AS [c01umn1]
FROM    ( SELECT    [g].[Id] AS [Id] ,
                    [g].[TypeId] AS [TypeId]
          FROM      ( SELECT    [h].[Id] ,
                                102 AS [TypeId] ,
                                [h].[Name] ,
                                [i].[Id] AS [#a.Id] ,
                                102 AS [#a.TypeId] ,
                                [i].[Name] AS [#a.Name]
                      FROM      [dbo].[Place] [h]
                                INNER JOIN [dbo].[Place] [i] ON ( CAST(1 AS BIT) <> 0 )
                    ) [g]
          GROUP BY  [g].[Id] ,
                    [g].[TypeId]
        ) [a]
        INNER JOIN [dbo].[Place] [b] ON ( [a].[Id] = [b].[Id] );

Expected SQL query:

SELECT  [a].[Id] ,
        [a].[TypeId] ,
        [b].[Name] AS [#b.Name] ,
        tmp
FROM    ( SELECT    [g].[Id] AS [Id] ,
                    [g].[TypeId] AS [TypeId] ,
                    CAST(SUM([g].[c01umn]) AS INTEGER) AS tmp
          FROM      ( SELECT    [h].[Id] ,
                                102 AS [TypeId] ,
                                [h].[Name] ,
                                [i].[Id] AS [#a.Id] ,
                                102 AS [#a.TypeId] ,
                                [i].[Name] AS [#a.Name] ,
                                ( CASE WHEN ( [h].[Id] = [i].[Id] ) THEN 1
                                       ELSE 2
                                  END ) AS [c01umn]
                      FROM      [dbo].[Place] [h]
                                INNER JOIN [dbo].[Place] [i] ON ( CAST(1 AS BIT) <> 0 )
                    ) [g]
          GROUP BY  [g].[Id] ,
                    [g].[TypeId]
        ) [a]
        INNER JOIN [dbo].[Place] [b] ON ( [a].[Id] = [b].[Id] );

Accepted Answer

Hello Anton,

I think current translation is correct. In most general case GroupBy + Sum are translated to a GROUP BY + subquery in SQL. However if both subquery and GROUP BY are performed over the same query expression the translated query could be optimized to GROUP BY + aggregate function.

The problem is that you're grouping by entity. This means DataObjects.Net will drop all but key columns to translate GroupBy and then re-join the rest of the columns. This transformation prevents optimizer from detecting situation where simple GROUP BY + aggregate function could be used.

Fortunately there is a simple way to avoid such behaviour, change your query to use grouping by key:

var keys = query.GroupBy(i => new { i.item.Key }).Select(i => new {i, sum = i.Sum(e => e.eq)});

Subscription:

Related questions