Query samples on a self referenced table (Part-4)

In previous post we joined tables but there was a problem. We can not see "Help" menu which has not any sub menu. To solve the problem we will use Outer Join.

Linq

var menus = from t in Menus
  where t.MenuType==1
  join s in Menus on t.MenuID equals s.MenuParent into Subs
  from ss in Subs.DefaultIfEmpty()
  select new {
   TopMenu = t.Title,
   SubMenu = ss == null ? null : ss.Title
  };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .GroupJoin (
      Menus, 
      t => (t.MenuID), 
      s => s.MenuParent, 
      (t, Subs) => 
         new  
         {
            t = t, 
            Subs = Subs
         }
   )
   .SelectMany (
      temp0 => temp0.Subs.DefaultIfEmpty (), 
      (temp0, ss) => 
         new  
         {
            TopMenu = temp0.t.Title, 
            SubMenu = (ss == null) ? null : ss.Title
         }
   )

SQL

DECLARE @p0 Int = 1

SELECT [t0].[Title] AS [TopMenu], 
    (CASE 
        WHEN [t2].[test] IS NULL THEN NULL
        ELSE CONVERT(NVarChar(MAX),[t2].[Title])
     END) AS [SubMenu]
FROM [Menu] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[Title], [t1].[MenuParent]
    FROM [Menu] AS [t1]
    ) AS [t2] ON ([t0].[MenuID]) = [t2].[MenuParent]
WHERE [t0].[MenuType] = @p0

Read More