任何人都可以帮助我将SQL转换为linq查询。我尝试，但未能

Question

这里是我的SQL查询SQL中工作正常：

select ld.FolderId, count(ld.LeadId) LeadID, sum(note.noteCount) NoteCount, count(ld.CallResultId) Calls 
from LeadsDetails ld 
    left join 
    (
     select lnh.LeadId, Count(lnh.NoteId) as noteCount 
     from [dbo].[LeadNoteHistory] lnh 
     group by lnh.LeadId 
    )note 
    on note.LeadId=ld.LeadId 
group by ld.FolderId 

我试过 -

var query = 
    from lead in _context.LeadsDetails 
    join note in _context.LeadNoteHistories 
    on lead.LeadId equals note.LeadId into g 
    from notes in g.DefaultIfEmpty() 
    group lead by lead.FolderId into grp 
    select new 
    { 
     FolderId = g.FolderId, 
     LeadID = g.LeadId, 
     NoteCount = notes.NoteId, 
     Call = lead.CallResultId 
    }; 

不能得到正确的结果。请告诉我做错了什么。

Answer 1

以后您不能在select子句中访问变量'g'。你需要使用变量'grp'。您还需要通过修改最终组。我试着修改，看看，如果这个工程：

var query = 
    from lead in _context.LeadsDetails 
    join note in _context.LeadNoteHistories 
    on lead.LeadId equals note.LeadId into g 
    from notes in g.DefaultIfEmpty() 
    group new {lead,notes} lead by lead.FolderId into grp 
    select new 
    { 
     FolderId = grp.Key, 
     LeadID = grp.Count(), 
     NoteCount = grp.Count(x=>x.notes!=null), 
     Call = grp.Count() 
    }; 

Answer 2

要翻译SQL到LINQ，

1. 翻译子选择作为独立变量

2. 翻译在LINQ条款顺序为每个条款，留下一元运营商（DISTINCT，TOP等）作为适用于整个LINQ查询的函数。

3. 使用表别名作为范围变量。使用列别名作为匿名类型字段名称。对于多列

4. 左连接

5. 使用匿名类型（new { }）是通过使用连接变量做是另一回事from从加入变量，然后.DefaultIfEmpty()模拟。

这里是你的SQL翻译：

var rightside = from lnh in dbo.LeadNoteHistory 
       group lnh by lnh.LeadId into lnhg 
       select new { LeadId = lnhg.Key, noteCount = lnhg.Count() }; 

var ans = from ld in dbo.LeadsDetails 
      join note in rightside on ld.LeadId equals note.LeadId into notej 
      from note in notej.DefaultIfEmpty() 
      group new { ld, note } by ld.FolderId into ldnoteg 
      select new { 
       FolderId = ldnoteg.Key, 
       LeadID = ldnoteg.Select(lng => lng.ld.LeadId).Count(), 
       NoteCount = ldnoteg.Select(lng => lng.note.noteCount).Sum(), 
       Calls = ldnoteg.Select(lng => lng.ld.CallResultId).Count() 
      }; 

我离开LeadID定义你的SQL，但不看我的权利。