Can we control LINQ expression order with Skip(), Take() and OrderBy()

Question

I'm using LINQ to Entities to display paged results. But I'm having issues with the combination of Skip(), Take() and OrderBy() calls.

Everything works fine, except that OrderBy() is assigned too late. It's executed after result set has been cut down by Skip() and Take().

So each page of results has items in order. But ordering is done on a page handful of data instead of ordering of the whole set and then limiting those records with Skip() and Take().

How do I set precedence with these statements?

My example (simplified)

var query = ctx.EntitySet.Where(/* filter */).OrderByDescending(e => e.ChangedDate);
int total = query.Count();
var result = query.Skip(n).Take(x).ToList();

One possible (but a bad) solution

One possible solution would be to apply clustered index to order by column, but this column changes frequently, which would slow database performance on inserts and updates. And I really don't want to do that.

EDIT

I ran ToTraceString() on my query where we can actually see when order by is applied to the result set. Unfortunately at the end. :(

SELECT 
-- columns
FROM  (SELECT 
    -- columns
    FROM   (SELECT -- columns
        FROM ( SELECT 
            -- columns
            FROM table1 AS Extent1
            WHERE  EXISTS (SELECT 
                -- single constant column
                FROM table2 AS Extent2
                WHERE (Extent1.ID = Extent2.ID) AND (Extent2.userId = :p__linq__4)
            )
        )  AS Project2
        limit 0,10  ) AS Limit1
    LEFT OUTER JOIN  (SELECT 
        -- columns
        FROM table2 AS Extent3 ) AS Project3 ON Limit1.ID = Project3.ID
UNION ALL
    SELECT 
    -- columns
    FROM   (SELECT -- columns
        FROM ( SELECT 
            -- columns
            FROM table1 AS Extent4
            WHERE  EXISTS (SELECT 
                -- single constant column
                FROM table2 AS Extent5
                WHERE (Extent4.ID = Extent5.ID) AND (Extent5.userId = :p__linq__4)
            )
        )  AS Project6
        limit 0,10  ) AS Limit2
    INNER JOIN table3 AS Extent6 ON Limit2.ID = Extent6.ID) AS UnionAll1
ORDER BY UnionAll1.ChangedDate DESC, UnionAll1.ID ASC, UnionAll1.C1 ASC

Answer 1

One way:

var query = ctx.EntitySet.Where(/* filter */).OrderBy(/* expression */).ToList();
int total = query.Count;
var result = query.Skip(n).Take(x).ToList();

Convert it to a List before skipping. It's not too efficient, mind you...

Answer 2

Assuming from you comment the persisting the values in a List is not acceptable:

There's no way to completely minimize the iterations, as you intended (and as I would have tried too, living in hope). Cutting the iterations down by one would be nice. Is it possible to just get the Count once and cache/session it? Then you could:

int total = ctx.EntitySet.Count;  // Hopefully you can not repeat doing this.
var result = ctx.EntitySet.Where(/* filter */).OrderBy(/* expression */).Skip(n).Take(x).ToList();

Hopefully you can cache the Count somehow, or avoid needing it every time. Even if you can't, this is the best you can do.

Answer 3

Are you absolutely certain the ordering is off? What does the SQL look like?

Can you reorder your code as follows and post the output?

// Redefine your queries. 
var query = ctx.EntitySet.Where(/* filter */).OrderBy(e => e.ChangedDate); 
var skipped = query.Skip(n).Take(x);

// let's look at the SQL, shall we?
var querySQL = query.ToTraceString();
var skippedSQL = skipped.ToTraceString();

// actual execution of the queries...
int total = query.Count(); 
var result = skipped.ToList(); 

Edit:

I'm absolutely certain. You can check my "edit" to see trace result of my query with skipped trace result that is imperative in this case. Count is not really important.

Yeah, I see it. Wow, that's a stumper. Might even be an outright bug. I note you're not using SQL Server... what DB are you using? Looks like it might be MySQl.

Answer 4

I haven't worked directly with Linq to Entities, but it should have a way to hook specific stored procedures into certain locations when needed. (Linq to SQL did.) If so, you could turn this query into a stored procedure, doing exacly what is required, and doing it efficiently.

Answer 5

Hello, Robert.
Could you please create a sample illusrating the problem and send it to us (support * devart * com, subject "EF: Skip, Take, OrderBy")?
Hope we will be able to help you.
You can also contact us using our forums or contact form.

Answer 6

My workaround solution

I've managed to workaround this problem. Don't get me wrong here. I haven't solved precedence issue as of yet, but I've mitigated it.

What I did?

This is the code I've used until I get an answer from Devart. If they won't be able to overcome this issue I'll have to use this code in the end.

// get ordered list of IDs
List<int> ids = ctx.MyEntitySet
    .Include(/* Related entity set that's needed in where clause */)
    .Where(/* filter */)
    .OrderByDescending(e => e.ChangedDate)
    .Select(e => e.Id)
    .ToList();

// get total count
int total = ids.Count;

if (total > 0)
{
    // get a single page of results
    List<MyEntity> result = ctx.MyEntitySet
        .Include(/* related entity set (as described above) */)
        .Include(/* additional entity set that's neede in end results */)
        .Where(string.Format("it.Id in {{{0}}}", string.Join(",", ids.ConvertAll(id => id.ToString()).Skip(pageSize * currentPageIndex).Take(pageSize).ToArray())))
        .OrderByDescending(e => e.ChangedOn)
        .ToList();
}

First of all I'm getting ordered IDs of my entities. Getting only IDs is well performant even with larger set of data. MySql query is quite simple and performs really well. In the second part I partition these IDs and use them to get actual entity instances.

Thinking of it, this should perform even better than the way I was doing it at the beginning (as described in my question), because getting total count is much much quicker due to simplified query. The second part is practically very very similar, except that my entities are returned rather by their IDs instead of partitioned using Skip and Take...

Hopefully someone may find this solution helpful.