时间戳

首页 > 解决方案 > Linq 查询在 SQL 中生成不同的结果

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

问题描述

我在 LINQ 中编写了这个左连接，我只PersonId从SHPerson表中选择，但在 SQL Server 分析器中，我也是PersonId从SHPFA表中获取的。

 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]

标签： c#linqlinq-to-sqlleft-joinsql-server-profiler

解决方案

您是想从子选择内部连接中删除它还是想从最终选择结果中删除它？因为似乎您正在使用两个相似的列[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

推荐阅读