如何将下面的 sql 分组依据语句转换为 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]

编辑

是

的，上面的SQL是使用Linqpad生成的，这就是我真正想要转换为LINQ的 SELECT COUNT(DISTINCT [t0].DealID) AS count, [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]) = LOWER(@Email)) OR (LOWER([t0].[CreatedBy]) = LOWER(@UserName))
GROUP BY [t0].[StatusID], [t1].[FriendlyName]
ORDER BY [t0].[StatusID]

我想我找到了使用 linq 进行count(distinct)的解决方案，我最初偶然发现了，但转换后的解决方案仍然不起作用，我认为下面的 linq 代码不起作用，因为我使用的是 sql 2000 并且生成的 sql 具有嵌套选择，并且 sql 2000 似乎有一些限制。我仍然不确定我的结论，可能是linq blelow是错误的altogther，或者可能有更好的方法来编写上面的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("[e-mail]")   
|| 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