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

Read More

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

In previous post we created a database table, filled it with sample data and saw some basic queries.

In this post we will query the table in { Menu.Title, IEnumerable<menu> } form. This will show Top menu title and sub menu items in a row.

Linq

var menus = from t in Menus 
 where t.MenuType==1 
 select new {
  t.Title,
  SubMenus = from s in Menus
  where t.MenuType==1 && t.MenuID==s.MenuParent
  select s
 };

Lambda

Menus
   .Where (t => (t.MenuType == 1))
   .Select (
      t => 
         new  
         {
            Title = t.Title, 
            SubMenus = Menus
               .Where (s => ((t.MenuType == 1) && ((Int32?)(t.MenuID) == s.MenuParent)))
         }
   )

SQL

DECLARE @p0 Int = 1
DECLARE @p1 Int = 1

SELECT [t0].[Title], [t1].[MenuID], [t1].[Title] AS [Title2], [t1].[Url], [t1].[MenuParent], [t1].[MenuType], (
    SELECT COUNT(*)
    FROM [Menu] AS [t2]
    WHERE ([t0].[MenuType] = @p0) AND (([t0].[MenuID]) = [t2].[MenuParent])
    ) AS [value]
FROM [Menu] AS [t0]
LEFT OUTER JOIN [Menu] AS [t1] ON ([t0].[MenuType] = @p0) AND (([t0].[MenuID]) = [t1].[MenuParent])
WHERE [t0].[MenuType] = @p1

Read More

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

I will give you some query examples in Linq, Lambda Expression and SQL syntax.
Examples are build on one table that includes self referenced data. We will create a menu table on the database and fill it with some sample data. Some menu items are top menus and the others are subs. Sub menu items has a parent id which is referenced another menu item on the same table.

Step 1: Create menu table on SQL Server.

CREATE TABLE [dbo].Menu(
 [MenuID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [nvarchar](50) NULL,
 [Url] [nvarchar](100) NULL,
 [MenuParent] [int] NULL, --If has parent fill this column with MenuID 
 [MenuType] [int] NULL --1: Top menu, 2:Sub menu
)

Step 2: Insert some data

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Accounts', '.aspx', 0, 1) --ID=1

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Users', '.aspx', 1, 2) --ID=2

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Customers', '.aspx', 1, 2) --ID=3

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Settings', '.aspx', 0, 1) --ID=4

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('App', '.aspx', 4, 2) --ID=5

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Web', '.aspx', 4, 2) --ID=6

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Service', '.aspx', 4, 2) --ID=7

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Parameters', '.aspx', 0, 1) --ID=8

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Account Type', '.aspx', 8, 2) --ID=9

INSERT INTO [dbo].[Menu] ([Title], [Url], [MenuParent], [MenuType]) VALUES ('Help', '.aspx', 0, 1) --ID=10

Step 3: Query table. In this example we will see some basic queries.

Basic Queries

Queries below selects top menus and sub menus.

Linq

var topMenus = from f in Menus where f.MenuType==1 select f;
var subMenus = from f in Menus where f.MenuType==2 select f;

Lambda

Menus
   .Where (f => (f.MenuType == 1))

Menus
   .Where (f => (f.MenuType == 2))

SQL

DECLARE @p0 Int = 1
SELECT [t0].[MenuID], [t0].[Title], [t0].[Url], [t0].[MenuParent], [t0].[MenuType]
FROM [Menu] AS [t0]
WHERE [t0].[MenuType] = @p0
GO

DECLARE @p1 Int = 2
SELECT [t0].[MenuID], [t0].[Title], [t0].[Url], [t0].[MenuParent], [t0].[MenuType]
FROM [Menu] AS [t0]
WHERE [t0].[MenuType] = @p1
GO

See Part-2 for the other queries.

Read More