Hi, When I execute the following LINQ query:

DataAccessAdapter da = new DataAccessAdapter();
LinqMetaData md = new LinqMetaData(da);

var query = (from cat in md.Category
                        select cat).Take(3);

LLBLGen generates the following SQL query:

SELECT [LPLA_1].[CategoryID] AS [CategoryId], 
             [LPLA_1].[CategoryName], 
             [LPLA_1].[Description], 
             [LPLA_1].[Picture] 
FROM    [Northwind].[dbo].[Categories] [LPLA_1] 

There is no paging in the generated SQL query. Is there any way to enable paging at DB for LINQ queries?

Regards, Farzad Badili

Please use TakePage(pageNumber, pageSize).

Check this link

Hi, Thanks for your fast support

When I changed the code to this:

            DataAccessAdapter da = new DataAccessAdapter();
            LinqMetaData md = new LinqMetaData(da);


            var query = (from cat in md.Category
                         orderby cat.CategoryId
                         select cat).TakePage(1,3);

My application didn't compile and I got this error message:

'System.Linq.IOrderedQueryable<Northwind.ORM.EntityClasses.CategoryEntity>' does not contain a definition for 'TakePage' and no extension method 'TakePage' accepting a first argument of type 'System.Linq.IOrderedQueryable<Northwind.ORM.EntityClasses.CategoryEntity>' could be found (are you missing a using directive or an assembly reference?) D:\My Documents\Visual Studio 2008\Projects\Northwind LLBL\WinSample\Form1.cs   332 38  WinSample

What should I do for codes like this one:

            EntityCollection<CategoryEntity> q2 = new EntityCollection<CategoryEntity>();

            da.FetchEntityCollection(q2, null, 0, null, 1, 3);

Regards, Farzad Badili

Please add a using statement to the SD.LLBLGen.Pro.LinqSupportClasses as the extension method is defined there.

TakePage() method is working now, but it doesn't make any difference in the generated SQL command. My code:

            DataAccessAdapter da = new DataAccessAdapter();
            LinqMetaData md = new LinqMetaData(da);


            var query = (from cat in md.Category
                         orderby cat.CategoryId
                         select cat).TakePage(2, 3);

Generated SQL Query:

SELECT [LPLA_1].[CategoryID] AS [CategoryId], 
             [LPLA_1].[CategoryName], 
             [LPLA_1].[Description], 
             [LPLA_1].[Picture] 
FROM    [Northwind].[dbo].[Categories] [LPLA_1]  
ORDER BY [LPLA_1].[CategoryID] ASC

Same thing happens for "DataAccessAdapter.FetchEntityCollection()"

What should I do about this one:

            DataAccessAdapter da = new DataAccessAdapter();

            EntityCollection<CategoryEntity> q2 = new EntityCollection<CategoryEntity>();
            
            da.FetchEntityCollection(q2, null, 0, null, 1, 3);

Which database are you using, which version? Which trace switches are you using? Are you setting the SqlServerDQECompatibilityLevel?

I'm not sure if the code you have posted is the complete real one or just a sample. But would you please check the following thread: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=12525

(EDIT) Oh, I see you have an image field in the select list, most probably that's why DISTINCT can't be applied so is the server side paging. Please check this thread: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14043

I'm using SQL Server 2005 and SQL Server Profiler.

I read those threads you mentioned. When I perform this test on a different table without image field, the paging worked correctly.

Does LLBLGen perform paging in the client side if a table contains XML columns? It seems that the same thing happens for the XML fields too.

It depends on the query. You should use the latest ormsupportclasses dll available which contains an optimization for 'DISTINCT' emitting and the necessity for it. If DISTINCT is required (e.g. when the query is likely to produce duplicates) and DISTINCT can't be emitted (because the resultset contains a distinct violating type, like image, ntext etc.) paging is performed on the client. THis is done through skipping rows on the datareader. The reason this is done is because if duplicates aren't removed, it can be the first page for example contains 10 duplicate rows, which will result in 1 entity (as duplicates are skipped).

This will cause a serious problem for our project. It's about 8 months that we are working on an ERP project. We have more than one hundred tables now. Most tables contain XML fields for multilingual texts. Each table uses just one field for the primary key.

Is there any possible solution for this problem?

Thanks for your support and Happy new year Farzad Badili

fdb wrote:

This will cause a serious problem for our project. It's about 8 months that we are working on an ERP project. We have more than one hundred tables now. Most tables contain XML fields for multilingual texts. Each table uses just one field for the primary key.

Is there any possible solution for this problem?

Thanks for your support and Happy new year Farzad Badili

The solution is that you should use the latest ormsupportclasses dll from the customer area: that one will not require distinct on queries without joins and a limit if you're fetching entities (as there won't be duplicates).

With duplicates in the resultset, paging is useless, so if you want paging on the server and fetch the xml fields, and use a query which results (potentially) in duplicates in the resultset, there's no other than to exclude the xml fields, otherwise you won't get server-side paging, as you run the risk of retrieving a page which isn't the page you requested. the Xml type can't be used with distinct in the query, sqlserver will give an error because of that.

The query in your startpost should page on the server with the latest ormsupportclasses dll as it will no longer require distinct to be emitted. Could you please try the latest ormsupportclasses dll (runtime libraries) from the customer area? (please verify you're using the latest build by checking the build nr as described in this thread http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717)