由于您不需要 Portfolio 中的任何内容,只需按 PortfolioId 过滤即可直接查询 PortfolioItems。假设您的 DbContext 有一个包含所有投资组合中的所有项目的 DbSet,可能是这样的:
var value = DbContext.PortfolioItems
.Where(i => i.PortfolioId == portfolioId && i.Status == ItemStatusConstants.Subscribed && _activeStatuses.Contains(i.Category.Status))
.Sum(i=>i.Amount);
我相信如果您直接使用适当的 Queryable.Sum 重载,则不需要 DefaultIfEmpty 也不需要 select。
编辑:尝试了两个不同的 LINQ 查询,但没有公开 DbSet。
第一个查询与您的基本相同:
var value2 = dbContext.Portfolios
.Where(p => p.Id == portfolioId)
.SelectMany(p => p.Items)
.Where(i => i.Status == "A" && _activeStatuses.Contains(i.Category.Status))
.Select(i=>i.Amount)
.DefaultIfEmpty()
.Sum();
在 SQL Server 中分析查询(手头没有 MySql)并生成一个丑陋的句子(参数被替换并且引号未转义以进行测试):
SELECT [GroupBy1].[a1] AS [C1]
FROM (SELECT Sum([Join2].[a1_0]) AS [A1]
FROM (SELECT CASE
WHEN ( [Project1].[c1] IS NULL ) THEN Cast(
0 AS DECIMAL(18))
ELSE [Project1].[amount]
END AS [A1_0]
FROM (SELECT 1 AS X) AS [SingleRowTable1]
LEFT OUTER JOIN
(SELECT [Extent1].[amount] AS [Amount],
Cast(1 AS TINYINT) AS [C1]
FROM [dbo].[items] AS [Extent1]
INNER JOIN [dbo].[categories] AS
[Extent2]
ON [Extent1].[categoryid] =
[Extent2].[id]
WHERE ( N'A' = [Extent1].[status] )
AND ( [Extent1].[portfolioid] =
'E2CC0CC2-066F-45C9-9D48-543D92C4C92E' )
AND ( [Extent2].[status] IN ( N'A', N'B', N'C' )
)
AND ( [Extent2].[status] IS NOT NULL )) AS
[Project1]
ON 1 = 1) AS [Join2]) AS [GroupBy1]
如果我们删除“Select”和“DefaultIfEmpty”方法,并将查询重写为:
var value = dbContext.Portfolios
.Where(p => p.Id == portfolioId)
.SelectMany(p => p.Items)
.Where(i => i.Status == "A" && _activeStatuses.Contains(i.Category.Status))
.Sum(i => i.Amount);
生成的句子更加清晰:
SELECT [GroupBy1].[a1] AS [C1]
FROM (SELECT Sum([Extent1].[amount]) AS [A1]
FROM [dbo].[items] AS [Extent1]
INNER JOIN [dbo].[categories] AS [Extent2]
ON [Extent1].[categoryid] = [Extent2].[id]
WHERE ( N'A' = [Extent1].[status] )
AND ( [Extent1].[portfolioid] =
'E2CC0CC2-066F-45C9-9D48-543D92C4C92E' )
AND ( [Extent2].[status] IN ( N'A', N'B', N'C' ) )
AND ( [Extent2].[status] IS NOT NULL )) AS [GroupBy1]
结论:我们不能依赖 LINQ 提供程序来创建优化查询。在思考生成的 SQL 语句之前,必须对 linq 查询进行分析和优化。