Hello,

I've got a Stored Procedure and i'm trying to convert it to a Linq to LLBLGen query. The query in Linq to LLBGen works, but when I trace the query which is send to sql server it is far from perfect.

This is the Stored Procedure:

ALTER PROCEDURE [dbo].[spDIGI_GetAllUmbracoProducts] 
    -- Add the parameters for the stored procedure.
    @searchText nvarchar(255),
    @startRowIndex int,
    @maximumRows int,
    @sortExpression nvarchar(255)
AS
BEGIN
    
    SET @startRowIndex = @startRowIndex + 1
    SET @searchText = '%' + @searchText + '%'

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON; 

    -- This is the query which will fetch all the UmbracoProducts.
    -- This query also supports paging and sorting.
    WITH UmbracoOverview As 
    (
        SELECT      ROW_NUMBER() OVER(
            ORDER BY 
            CASE 
                WHEN @sortExpression = 'productName' THEN umbracoProduct.productName 
                WHEN @sortExpression = 'productCode' THEN umbracoProduct.productCode            
            END ASC,
            CASE 
                WHEN @sortExpression = 'productName DESC' THEN umbracoProduct.productName 
                WHEN @sortExpression = 'productCode DESC' THEN umbracoProduct.productCode           
            END DESC ) 
            AS row_num, umbracoProduct.umbracoProductId, umbracoProduct.productName, umbracoProduct.productCode
        FROM         umbracoProduct INNER JOIN
                      product ON umbracoProduct.umbracoProductId = product.umbracoProductId
        WHERE       (umbracoProduct.productName LIKE @searchText 
        OR          umbracoProduct.productCode LIKE @searchText
        OR          product.code LIKE @searchText
        OR          product.description LIKE @searchText
        OR          product.descriptionLong LIKE @searchText
        OR          product.unitCode LIKE @searchText)
    )
    
    SELECT      UmbracoOverview.UmbracoProductId, UmbracoOverview.productName, UmbracoOverview.productCode 
    FROM        UmbracoOverview 
    WHERE       (row_num >= @startRowIndex 
    AND         row_num < (@startRowIndex + @maximumRows))

    
    -- This query will count all the UmbracoProducts.
    -- This query is used for paging inside ASP.NET.
    SELECT      COUNT (umbracoProduct.umbracoProductId) AS CountNumber
    FROM        umbracoProduct INNER JOIN
                    product ON umbracoProduct.umbracoProductId = product.umbracoProductId
    WHERE       (umbracoProduct.productName LIKE @searchText 
    OR          umbracoProduct.productCode LIKE @searchText
    OR          product.code LIKE @searchText
    OR          product.description LIKE @searchText
    OR          product.descriptionLong LIKE @searchText
    OR          product.unitCode LIKE @searchText)
END

This is my Linq to LLBLGen query:

using System.Linq.Dynamic;

            var q = (
                        from up in MetaData.UmbracoProduct
                        join p in MetaData.Product on up.UmbracoProductId equals p.UmbracoProductId
                        where up.ProductCode.Contains(searchText) ||
                            up.ProductName.Contains(searchText) ||
                            p.Code.Contains(searchText) ||
                            p.Description.Contains(searchText) ||
                            p.DescriptionLong.Contains(searchText) ||
                            p.UnitCode.Contains(searchText)
                        select new UmbracoProductOverview
                        {
                            UmbracoProductId = up.UmbracoProductId,
                            ProductName = up.ProductName,
                            ProductCode = up.ProductCode
                        }
                    ).OrderBy(sortExpression);

    //Save the count in HttpContext.Current.Items. This value will only be saved during 1 single HTTP request.
    HttpContext.Current.Items["AllProductsCount"] = q.Count();

    //Returns the results paged.
    return q.Skip(startRowIndex).Take(maximumRows).ToList<UmbracoProductOverview>();

This is my Initial expression to process:

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[Eurofysica.DB.EntityClasses.UmbracoProductEntity]).Join( value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[ Eurofysica.DB.EntityClasses.ProductEntity]), up => up.UmbracoProductId, p => p.UmbracoProductId, (up, p) => new <>f__AnonymousType0`2(up = up, p = p)).Where(<>h__TransparentIdentifier0 => (((((<>h__TransparentIdentifier0.up.ProductCode.Contains(value(Eurofysica.BusinessLogic.BLL.Controllers.UmbracoProductController+<>c__DisplayClass1).searchText) || <>h__TransparentIdentifier0.up.ProductName.Contains(value(Eurofysica.BusinessLogic.BLL.Controllers.UmbracoProductController+<>c__DisplayClass1).searchText)) || <>h__TransparentIdentifier0.p.Code.Contains(value(Eurofysica.BusinessLogic.BLL.Controllers.UmbracoProductController+<>c__DisplayClass1).searchText)) || <>h__TransparentIdentifier0.p.Description.Contains(value(Eurofysica.BusinessLogic.BLL.Controllers.UmbracoProductController+<>c__DisplayClass1).searchText)) || <>h__TransparentIdentifier0.p.DescriptionLong.Contains(value(Eurofysica.BusinessLogic.BLL.Controllers.UmbracoProductController+<>c__DisplayClass1).searchText)) || <>h__TransparentIdentifier0.p.UnitCode.Contains(value(Eurofysica.BusinessLogic.BLL.Controllers.UmbracoProductController+<>c__DisplayClass1).searchText))).Select(<>h__TransparentIdentifier0 => new UmbracoProductOverview() {UmbracoProductId = <>h__TransparentIdentifier0.up.UmbracoProductId, ProductName = <>h__TransparentIdentifier0.up.ProductName, ProductCode = <>h__TransparentIdentifier0.up.ProductCode}).OrderBy( => .ProductName).Count()

Now this is how the queries look like that are send to sql server (Code tag didn't work so I used quote):

Select query:

Query: SELECT [LPA_L2].[umbracoProductId] AS [UmbracoProductId], [LPA_L2].[productName] AS [ProductName], [LPA_L2].[productCode] AS [ProductCode] FROM ( [eurofysica].[dbo].[umbracoProduct] [LPA_L2] INNER JOIN [eurofysica].[dbo].[product] [LPA_L3] ON [LPA_L2].[umbracoProductId] = [LPA_L3].[umbracoProductId]) WHERE ( ( ( ( ( ( ( ( [LPA_L2].[productCode] LIKE @ProductCode1) OR ( [LPA_L2].[productName] LIKE @ProductName2)) OR ( [LPA_L3].[code] LIKE @Code3)) OR ( [LPA_L3].[description] LIKE @Description4)) OR ( [LPA_L3].[descriptionLong] LIKE @DescriptionLong5)) OR ( [LPA_L3].[unitCode] LIKE @UnitCode6)))) Parameter: @ProductCode1 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @ProductName2 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @Code3 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @Description4 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @DescriptionLong5 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @UnitCode6 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%".

Count query:

Query: SELECT TOP 1 COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L2].[umbracoProductId] AS [UmbracoProductId], [LPA_L2].[productName] AS [ProductName], [LPA_L2].[productCode] AS [ProductCode] FROM ( [eurofysica].[dbo].[umbracoProduct] [LPA_L2] INNER JOIN [eurofysica].[dbo].[product] [LPA_L3] ON [LPA_L2].[umbracoProductId] = [LPA_L3].[umbracoProductId]) WHERE ( ( ( ( ( ( ( ( [LPA_L2].[productCode] LIKE @ProductCode1) OR ( [LPA_L2].[productName] LIKE @ProductName2)) OR ( [LPA_L3].[code] LIKE @Code3)) OR ( [LPA_L3].[description] LIKE @Description4)) OR ( [LPA_L3].[descriptionLong] LIKE @DescriptionLong5)) OR ( [LPA_L3].[unitCode] LIKE @UnitCode6))))) [LPA_L1] Parameter: @ProductCode1 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @ProductName2 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @Code3 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @Description4 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @DescriptionLong5 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%". Parameter: @UnitCode6 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "%%".

As you can see no sorting or paging is done (like in my Stored Procedure). This is probably done inside the code after all the results are fetched. This costs a lot of performance! Does anybody know how I can convert my Stored Procedure to Linq to LLBLGen the proper way?

This is probably done inside the code after all the results are fetched. This costs a lot of performance! Does anybody know how I can convert my Stored Procedure to Linq to LLBLGen the proper way?

If you get back the page that you needed, then I don't think any performance penalty has been paid.

Please check the following threads for more details: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16756 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13411

Walaa wrote:

If you get back the page that you needed, then I don't think any performance penalty has been paid.

Are you sure about that? My query fetches all the rows from sql server (which are over 3000) and then sorts and pages them. If only the 20 results which I need are fetched (as in my stored procedure) this should be a lot faster right?

These are the results I got from tracing:

Linq to LLBLgen query:

• aspx.page End Render 0,0309190832913122 0,003680

Stored Procedure:

• aspx.page End Render 0,0152554305086261 0,003720

As you can see my Stored Procedure is 2 times faster.

You create a linq query which results in a completely differnt sql query (so either rewrite the linq query or write a native llblgen pro query).

Walaa wrote:

You create a linq query which results in a completely differnt sql query (so either rewrite the linq query or write a native llblgen pro query).

I know the sql query completely different. I just don't know why it's different. I don't think this is an Linq to LLBLGen problem, but more a query syntax problem. Does anybody know what I'm doing wrong?

Will look into it.

Otis wrote:

Will look into it.

Thank you!

I've found the following information about TakePage:

http://www.devexpress.com/Support/Center/p/Q98554.aspx

and

http://stackoverflow.com/questions/1887513/linq-to-llblgen-query-problem

Might using the TakePage expression solve my problem and where can I find this expression?

TakePage is our own extension method for paging. (funny that you have to learn about that method from 3rd party sources, while it's right there in the manual: http://www.llblgen.com/documentation/2.6/hh_goto.htm#Using%20the%20generated%20code/Linq/gencode_linq_generalusage.htm%23Paging )

Specifying Skip + Take will work but only if Skip has a value which is a multiply of the page size (the # given with Take), as LLBLGen pro has no skipping feature in the fetch logic.

So my thought is that your Skip + Take combo isn't valid and it's therefore ignored, please try TakePage and try again. Also, be aware that if the resultset contains an Image/NText etc. field it's not paging on the server as you have included a join, which could lead to duplicates.