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.