Jan 23, 2015

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

In previous post we queried tables.
In this post we will join the tables. This will show Top Menu title and Sub Menu title in a row.

Linq

var menus = from t in Menus
  where t.MenuType == 1
  join s in Menus on t.MenuID equals s.MenuParent
  select new {
   TopMenu = t.Title,
   SubMenu = s.Title
  };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .Join (
      Menus, 
      t => (Int32?)(t.MenuID), 
      s => s.MenuParent, 
      (t, s) => 
         new  
         {
            TopMenu = t.Title, 
            SubMenu = s.Title
         }
   )

SQL

DECLARE @p0 Int = 1

SELECT [t0].[Title] AS [TopMenu], [t1].[Title] AS [SubMenu]
FROM [Menu] AS [t0]
INNER JOIN [Menu] AS [t1] ON ([t0].[MenuID]) = [t1].[MenuParent]
WHERE [t0].[MenuType] = @p0

0 comments: