Hey folks,

I have this query:

page = (from c in meta.Page.WithPath(p => p.Prefetch<PageDetailEntity>(z => z.StagedPageDetail).SubPath(d => d.Prefetch<MasterPageEntity>(m => m.MasterPage))).WithPath(p => p.Prefetch<PageDetailEntity>(z => z.LivePageDetail))
                            where c.PageId == pageId
                            select c).First();

Which isn't working properly. Both the StagedPageDetail and LivePageDetail are null (there is no live page detail so thats expected). If I remove the prefetch for LivePageDetail I get the staged page detail.

I need trace on what was being executed and I can see llbl isn't even trying to execute the prefetchs.

exec sp_executesql N'SELECT DISTINCT TOP 1 [LPLA_1].[PageId], [LPLA_1].[LiveVersionNo], [LPLA_1].[StagedVersionNo], [LPLA_1].[DuplicatePageId] FROM [ShivamCMS].[dbo].[Page] [LPLA_1] WHERE ( ( ( ( ( ( [LPLA_1].[PageId] = @PageId1))))))',N'@PageId1 int',@PageId1=15 go

where as if I remove the LivePageDetail prefetch:

exec sp_executesql N'SELECT DISTINCT TOP 1 [LPLA_1].[PageId], [LPLA_1].[LiveVersionNo], [LPLA_1].[StagedVersionNo], [LPLA_1].[DuplicatePageId] FROM [ShivamCMS].[dbo].[Page] [LPLA_1] WHERE ( ( ( ( ( [LPLA_1].[PageId] = @PageId1)))))',N'@PageId1 int',@PageId1=15 go exec sp_executesql N'SELECT [ShivamCMS].[dbo].[PageDetail].[PageId] AS [F1_0], [ShivamCMS].[dbo].[PageDetail].[VersionNo] AS [F1_1], [ShivamCMS].[dbo].[PageDetail].[MasterPageId] AS [F1_2], [ShivamCMS].[dbo].[PageDetail].[PageTypeId] AS [F1_3], [ShivamCMS].[dbo].[PageDetail].[ParentPageId] AS [F1_4], [ShivamCMS].[dbo].[PageDetail].[CommittedByUserId] AS [F1_5], [ShivamCMS].[dbo].[PageDetail].[MemberListId] AS [F1_6], [ShivamCMS].[dbo].[PageDetail].[ThemeId] AS [F1_7], [ShivamCMS].[dbo].[PageDetail].[StyleSheetThemeId] AS [F1_8], [ShivamCMS].[dbo].[PageDetail].[CacheDuration] AS [F1_9], [ShivamCMS].[dbo].[PageDetail].[SlidingAccessStart] AS [F1_10], [ShivamCMS].[dbo].[PageDetail].[SlidingAccessEnd] AS [F1_11], [ShivamCMS].[dbo].[PageDetail].[Name] AS [F1_12], [ShivamCMS].[dbo].[PageDetail].[Detail] AS [F1_13], [ShivamCMS].[dbo].[PageDetail].[Title] AS [F1_14], [ShivamCMS].[dbo].[PageDetail].[MetaKeyword] AS [F1_15], [ShivamCMS].[dbo].[PageDetail].[MetaDescription] AS [F1_16], [ShivamCMS].[dbo].[PageDetail].[HasFooter] AS [F1_17], [ShivamCMS].[dbo].[PageDetail].[IsDefault] AS [F1_18], [ShivamCMS].[dbo].[PageDetail].[HasExpiry] AS [F1_19], [ShivamCMS].[dbo].[PageDetail].[IsInNavigationMenu] AS [F1_20], [ShivamCMS].[dbo].[PageDetail].[MenuIndex] AS [F1_21], [ShivamCMS].[dbo].[PageDetail].[MenuTooltip] AS [F1_22], [ShivamCMS].[dbo].[PageDetail].[RequestPath] AS [F1_23], [ShivamCMS].[dbo].[PageDetail].[StartDate] AS [F1_24], [ShivamCMS].[dbo].[PageDetail].[ExpiryDate] AS [F1_25], [ShivamCMS].[dbo].[PageDetail].[UpdatedBy] AS [F1_26], [ShivamCMS].[dbo].[PageDetail].[UpdatedOn] AS [F1_27], [ShivamCMS].[dbo].[PageDetail].[CreatedBy] AS [F1_28], [ShivamCMS].[dbo].[PageDetail].[CreatedOn] AS [F1_29], [ShivamCMS].[dbo].[PageDetail_Faq].[PageId] AS [F2_30], [ShivamCMS].[dbo].[PageDetail_Faq].[VersionNo] AS [F2_31], [ShivamCMS].[dbo].[PageDetail_LinkPage].[PageId] AS [F3_30], [ShivamCMS].[dbo].[PageDetail_LinkPage].[VersionNo] AS [F3_31], [ShivamCMS].[dbo].[PageDetail_LinkPage].[LinkListId] AS [F3_32], [ShivamCMS].[dbo].[PageDetail_News].[PageId] AS [F4_30], [ShivamCMS].[dbo].[PageDetail_News].[VersionNo] AS [F4_31], [ShivamCMS].[dbo].[PageDetail_News].[AuthorId] AS [F4_32], [ShivamCMS].[dbo].[PageDetail_NewsList].[PageId] AS [F5_30], [ShivamCMS].[dbo].[PageDetail_NewsList].[VersionNo] AS [F5_31], [ShivamCMS].[dbo].[PageDetail_NewsList].[PageListId] AS [F5_32] FROM (((( [ShivamCMS].[dbo].[PageDetail] LEFT JOIN [ShivamCMS].[dbo].[PageDetail_Faq] ON [ShivamCMS].[dbo].[PageDetail].[PageId]=[ShivamCMS].[dbo].[PageDetail_Faq].[PageId] AND [ShivamCMS].[dbo].[PageDetail].[VersionNo]=[ShivamCMS].[dbo].[PageDetail_Faq].[VersionNo]) LEFT JOIN [ShivamCMS].[dbo].[PageDetail_LinkPage] ON [ShivamCMS].[dbo].[PageDetail].[PageId]=[ShivamCMS].[dbo].[PageDetail_LinkPage].[PageId] AND [ShivamCMS].[dbo].[PageDetail].[VersionNo]=[ShivamCMS].[dbo].[PageDetail_LinkPage].[VersionNo]) LEFT JOIN [ShivamCMS].[dbo].[PageDetail_News] ON [ShivamCMS].[dbo].[PageDetail].[PageId]=[ShivamCMS].[dbo].[PageDetail_News].[PageId] AND [ShivamCMS].[dbo].[PageDetail].[VersionNo]=[ShivamCMS].[dbo].[PageDetail_News].[VersionNo]) LEFT JOIN [ShivamCMS].[dbo].[PageDetail_NewsList] ON [ShivamCMS].[dbo].[PageDetail].[PageId]=[ShivamCMS].[dbo].[PageDetail_NewsList].[PageId] AND [ShivamCMS].[dbo].[PageDetail].[VersionNo]=[ShivamCMS].[dbo].[PageDetail_NewsList].[VersionNo]) WHERE ( ( ( [ShivamCMS].[dbo].[PageDetail].[PageId] = @PageId1 AND [ShivamCMS].[dbo].[PageDetail].[VersionNo] = @VersionNo2)))',N'@PageId1 int,@VersionNo2 int',@PageId1=15,@VersionNo2=1 go exec sp_executesql N'SELECT [ShivamCMS].[dbo].[MasterPage].[MasterPageId], [ShivamCMS].[dbo].[MasterPage].[Name], [ShivamCMS].[dbo].[MasterPage].[VirtualPath], [ShivamCMS].[dbo].[MasterPage].[Description], [ShivamCMS].[dbo].[MasterPage].[UpdatedBy], [ShivamCMS].[dbo].[MasterPage].[UpdatedOn], [ShivamCMS].[dbo].[MasterPage].[CreatedBy], [ShivamCMS].[dbo].[MasterPage].[CreatedOn] FROM [ShivamCMS].[dbo].[MasterPage] WHERE ( ( ( [ShivamCMS].[dbo].[MasterPage].[MasterPageId] = @MasterPageId1)))',N'@MasterPageId1 int',@MasterPageId1=1 go

All those joins are the inheriting tables...

So any ideas why I can't have two prefetches?

Heres a bit of the structure

Page Table

USE [ShivamCMS]
GO
/****** Object:  Table [dbo].[Page] Script Date: 01/30/2009 17:19:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Page](
    [PageId] [int] IDENTITY(1,1) NOT NULL,
    [LiveVersionNo] [int] NULL,
    [StagedVersionNo] [int] NULL,
    [DuplicatePageId] [int] NULL,
 CONSTRAINT [PK_Page_1] PRIMARY KEY CLUSTERED 
(
    [PageId] 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].[Page]  WITH NOCHECK ADD  CONSTRAINT [FK_Page_LivePageDetail] FOREIGN KEY([PageId], [LiveVersionNo])
REFERENCES [dbo].[PageDetail] ([PageId], [VersionNo])
GO
ALTER TABLE [dbo].[Page] CHECK CONSTRAINT [FK_Page_LivePageDetail]
GO
ALTER TABLE [dbo].[Page]  WITH CHECK ADD  CONSTRAINT [FK_Page_Page] FOREIGN KEY([DuplicatePageId])
REFERENCES [dbo].[Page] ([PageId])
GO
ALTER TABLE [dbo].[Page] CHECK CONSTRAINT [FK_Page_Page]
GO
ALTER TABLE [dbo].[Page]  WITH NOCHECK ADD  CONSTRAINT [FK_Page_StagedPageDetail] FOREIGN KEY([PageId], [StagedVersionNo])
REFERENCES [dbo].[PageDetail] ([PageId], [VersionNo])
GO
ALTER TABLE [dbo].[Page] CHECK CONSTRAINT [FK_Page_StagedPageDetail]

And heres PageDetail

USE [ShivamCMS]
GO
/****** Object:  Table [dbo].[PageDetail]   Script Date: 01/30/2009 17:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PageDetail](
    [PageId] [int] NOT NULL,
    [VersionNo] [int] NOT NULL,
    [MasterPageId] [int] NULL,
    [PageTypeId] [int] NOT NULL,
    [ParentPageId] [int] NULL,
    [CommittedByUserId] [int] NULL,
    [MemberListId] [int] NULL,
    [ThemeId] [int] NULL,
    [StyleSheetThemeId] [int] NULL,
    [CacheDuration] [int] NOT NULL CONSTRAINT [DF_PageDetail_CacheDuration]  DEFAULT ((0)),
    [SlidingAccessStart] [bigint] NULL,
    [SlidingAccessEnd] [bigint] NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Detail] [ntext] NULL,
    [Title] [nvarchar](128) NULL,
    [MetaKeyword] [nvarchar](256) NULL,
    [MetaDescription] [nvarchar](256) NULL,
    [HasFooter] [bit] NOT NULL CONSTRAINT [DF_PageDetails_ShowFooter]  DEFAULT ((0)),
    [IsDefault] [bit] NULL CONSTRAINT [DF_PageDetails_IsDefault]  DEFAULT ((0)),
    [HasExpiry] [bit] NOT NULL CONSTRAINT [DF_PageDetails_EnableExpiry]  DEFAULT ((0)),
    [IsInNavigationMenu] [bit] NULL CONSTRAINT [DF_PageDetails_NavigationMenu]  DEFAULT ((0)),
    [MenuIndex] [int] NOT NULL CONSTRAINT [DF_PageDetail_MenuIndex]  DEFAULT ((0)),
    [MenuTooltip] [nvarchar](128) NULL,
    [RequestPath] [nvarchar](256) NULL,
    [StartDate] [smalldatetime] NULL,
    [ExpiryDate] [smalldatetime] NULL,
    [UpdatedBy] [dbo].[CurrentUser] NOT NULL,
    [UpdatedOn] [dbo].[Date] NOT NULL,
    [CreatedBy] [dbo].[CurrentUser] NOT NULL,
    [CreatedOn] [dbo].[Date] NOT NULL,
 CONSTRAINT [PK_PageDetail] PRIMARY KEY CLUSTERED 
(
    [PageId] ASC,
    [VersionNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetail_MemberList] FOREIGN KEY([MemberListId])
REFERENCES [dbo].[MemberList] ([MemberListId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetail_MemberList]
GO
ALTER TABLE [dbo].[PageDetail]  WITH NOCHECK ADD  CONSTRAINT [FK_PageDetail_Page] FOREIGN KEY([PageId])
REFERENCES [dbo].[Page] ([PageId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetail_Page]
GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetail_ParentPage] FOREIGN KEY([ParentPageId])
REFERENCES [dbo].[Page] ([PageId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetail_ParentPage]
GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetail_StylesheetTheme] FOREIGN KEY([StyleSheetThemeId])
REFERENCES [dbo].[Theme] ([ThemeId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetail_StylesheetTheme]
GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetail_Theme] FOREIGN KEY([ThemeId])
REFERENCES [dbo].[Theme] ([ThemeId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetail_Theme]
GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetail_User] FOREIGN KEY([CommittedByUserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetail_User]
GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetails_MasterPageList] FOREIGN KEY([MasterPageId])
REFERENCES [dbo].[MasterPage] ([MasterPageId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetails_MasterPageList]
GO
ALTER TABLE [dbo].[PageDetail]  WITH CHECK ADD  CONSTRAINT [FK_PageDetails_PageType] FOREIGN KEY([PageTypeId])
REFERENCES [dbo].[PageType] ([PageTypeId])
GO
ALTER TABLE [dbo].[PageDetail] CHECK CONSTRAINT [FK_PageDetails_PageType]

RT lib version ? PRefetches only work if there is data returned. If there are no parents, there are no child entities fetched from the db.

LLBLGen Pro - Runtime Libraries 2.6

Release date: 19-dec-2008

There is a parent. As I said if I remove the second prefetch the query works. Add the second prefetch back in and the same query only get the top entity, no prefetches are executed.

I've been seeing the same kind of problem actually now that I think about it. I am guessing that the prefetch path writes over the prefetch path node in the generated expression somehow so that it is only the final prefetch path that actually gets executed. We saw that if we removed the second prefetch path, the first executed properly. If we added a second prefetch path, then only the second executed. It would be interesting to add a third prefetch path to see if I am correct.

I don't think you need to be calling WithPath multiple times. Rather, you can just make an additional call to .Prefetch at the root level.

If you use this approach instead, does it work?

var query = (from c in meta.Page
                  where c.PageId == pageId
                  select c)
                  .WithPath(p => 
                       p.Prefetch<PageDetailEntity>(z => z.StagedPageDetail)
                             .SubPath(d => d.Prefetch<MasterPageEntity>(m => m.MasterPage))
                       .Prefetch(z => z.LivePageDetail)
                  ).Single();

Using with-path twice is actually creating two prefetch paths for the same root, as Jez explains. So use 1 Withpath call.

That appears to have solved the problem. Thanks for the support!