Skip and Take not working as expected

Skip and Take not working as expected

Postby msw10100 » Tue 08 Oct 2013 01:34

Hi, I'm using LinqConnect 4.2.338 with Postgres. I am trying to implement paging in my application, and I see that when I use Skip(X).Take(Y), my queries are returning the skip count of X rows instead of the take count of Y rows. I've recreated this outside my full application with the tiny routine seen below.

Code: Select all
using (
    var dc = new ABCDataContext("User Id=ABC;Password=ABC;Host=localhost;Database=blueridge;Schema=public")
    )
{
    var skip = 0;
    var take = 100;
    var readSoFar = 0;
    while (readSoFar < 1000)
    {
        var query = from it in dc.AuditRecords
                    orderby it.Id
                    select it;
        var results = query.Skip(skip).Take(take);
        Console.WriteLine("skip={0},take={1},resultCount={2}, first-result-id={3}", skip, take, results.Count(), results.First().Id);
        readSoFar += results.Count();
        skip += results.Count();
    }
}


This results in

Code: Select all
skip=0,take=100,resultCount=100, first-result-id=48701
skip=100,take=100,resultCount=100, first-result-id=48801
skip=200,take=100,resultCount=200, first-result-id=48901
skip=400,take=100,resultCount=400, first-result-id=49101
skip=800,take=100,resultCount=800, first-result-id=49501


You can see by the result IDs that it's skipping properly, but it's returning the wrong number of rows. How can I get it to return just the "take" number of rows?

Cheers -- Michael Watson
msw10100
 
Posts: 12
Joined: Thu 20 Sep 2012 04:00

Re: Skip and Take not working as expected

Postby MariiaI » Tue 08 Oct 2013 10:39

Please rewrite this code
Code: Select all
var results = query.Skip(skip).Take(take);

in this way
Code: Select all
var results = query.Skip(skip).Take(take).ToList();
The .ToList() method forces the execution of the query and enumerates all resulting objects to the list of results. With the first variant of the code many additional queries to the database are performed on each iteration.

As for the issue with Take(), we have reproduced it and it is related to the compiled query cache. We will investigate it and inform you about the results as soon as possible.
As a workaround, please try disabling compiled query cache, for example remove (or comment) the next line in the YourDataContext.Designer.cs file:
Code: Select all
public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(YourDataContext));

However, when changing it manually in the *.Designer.cs file, these changes will be lost after making any changes to your model and regenerating the code. To prevent it, you could try modifying the LinqConnect template so that the line
Code: Select all
public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(YourDataContext));
is not generated in the *.Designer.cs file, e.g.:
- open Model Explorer->Templates;
- right-click the "LinqConnect" template and select "Copy to Model Folder" from the shortcut menu;
- remove or comment this line of code (line 85):
Code: Select all
public static CompiledQueryCache compiledQueryCache = CompiledQueryCache.RegisterDataContext(typeof(<#= contextClassName #>));
- save changes and re-generate the code for your model.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Skip and Take not working as expected

Postby MariiaI » Fri 18 Oct 2013 05:10

The bug with the compiled query cache when query contains parameters with the same values is fixed.
New version of LinqConnect 4.3 is available!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=28128.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)