我正在尝试将sql语句转换为linq/实体框架。我对使用sql还很陌生，现在很困难。下面是sql。

DECLARE @tbl TABLE (
  RowNum INT, FName VARCHAR(9), MInitial VARCHAR(1),
  LName VARCHAR(30), LegalAddress VARCHAR(50), MailAddress VARCHAR(50),
  AmDescr VARCHAR(50), EmpCount DECIMAL, EmpStatus SmallInt, Occupation VARCHAR(40) )

INSERT INTO @tbl
SELECT
  ROW_NUMBER() OVER(ORDER BY LName ASC) AS RowNum,
  FName,
  MInitial,
  LName,
  LegalAddress,
  MailAddress,
  AmDescr,
  agent.EmpCount,
  agent.EmpStatus,
  agentEmployer.Occupation
  FROM (
    SELECT
      trust.FName,
      trust.MInitial,
      trust.LName
      tla.Address,
      tma.Address,
      at.Descr,
      trust.EmpStatus
      (SELECT MAX(EMPCOUNT) FROM AGENTEMPLOYERS WHERE agent.SSN = trust.SSN) AS EmpCount
    FROM CLIENTSSUITS cs
    INNER JOIN TRUSTS trust ON trust.SSN = cs.CLIENT
    FULL JOIN ADV_TYPES at ON at.CODE = trust.AT
    LEFT OUTER JOIN CLIENTADDRESSES tla ON tla.SSN = trust.SSN AND tla.ID = 'L'
    LEFT OUTER JOIN CLIENTADDRESSES tma ON tma.SSN = trust.SSN AND tma.ID = 'M'
    WHERE cs.PRIMARY = w AND SecondaryRole = x AND cs.ID = y AND cs.Rev = z AND cs.DELETED = 0
    GROUP BY trust.FName,
      trust.MName,
      trust.LName,
      tla.Address,
      tma.Address,
      at.Descr,
      trust.EmpStatus
  ) agent
LEFT OUTER JOIN AGENTEMPLOYERS agentEmployer on agentEmployer.SSN = agent.SSN AND agentEmployer.EmpCount = agent.EmpCount

给我带来最大麻烦的是 GROUP BY 声明 agent 变量（别名）和 LEFT OUTER JOIN 在 AGENTEMPLOYERS .
当我试着写内心 SELECT ... FROM CLIENTSUITS ，的 GROUP BY 把我甩了。我的结局是 IGrouping 我不知道怎么用的东西。
“agent”变量只是保存 SELECT ... FROM CLIENTSUITS 阻止？我以前从未见过这样写的sql。此外，在外部 INSERT INTO @tbl ，的 EmpCounter 以及 EmpStatus 价值观来自“代理”，但内在的 SELECT ... FROM CLIENTSUITS 部分来自信任(我猜如果'agent'变量只包含 SELECT ... FROM CLIENTSUITS 那我觉得是一样的。
最后呢 LEFT OUTER JOIN ，我觉得很奇怪。它只是对“agent”的结果进行连接吗？以这种方式编写它相对于在内部sql语句中使用它有什么好处？
提前感谢您的任何回答，帮助我将此转换为ef linq。
编辑：尝试解决。我得到所有未分组的数据，然后将其分组，然后尝试进行最后的计算 LEFT OUTER JOIN 但在这里我可以访问 IGrouping 对象，我不知道该怎么办。然后在这里我计划用我需要的所有值来构造这个对象。
我现在看到我现在缺少rownum了，但是我认为我应该能够通过lname对它进行排序，然后转换成一个列表，并且仍然有效地通过遍历列表来获得rownum。

var tblDataUngrouped = (from cs in ctx.Clientsuits
                       // inner join on trusts
                       join trust in ctx.Trusts
                       on cs.Client equals trust.Ssn
                       // join on AdvTypes
                       join at in ctx.AdvTypes
                       on trust.AT equals at.Code into temp
                       from subAT in temp.DefaultIfEmpty()
                       // join on Clientaddresses, Legal
                       join tla in ctx.Clientaddresses
                       on new { ssn = trust.Ssn, psind = "L" } equals new { ssn = tla.Ssn, psind = tla.Psind } into temp2
                       from subTla in temp2.DefaultIfEmpty()
                       // join on Clientaddresses, Mail
                       join tma in ctx.Clientaddresses
                       on new { ssn = trust.Ssn, psind = "M" } equals new { ssn = tma.Ssn, psind = tma.Psind } into temp3
                       from subTma in temp3.DefaultIfEmpty()
                       where cs.Primaryclient == w && cs.SecondaryRole == x && cs.Idnum == y && cs.Revnum == z && cs.Deleted == 0
                       select new
                            {
                               trustee.Firstname,
                               trustee.Middleinitial,
                               trustee.Lastname,
                               trust.EmpStatus,
                               legalAddr = subTla.Address,
                               mailAddr = subTma.Address,
                               atDescr = subAT.Descr,
                               empCounter = ctx.Agentemployers.Where(ae => ae.Ssn == trust.Ssn).Max(ae => ae.Empcounter)
                            }).ToList();

// Group the table data
var tblDataGrouped = trusteeTblDataUngrouped.GroupBy(x => new
   {
       x.FName,
       x.MInital,
       x.LName,
       x.legalAddr,
       x.mailAddr,
       x.atDescr,
       x.EmpStatus
   });

var tblData = from trustee tblDataGrouped
              join agentEmp in ctx.AgentEmployers
              on new { /* HERE_1 */ } equals new { agentEmp.SSN, agentEmp.EmpCounter  } into temp
              from subEmployer in temp.DefaultIfEmpty
              select new { /* HERE_2 */ };