{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/5784","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/5784/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/5784/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/5784/events","html_url":"https://github.com/aspnet/EntityFramework/issues/5784","id":160551684,"number":5784,"title":"Query: Sum over subquery produces incorrect SQL","user":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/type-investigation","name":"type-investigation","color":"0052cc"}],"state":"open","locked":false,"assignee":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"assignees":[{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/19","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.1.0","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/19/labels","id":1748298,"number":19,"title":"1.1.0","description":"","creator":{"login":"rowanmiller","id":4741851,"avatar_url":"https://avatars.githubusercontent.com/u/4741851?v=3","gravatar_id":"","url":"https://api.github.com/users/rowanmiller","html_url":"https://github.com/rowanmiller","followers_url":"https://api.github.com/users/rowanmiller/followers","following_url":"https://api.github.com/users/rowanmiller/following{/other_user}","gists_url":"https://api.github.com/users/rowanmiller/gists{/gist_id}","starred_url":"https://api.github.com/users/rowanmiller/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rowanmiller/subscriptions","organizations_url":"https://api.github.com/users/rowanmiller/orgs","repos_url":"https://api.github.com/users/rowanmiller/repos","events_url":"https://api.github.com/users/rowanmiller/events{/privacy}","received_events_url":"https://api.github.com/users/rowanmiller/received_events","type":"User","site_admin":false},"open_issues":107,"closed_issues":122,"state":"open","created_at":"2016-05-04T18:23:47Z","updated_at":"2016-08-11T22:37:56Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2016-06-16T00:11:59Z","updated_at":"2016-08-11T23:07:01Z","closed_at":null,"body":"````\r\n//Model\r\npublic class MyContext : DbContext\r\n{\r\n    public DbSet<Inventory> Inventory { get; set; }\r\n    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\r\n    {\r\n        optionsBuilder.UseSqlServer(@\"Server=(localdb)\\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;\");\r\n    }\r\n\r\n    protected override void OnModelCreating(ModelBuilder modelBuilder)\r\n    {\r\n\r\n    }\r\n}\r\n\r\npublic class Purchase\r\n{\r\n    public Purchase()\r\n    {\r\n        Inventory = new List<Inventory>();\r\n    }\r\n\r\n    public int Id { get; set; }\r\n    public int Count { get; set; }\r\n    public IList<Inventory> Inventory { get; }\r\n}\r\n\r\npublic class Inventory\r\n{\r\n    public int Id { get; set; }\r\n    public int PurchaseId { get; set; }\r\n    public int? SaledCount { get; set; }\r\n    public Purchase Purchase { get; set; }\r\n}\r\n```\r\nRepro code:\r\n```\r\nusing (var ctx = new MyContext())\r\n{\r\n    var query = (from p in ctx.Set<Purchase>()\r\n                    let count = p.Count - (int)(p.Inventory.Select(i => i.SaledCount).Sum() ?? 0)\r\n                    where (count > 0)\r\n                    select count).Sum();\r\n}\r\n```\r\nException: `System.Data.SqlClient.SqlException : Cannot perform an aggregate function on an expression containing an aggregate or a subquery.`\r\nStackTrace:\r\n```\r\nCannot perform an aggregate function on an expression containing an aggregate or a subquery.\r\n\tat System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n\tat System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n\tat System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n\tat System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n\tat System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n\tat System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n\tat System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n\tat System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n\tat System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n\tat System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n\tat System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n\tat System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n\tat System.Data.Common.DbCommand.ExecuteReader()\r\n\tStorage\\Internal\\RelationalCommand.cs(275,0): at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)\r\n\tStorage\\Internal\\RelationalCommand.cs(140,0): at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)\r\n\tQuery\\Internal\\QueryingEnumerable.cs(81,0): at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()\r\n\tQuery\\QueryMethodProvider.cs(115,0): at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable`1 valueBuffers)\r\n\tat lambda_method(Closure , QueryContext )\r\n\tQuery\\Internal\\QueryCompiler.cs(200,0): at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc)\r\n\tQuery\\Internal\\QueryCompiler.cs(98,0): at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)\r\n\tQuery\\Internal\\EntityQueryProvider.cs(57,0): at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)\r\n\tat System.Linq.Queryable.Sum(IQueryable`1 source)\r\n\tQuerySqlServerTest.cs(5457,0): at Microsoft.EntityFrameworkCore.SqlServer.FunctionalTests.QuerySqlServerTest.Test()\r\n```\r\nGenerated SQL:\r\n```\r\nSELECT SUM([p].[Count] - COALESCE((\r\n    SELECT SUM([i0].[SaledCount])\r\n    FROM [Inventory] AS [i0]\r\n    WHERE [p].[Id] = [i0].[PurchaseId]\r\n), 0))\r\nFROM [Purchase] AS [p]\r\nWHERE ([p].[Count] - COALESCE((\r\n    SELECT SUM([i].[SaledCount])\r\n    FROM [Inventory] AS [i]\r\n    WHERE [p].[Id] = [i].[PurchaseId]\r\n), 0)) > 0\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/comments/239320137","html_url":"https://github.com/aspnet/EntityFramework/issues/5784#issuecomment-239320137","issue_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/5784","id":239320137,"user":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"created_at":"2016-08-11T23:07:01Z","updated_at":"2016-08-11T23:07:01Z","body":"Query generated with EF6\r\n```\r\nSELECT \r\n    [GroupBy3].[A1] AS [C1]\r\n    FROM ( SELECT \r\n        SUM([Filter3].[A1]) AS [A1]\r\n        FROM ( SELECT \r\n            [Project2].[Count] - (CASE WHEN ([Project2].[C1] IS NULL) THEN 0 ELSE [Project2].[C2] END) AS [A1]\r\n            FROM ( SELECT \r\n                [Project1].[Count] AS [Count], \r\n                [Project1].[C1] AS [C1], \r\n                (SELECT \r\n                    SUM([Extent3].[SaledCount]) AS [A1]\r\n                    FROM [dbo].[Inventories] AS [Extent3]\r\n                    WHERE [Project1].[Id] = [Extent3].[PurchaseId]) AS [C2]\r\n                FROM ( SELECT \r\n                    [Extent1].[Id] AS [Id], \r\n                    [Extent1].[Count] AS [Count], \r\n                    (SELECT \r\n                        SUM([Extent2].[SaledCount]) AS [A1]\r\n                        FROM [dbo].[Inventories] AS [Extent2]\r\n                        WHERE [Extent1].[Id] = [Extent2].[PurchaseId]) AS [C1]\r\n                    FROM [dbo].[Purchases] AS [Extent1]\r\n                )  AS [Project1]\r\n            )  AS [Project2]\r\n            WHERE ([Project2].[Count] - (CASE WHEN ([Project2].[C1] IS NULL) THEN 0 ELSE [Project2].[C2] END)) > 0\r\n        )  AS [Filter3]\r\n    )  AS [GroupBy3]\r\n```"}}
{"actor":{"display_login":"smitpatel"}}