Version 3.1 Build: May 13, 2011 DB: SQL Server 2008 R2 using Adapter model

Summary TakePage in LINQ to LLBLGen isn't doing paging at the SQL Server when the query has a left join and coalesce orderby expression.

Problem Query The query below highlights the problem. It uses TakePage and it has a left join with a coalesce order by expression. I know I am not using the left join, but I've simplified the query by removing the where clause for this example.

List<LearningProductEntity> products =
    (from lp in metaData.LearningProduct
     join lpt in metaData.LearningProductTag on lp.Id equals lpt.ProductId into tags
     from t in tags.DefaultIfEmpty()
     orderby lp.NoteworthyDate ?? lp.UpdatedUtc
     select lp).TakePage(2, 10).ToList();

Resulting SQL This result shows that the paging did not occur at SQL Server.

SELECT [LPA_L1].[AllowsComments], [LPA_L1].[AlternateIdInbound], [LPA_L1].[AlternateIdOutbound], [LPA_L1].[DefaultTermDays], [LPA_L1].[DescriptionHtml], [LPA_L1].[ExpirationDate], [LPA_L1].[IconFilename], [LPA_L1].[Id], [LPA_L1].[NoteworthyDate], [LPA_L1].[OrganizationAlternateId], [LPA_L1].[OrganizationId], [LPA_L1].[PreviewMediaHeight], [LPA_L1].[PreviewMediaMP4VideoFilename] AS [PreviewMediaMp4VideoFilename], [LPA_L1].[PreviewMediaPosterImageFilename], [LPA_L1].[PreviewMediaWidth], [LPA_L1].[PublishedUTC] AS [PublishedUtc], [LPA_L1].[Rate1], [LPA_L1].[Rate2], [LPA_L1].[TeaserHtml], [LPA_L1].[Title], [LPA_L1].[TypeId], [LPA_L1].[UpdatedByLearnerId], [LPA_L1].[UpdatedUTC] AS [UpdatedUtc] FROM ( [Nucleus].[Learning].[Product] [LPA_L1]  LEFT JOIN [Nucleus].[Learning].[ProductTag] [LPA_L2]  ON  [LPA_L1].[Id] = [LPA_L2].[ProductId]) ORDER BY COALESCE([LPA_L1].[NoteworthyDate], [LPA_L1].[UpdatedUTC]) ASC

Query without Coalesce Orderby If I change the orderby expression to be a simple orderby (instead of a coalesce), then the paging does occur at SQL Server.

List<LearningProductEntity> products =
    (from lp in metaData.LearningProduct
     join lpt in metaData.LearningProductTag on lp.Id equals lpt.ProductId into tags
     from t in tags.DefaultIfEmpty()
     orderby lp.NoteworthyDate
     select lp).TakePage(2, 10).ToList();

Resulting SQL This result shows that the paging did occur at SQL Server.

exec sp_executesql N'WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT DISTINCT TOP 20 [LPA_L1].[AllowsComments], [LPA_L1].[AlternateIdInbound], [LPA_L1].[AlternateIdOutbound], [LPA_L1].[DefaultTermDays], [LPA_L1].[DescriptionHtml], [LPA_L1].[ExpirationDate], [LPA_L1].[IconFilename], [LPA_L1].[Id], [LPA_L1].[NoteworthyDate], [LPA_L1].[OrganizationAlternateId], [LPA_L1].[OrganizationId], [LPA_L1].[PreviewMediaHeight], [LPA_L1].[PreviewMediaMP4VideoFilename] AS [PreviewMediaMp4VideoFilename], [LPA_L1].[PreviewMediaPosterImageFilename], [LPA_L1].[PreviewMediaWidth], [LPA_L1].[PublishedUTC] AS [PublishedUtc], [LPA_L1].[Rate1], [LPA_L1].[Rate2], [LPA_L1].[TeaserHtml], [LPA_L1].[Title], [LPA_L1].[TypeId], [LPA_L1].[UpdatedByLearnerId], [LPA_L1].[UpdatedUTC] AS [UpdatedUtc] FROM ( [Nucleus].[Learning].[Product] [LPA_L1]  LEFT JOIN [Nucleus].[Learning].[ProductTag] [LPA_L2]  ON  [LPA_L1].[Id] = [LPA_L2].[ProductId]) ORDER BY [LPA_L1].[NoteworthyDate] ASC) AS _tmpSet) SELECT [AllowsComments], [AlternateIdInbound], [AlternateIdOutbound], [DefaultTermDays], [DescriptionHtml], [ExpirationDate], [IconFilename], [Id], [NoteworthyDate], [OrganizationAlternateId], [OrganizationId], [PreviewMediaHeight], [PreviewMediaMp4VideoFilename], [PreviewMediaPosterImageFilename], [PreviewMediaWidth], [PublishedUtc], [Rate1], [Rate2], [TeaserHtml], [Title], [TypeId], [UpdatedByLearnerId], [UpdatedUtc] FROM __actualSet WHERE [__rowcnt] > @p1 AND [__rowcnt] <= @p2 ORDER BY [__rowcnt] ASC',N'@p1 int,@p2 int',@p1=10,@p2=20

Expected SQL I would expect the following SQL statement when using LINQ statement at the top of this thread. The coalesce requires an additional select column (I think).

exec sp_executesql N'
WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (
SELECT DISTINCT TOP 20 
    [LPA_L1].[AllowsComments], [LPA_L1].[AlternateIdInbound], [LPA_L1].[AlternateIdOutbound], 
    [LPA_L1].[DefaultTermDays], [LPA_L1].[DescriptionHtml], [LPA_L1].[ExpirationDate], 
    [LPA_L1].[IconFilename], [LPA_L1].[Id], [LPA_L1].[NoteworthyDate], 
    [LPA_L1].[OrganizationAlternateId], [LPA_L1].[OrganizationId], [LPA_L1].[PreviewMediaHeight], 
    [LPA_L1].[PreviewMediaMP4VideoFilename] AS [PreviewMediaMp4VideoFilename], 
    [LPA_L1].[PreviewMediaPosterImageFilename], [LPA_L1].[PreviewMediaWidth], 
    [LPA_L1].[PublishedUTC] AS [PublishedUtc], [LPA_L1].[Rate1], [LPA_L1].[Rate2], 
    [LPA_L1].[TeaserHtml], [LPA_L1].[Title], [LPA_L1].[TypeId], [LPA_L1].[UpdatedByLearnerId], 
    [LPA_L1].[UpdatedUTC] AS [UpdatedUtc] 

-- ***** MISSING THIS IN THE SELECT. REQUIRED BY COALESCE ORDER BY  ********
    ,COALESCE([LPA_L1].[NoteworthyDate], [LPA_L1].[UpdatedUTC]) as SomeGeneratedColumnName

FROM ( [Nucleus].[Learning].[Product] [LPA_L1]  LEFT JOIN [Nucleus].[Learning].[ProductTag] [LPA_L2]  ON  [LPA_L1].[Id] = [LPA_L2].[ProductId]) 

-- ***** COALESCE ORDER BY **********
order by COALESCE([LPA_L1].[NoteworthyDate], [LPA_L1].[UpdatedUTC]) ASC 

) AS _tmpSet) SELECT [AllowsComments], [AlternateIdInbound], [AlternateIdOutbound], [DefaultTermDays], [DescriptionHtml], [ExpirationDate], [IconFilename], [Id], [NoteworthyDate], [OrganizationAlternateId], [OrganizationId], [PreviewMediaHeight], [PreviewMediaMp4VideoFilename], [PreviewMediaPosterImageFilename], [PreviewMediaWidth], [PublishedUtc], [Rate1], [Rate2], [TeaserHtml], [Title], [TypeId], [UpdatedByLearnerId], [UpdatedUtc] FROM __actualSet WHERE [__rowcnt] > @p1 AND [__rowcnt] <= @p2 ORDER BY [__rowcnt] ASC',N'@p1 int,@p2 int',@p1=10,@p2=20

This is by design. Distinct has to be in the query, but that can't be applied as the order by clause targets an element which isn't in the projection. When distinct is added to the query, the query will cause an error in sqlserver. The paging is done on the client side, by skipping (pagesize*(pagenumber-1)) rows from the datareader and then reading pagesize rows.

Thanks for your reply. With the distinct, all that is needed is that the coalesce orderby needs to be in the select. See my last example query. If you can add that, then the query will work as expected. Is this something you are willing to change?

aspicer wrote:

Thanks for your reply. With the distinct, all that is needed is that the coalesce orderby needs to be in the select. See my last example query. If you can add that, then the query will work as expected. Is this something you are willing to change?

No, we can't do that, because it has the same reason why the query with the distinct originally fails: the ORDER BY clause adds a hidden column to the resultset in the DB if the sorted column isn't in the resultset right before projection and distinct filtering. As you can see, this could hurt distinct filtering, because the hidden column (which is taken into account during distinct filtering by sqlserver) is perhaps unique per row, so distinct makes no sense however it's required there to make the query work on the server. This way, we switch to client side distinct filtering and paging, as there's no other way to do it.

Thanks for the explanation. One last question... in my case I don't think i really need a Distinct. More testing on my side will be needed to verify whether or not i can get away without specifying distinct. Is there a way for me to achieve server side paging using the coalesce orderby and a left join without using distinct?

No unfortunately not. The logic tries to determine whether duplicates could occur, and if so, it will force a distinct on the query. What you could do is determine whether the coalesce is really needed (i.e. if a null for NoteworthyDate would ruin the order)