如何将下面的sql group by语句转换为Linq

Question

我是 LINQ 新手，正在尝试将下面的 TSQL 转换为 LINQ，有什么方法可以做到这一点吗？感谢您的所有帮助。

这是我试图转换的 SQL 语句

SELECT [t5].[StatusID], [t5].[FriendlyName], sum(1) AS [Count]  
FROM (  
SELECT distinct [t0].DealID, [t0].[StatusID], [t1].[FriendlyName]  
FROM [DR_Deal] AS [t0]  
INNER JOIN [DR_DealStage] AS [t1] ON [t0].[StatusID] = [t1].[DealStageID]  
INNER JOIN [DR_Deal_ApproverInfo] AS [t2] ON [t0].[DealID] = [t2].[DealID]  
INNER JOIN [DR_Approver] AS [t3] ON [t2].[ApproverID] = ([t3].[ApproverID])  
INNER JOIN [DR_Profile] AS [t4] ON [t3].[ProfileID] = ([t4].[ProfileID])  
WHERE (LOWER([t4].[Email]) = @p0) OR (LOWER([t0].[CreatedBy]) = @p1)  
) AS [t5]  
GROUP BY [StatusID], [FriendlyName]  
ORDER BY [t5].[StatusID]

EDITS

是的，上面的sql是使用linqpad生成的，这就是我真正想要转换为linq的 <代码> SELECT COUNT(DISTINCT [t0].DealID) AS 计数，[t0].[StatusID], [t1].[FriendlyName]
来自 [DR_Deal] AS [t0]
INNER JOIN [DR_DealStage] AS [t1] ON [t0].[StatusID] = [t1].[DealStageID]
内部联接 [DR_Deal_ApproverInfo] AS [t2] ON [t0].[DealID] = [t2].[DealID]
INNER JOIN [DR_Approver] AS [t3] ON [t2].[ApproverID] = ([t3].[ApproverID])
INNER JOIN [DR_Profile] AS [t4] ON [t3].[ProfileID] = ([t4].[ProfileID])
WHERE (LOWER([t4].[Email]) = LOWER(@Email)) OR (LOWER([t0].[CreatedBy]) = LOWER(@UserName))
GROUP BY [t0].[StatusID], [t1].[FriendlyName]
排序依据 [t0].[StatusID]

我想我找到了使用 linq 执行 count(distinct) 的解决方案，这是我最初偶然发现的，但转换后的解决方案仍然不起作用，我正在考虑下面的 linq 代码不起作用，因为我正在使用 sql 2000 并且生成的 sql 具有嵌套选择，并且 sql 2000 似乎存在一些限制。我仍然不确定我的结论，可能是 linq blelow 完全错误或者可能有更好的方法来编写上面的sql。

var query = (from d in DR_Deal  
join s in DR_DealStage  
on d.StatusID equals s.DealStageID  
join da in DR_Deal_ApproverInfo  
on d.DealID equals da.DealID  
join a in DR_Approver  
on da.ApproverID equals a.ApproverID  
join p in DR_Profile  
on a.ProfileID equals p.ProfileID  
where p.Email.ToLower().Equals("[email protected]")   
|| d.CreatedBy.ToLower().Equals("test")  
group d.DealID by new { d.StatusID, s.FriendlyName}  
into grp  
select new  
{  
  StatusID = grp.Key.StatusID,  
  FriendlyName = grp.Key.FriendlyName,  
  Count = grp.Distinct().Count()  
}).OrderBy(x=> x.StatusID);

Answer 1

我认为有很多方法可以做到这一点，但我做了一些类似的事情并将其映射到一个对象。此声明并不完全适合您的问题，但您可以使用以下内容将不同值与聚合（具有、组、计数、总和）值等混合。

             IEnumerable<MyObject> myObj                  
              =  (from d in dbContext.DR_Deal          
             /*DO ALL YOUR JOINS */ 
             join ds in dbContext.DR_DealStage on d.statusID equals ds.dealstageID
             where ds.mycolumn == myvalue

             select new MyObject()
             {
                   groupedByObject = d,
                   singleColumnsValue = ds.columnName
                   /* For calculated values have inner LINQ statements which join
                   back to elements from the main query. These can also 
                   return objects*/
                   agregateValue = (from x in dbContext.OtherTable
                                    where x.id == d.id).Count()
             })

Answer 2

我总是依赖 101 个 LinQ 示例

http://msdn.microsoft.com/en-us /vcsharp/aa336746