I’m working with the following LINQ statement:
var result = await _context.Responses
.Where(r => r.Query != null)
.GroupBy(r => r.Option)
.Select(group => new
{
group.Key,
Total = group.Sum(r => r.Query.Value)
})
.ToListAsync();
Here are my data models:
public class Response
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
public Option? Option { get; set; } // Values stored are 0, 1, 2
public Guid? QueryId { get; set; }
public virtual Query? Query { get; set; }
}
public class Query
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
public ushort Value { get; set; }
}
The generated SQL for this LINQ query is:
SELECT [r].[Option] AS [Key],
(
SELECT COALESCE(SUM(CAST([q1].[Value] AS int)), 0)
FROM [Responses] AS [r0]
LEFT JOIN [Queries] AS [q0]
ON [r0].[QueryId] = [q0].[Id]
LEFT JOIN [Queries] AS [q1]
ON [r0].[QueryId] = [q1].[Id]
WHERE ([q0].[Id] IS NOT NULL)
AND (
[r].[Option] = [r0].[Option]
OR (
([r].[Option] IS NULL)
AND ([r0].[Option] IS NULL)
)
)
) AS [Total]
FROM [Responses] AS [r]
LEFT JOIN [Queries] AS [q]
ON [r].[QueryId] = [q].[Id]
WHERE [q].[Id] IS NOT NULL
GROUP BY [r].[Option]
I’m puzzled as to why a subquery is being used for the SUM operation. I anticipated a simpler SQL output, something like:
SELECT [r].[Option] AS [Key],
COALESCE(SUM(CAST([q].[Value] AS int)), 0) AS [Total]
FROM [Responses] AS [r]
LEFT JOIN [Queries] AS [q]
ON [r].[QueryId] = [q].[Id]
WHERE [q].[Id] IS NOT NULL
GROUP BY [r].[Option]
Is there a method to eliminate the subquery from the resulting SQL?