LINQ query issue: returning multiple records

I have 2 tables. Problem is I always get multiple records no matter what I do.

1. Pages
2. SubMenus

Relation is 1 to 1 (each submenu has a page)

SubMenus sample data:

submenu_id  parentmenu_id   display_name    url_name
----------------------------------------------------
    1           1           Home            home    
    2           1           Contact         contact 

Pages table data:

page_id     submenu_id      page_title          url_name
---------------------------------------------------------
   1            1           Home Page           home    
   2            1           Contact Page        null

I want to retrieve single value from there JOIN where SubMenu.UrlName == Home

(from s in SubMenus
 join p in Pages on s.Id equals p.SubMenuId
 where s.UrlName == "home"
 select new 
          {
              s.Id, s.UrlName,
              PageId = p.Id, p.Title, p.Html, 
              p.MetaAuthor, p.MetaKeywords, p.MetaDescription 
          }).FirstOrDefault();

But if I check the SQL and run it. I get single record without any problem. Below is what SQL generated

SELECT TOP (1) 
    [Extent1].[submenu_id] AS [submenu_id], 
    [Extent1].[url_name] AS [url_name], 
    [Extent2].[page_id] AS [page_id], 
    [Extent2].[page_title] AS [page_title], 
    [Extent2].[page_html] AS [page_html], 
    [Extent2].[meta_author] AS [meta_author], 
    [Extent2].[meta_keywords] AS [meta_k开发者_如何转开发eywords], 
    [Extent2].[meta_description] AS [meta_description]
FROM  
    [dbo].[SubMenu] AS [Extent1]
INNER JOIN 
    [dbo].[Pages] AS [Extent2] ON [Extent1].[submenu_id] = [Extent2].[submenu_id]
WHERE 
    N'home' = [Extent1].[url_name]

Table structure:

CREATE TABLE [dbo].[ParentMenu]
(
    [parentmenu_id] [int] IDENTITY(1,1) NOT NULL,
    [group_name] [nvarchar](50) NULL,
    [title] [nvarchar](100) NULL,
    [active] [bit] NOT NULL,
    [index_order] [int] NULL,

    CONSTRAINT [PK_ParentMenu] 
        PRIMARY KEY CLUSTERED ([parentmenu_id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ParentMenu] 
    ADD CONSTRAINT [DF_ParentMenu_active] DEFAULT ((1)) FOR [active]
GO

ALTER TABLE [dbo].[ParentMenu] 
    ADD CONSTRAINT [DF_ParentMenu_index_order] DEFAULT ((0)) FOR [index_order]
GO

CREATE TABLE [dbo].[SubMenu]
(
    [submenu_id] [int] IDENTITY(1,1) NOT NULL,
    [parentmenu_id] [int] NOT NULL,
    [display_name] [nvarchar](100) NULL,
    [url_name] [nvarchar](100) NULL,
    [index_order] [int] NULL,
    [active] [bit] NOT NULL,

    CONSTRAINT [PK_SubMenu] 
        PRIMARY KEY CLUSTERED ([submenu_id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

    CONSTRAINT [IX_SubMenu_1] 
        UNIQUE NONCLUSTERED ([url_name] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SubMenu] WITH CHECK 
    ADD CONSTRAINT [FK_SubMenu_ParentMenu] 
        FOREIGN KEY([parentmenu_id]) REFERENCES [dbo].[ParentMenu] ([parentmenu_id])
             ON DELETE CASCADE
GO

ALTER TABLE [dbo].[SubMenu] CHECK CONSTRAINT [FK_SubMenu_ParentMenu]
GO

ALTER TABLE [dbo].[SubMenu] 
    ADD CONSTRAINT [DF_SubMenu_index_order] DEFAULT ((0)) FOR [index_order]
GO

ALTER TABLE [dbo].[SubMenu] 
    ADD CONSTRAINT [DF_SubMenu_active]  DEFAULT ((1)) FOR [active]
GO

CREATE TABLE [dbo].[Pages]
(
    [page_id] [int] IDENTITY(1,1) NOT NULL,
    [submenu_id] [int] NOT NULL,
    [page_title] [nvarchar](200) NULL,
    [url_name] [nvarchar](100) NULL,
    [page_html] [nvarchar](max) NULL,
    [meta_author] [nvarchar](300) NULL,
    [meta_keywords] [nvarchar](max) NULL,
    [meta_description] [nvarchar](max) NULL,
    [active] [bit] NOT NULL,
    [creation_date] [date] NULL,

    CONSTRAINT [PK_Pages] 
        PRIMARY KEY CLUSTERED ([page_id] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Pages] WITH CHECK 
    ADD CONSTRAINT [FK_Pages_SubMenu] 
        FOREIGN KEY([submenu_id]) REFERENCES [dbo].[SubMenu] ([submenu_id])
             ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Pages] CHECK CONSTRAINT [FK_Pages_SubMenu]
GO

ALTER TABLE [dbo].[Pages] 
    ADD CONSTRAINT [DF_Pages_active] DEFAULT ((1)) FOR [active]
GO

---

You are saying that Problem is I always get multiple records no matter what I do.

The query that you show has top 1 in the very first line, it will always return just one row.

What's wrong with it?

From what I see your SQL query matches what your LINQ query does.

In this case LINQ query cannot possible return more rows than SQL returns.