Linq 中Left join与 Group by一起使用

 直接看SQL语句：

 public IList<StructureRootDTO> GetRootStructureInfos()
        {
            using (ETCDataContext etcDataContext = this.EtcDataContext)
            {
                var query =
                       from item in
                           (
                               from parent in etcDataContext.SYS_Structures
                               where parent.ParentNode == "root"
                               join child in etcDataContext.SYS_Structures
                               on parent.Name equals child.ParentNode into childAll
                               from childItem in childAll.DefaultIfEmpty()
                               select new
                               {
                                   parent.ID,
                                   parent.Name,
                                   childItem.ParentNode
                               }
                           )
                       group item by item.Name into groupChild                      
                       select new StructureRootDTO
                       {
                           ID = groupChild.Max(o => o.ID),
                           Name = groupChild.Max(o => o.Name),
                           ChildParentNodeName = groupChild.Max(o => o.ParentNode)
                       };
                return query.OrderBy(o => o.ID).ToList();
            }
        }

 

生成query的SQL为：

 

SELECT MAX([t2].[ID]) AS [ID], MAX([t2].[Name]) AS [Name], MAX([t2].[value]) AS [ChildParentNodeName]
FROM (
    SELECT [t0].[ID], [t0].[Name], [t1].[ParentNode] AS [value], [t0].[ParentNode]
    FROM [dbo].[SYS_Structure] AS [t0]
    LEFT OUTER JOIN [dbo].[SYS_Structure] AS [t1] ON [t0].[Name] = [t1].[ParentNode]
    ) AS [t2]
WHERE [t2].[ParentNode] = 'root'
GROUP BY [t2].[Name]