Linq 查询在 SQL 中生成不同的结果

Question

我在 LINQ 中编写了这个左联接，我只从表中选择了PersonIdSHPerson但在 SQL Server 探查器中，我也从表中获得了PersonIdSHPFA。

var spfQuery = from n in shHPFARepository.GetAll()
.AsNoTracking()
.Where(t => t.ShareCount > 0)
group n by new { n.PersonId } into nGroup
select new 
{ 
nGroup.Key.PersonId, 
TotalhareCount = nGroup.Sum(t => t.ShareCount) 
};                                                                                       
var query = (from sp in SHPersonRepository.GetAll().AsNoTracking()
join spf in spfQuery on sp.Id equals spf.PersonId 
select new SHPOutputDto
{
PersonId = sp.PersonId,
ShareCount = spf.TotalShareCount,
IsShareHolder=true
});
var resultDto = await query.ToListAsync();

我不要SHPFA.PersonId([t0].[PersonId](在查询中被选中。

SELECT [t0].[PersonId], [t0].[TotalShareCount], [e].[PersonId]
FROM [SHolders].[SHPerson] AS [e]
INNER JOIN 
(SELECT [t].[SHPersonId], SUM([t].[ShareCount]) AS [TotalShareCount]
FROM [SHolders].[SHPFA] AS [t]
WHERE [t].[ShareCount] > 0
GROUP BY [t].[PersonId]) AS [t0] ON [e].[Id] = [t0].[PersonId]

Answer 1

您想从子选择内部联接中删除它还是想从最终选择结果中删除它？因为似乎您正在使用两个相似的列[PersonId]和[SHPersonId]

如果这只是一个拼写错误，那么您应该在第一个查询中使用[SHPersonId]，如下所示：

var spfQuery = from n in shHPFARepository.GetAll()
.AsNoTracking()
.Where(t => t.ShareCount > 0)
group n by n.SHPersonId into nGroup
select new 
{ 
SHPersonId = nGroup.Key, 
TotalhareCount = nGroup.Sum(t => t.ShareCount) 
};               

PS：不要忘记通过将PersonId重命名为SHPersonId来修改第二个查询

Answer 2

作为替代方法，您始终可以使用原始 sql 查询：

using (var ctx = new YourContext())
{
var spfQuery = ctx.Database.SqlQuery<YourModel>("SELECT
[t0].[TotalShareCount]
, [e].[PersonId]
FROM [SHolders].[SHPerson] AS [e]
INNER JOIN 
(SELECT [t].[SHPersonId], SUM([t].[ShareCount]) AS [TotalShareCount]
FROM [SHolders].[SHPFA] AS [t]
WHERE [t].[ShareCount] > 0
GROUP BY [t].[PersonId]) AS [t0] ON [e].[Id] = [t0].[PersonId]"
).ToList();
}