Force LINQ to SQL to use RowNumber() instead of Top n When Using .Skip(0)

Question

Is there a way to force LINQ to SQL to avoid using TOP X when using Skip(0)? I have a query that runs just fine for every paged result...except for page 1. I've profiled the query and the introduction of a TOP clause just kills it. I'm perplexed on why that is, but it just does. However, using RowNumber Between 1 AND 10 works just fine.

The culprit seems to be an EXISTS condition in my WHERE clause. The produced SQL is below. In SQL Manager, this query runs fine and returns 14 results...however it times out once I add a TOP 10 (as LINQ would do). However, if I comment the EXISTS in my where clause, then the problem goes away.

SELECT 
    t0.ProtectiveOrderID, 
    t3.DocketID, 
    t3.DocketNumber AS CaseNumber, 
    t3.PartySuffix AS CaseNumberSuffix, 
    t5.FirstName AS RespondentNameFirst, 
    t5.MiddleName AS RespondentNameMiddle, 
    t5.LastName AS RespondentNameLast, 
    t5.NameSuffix AS RespondentNameSuffix,
    t4.FirstName AS ProtectedNameFirst, 
    t4.MiddleName AS ProtectedNameMiddle, 
    t4.LastName AS ProtectedNameLast, 
    t4.NameSuffix AS ProtectedNameSuffix, 
    t3.ChildNextFriendFirstName AS ChildNextFriendNameFirst, 
    t3.ChildNextFriendMiddleName AS ChildNextFriendNameMiddle, 
    t3.ChildNextFriendLastName AS ChildNextFriendNameLast, 
    t3.ChildNextFriendNameSuffix
FROM dbo.ProtectiveOrder AS t0
INNER JOIN (
SELECT MAX(t1.ProtectiveOrderID) AS value
FROM dbo.ProtectiveOrder AS t1
GROUP BY t1.DocketID
) AS t2 ON t0.ProtectiveOrderID = t2.value
LEFT OUTER JOIN dbo.Docket AS t3 ON t3.DocketID = t0.DocketID
LEFT OUTER JOIN dbo.Directory AS t4 ON t4.DirectoryID = t3.ProtectedPartyID
LEFT OUTER JOIN dbo.Directory AS t5 ON t5.DirectoryID = t3.SubjectID
WHERE 
(
    ((t4.LastName LIKE 'smith%') AND (t4.FirstName LIKE 'jane%')) 
    OR ((t5.LastName LIKE 'smith%') AND (t5.FirstName LIKE 'jane%')) 
    OR ((t3.ChildNextFriendLastName LIKE 'smith%') AND (t3.ChildNextFriendFirstName LIKE 'jane%')) 
    OR (
            -- ***************
            -- THIS GUY KILLS THE QUERY WHEN A TOP IS INTRODUCED IN THE TOP-LEVEL SELECT
            -- ***************
            EXISTS(
                    SELECT NULL AS EMPTY
                    FROM dbo.Child AS t6
                    WHERE (t6.LastName LIKE 'smith%') AND (t6.FirstName LIKE 'jane%') AND (t6.DocketID = t3.DocketID)
            )
    )
)
ORDER BY t3.DocketNumber

Might want to post the LINQ Query too. I just tried some sample queries using Joins, Skips, and Takes and every one of them resulted in the use of ROW_NUMBER(). — Jeff Machamer, Aug 04 '11 at 17:02

score 1 · Answer 1 · answered Aug 03 '11 at 20:14

1

Override the Skip method and just check the input for zero. For any value but zero call the original skip method. For zero don't.

so if you modify the Skip provided in dynamic.cs you could do:

    public static IQueryable Skip(this IQueryable source, int count)
    {
        if (count == 0)
        {
            return source;
        }
        if (source == null) throw new ArgumentNullException("source");
        return source.Provider.CreateQuery(
            Expression.Call(
                typeof(Queryable), "Skip",
                new Type[] { source.ElementType },
                source.Expression, Expression.Constant(count)));
    }

answered Aug 03 '11 at 20:14

Jeff Machamer

942
4
7

I don't see how this eliminates the TOP clause. My .Skip(0).Take(10) still results in TOP (10) with this code. What I want is .Skip(0).Take(10) to result in Select RowNumber()...WHERE RowNumber Between 1 and 10 (as it does for any value other than 0 for Skip). Am I missing something? – Jason Butera Aug 04 '11 at 13:25
Based upon your question you said that .Skip(0) is what results in the TOP(10) occurring. If you use the new skip method, Skip(0) basically will result in skip not being called. If that doesn't fix the problem then the problem isn't a direct result of .Skip(0) as you stated. Does removing the .Skip(0) and leaving the .Take(10) result in the problem occurring? – Jeff Machamer Aug 04 '11 at 16:50
It's the combination of Skip and Take. .Skip(0).Take(10) results in TOP (10) as does .Take(10) by itself. It's not until you have a number greater than 0 in Skip(x) that results in the SQL paging using RowNumber(). I need the query to run using SQL paging even when not skipping any rows. – Jason Butera Aug 04 '11 at 17:31
If .Take(10) does not result in the problem, then you didn't use the .Skip method I provided. If you had used that method then the result would look exactly as if you did not use the .Skip method at all. More than likely it still called the original implementation of .Skip instead of this one. Change the name of this one to something besides Skip so that you know the correct one is being called. – Jeff Machamer Aug 04 '11 at 19:30

Force LINQ to SQL to use RowNumber() instead of Top n When Using .Skip(0)

1 Answers1