Skip and Take not working as expected

[msw10100]

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

[MariiaI]

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]

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.