I have a LINQ query that's doing something I wouldn't expect.

I query an order table, prefetching customers. The order data is filtered by store ID and status, and is using paging.

I see the first query for the order data, and it looks fine. It contains the expected where clauses and selects the first 50 records (this is the first page, and I'm asking for 50 records).

The second query is the one that's behaving unexpectedly. It's querying for all customers who are associated with an order that matches the filters I provided. This means that I'm returning thousands of customers.

I would have expected the customer query to be limited to the set of IDs that were in the orders I found via the first query.

Basically, I'd expect this query:

SELECT * FROM customers WHERE customer_id IN
    (SELECT DISTINCT TOP 50 customer_id FROM orders WHERE
     store_id = 1 AND status = 'O')

not:

SELECT * FROM customers WHERE customer_id IN
    (SELECT DISTINCT customer_id FROM orders WHERE
     store_id = 1 AND status = 'O')

Is this expected behavior, is it a bug, or could there be a problem with my code?

Thanks for any help!

Hi Alex,

In order to understand what is going wrong we need you post your code snippet that reproduces such query. Would be helpful the two linq queries and their generated sql. Also be sure you are using the latest runtime library version.

Regards,

Thanks for the quick response.

I'll get you the items you requested when I get to work tomorrow.

By your comments, I assume that it's not normal behavior for a prefetch to not be limited by the records returned in the query for the "parent" entities. Is this correct?

It's correct. When you come back to post, please include these things: - Approximate amount of data you are loading in the main collection. - Your LLBLGen version and Runtime library version (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7722)

C# Code

using (DataAccessAdapter.NoLockAdapter adapter = new DataAccessAdapter.NoLockAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = (from om in metaData.OrderMaster
             select om);

    PrefetchPath2 p = new PrefetchPath2((int)EntityType.OrderMasterEntity);
    p.Add(ContractMasterEntity.PrefetchPathCustMaster);

    q = q.WithPath(p);

    if (orderId != null && orderId.Length > 0) // orderId == collection of order IDs
    {
        q = q.Where(all => orderId.Contains(all.orderId));
    }

    if (orderStatus != null && orderStatus.Length > 0) // orderStatus == collection of statuses to look for
    {
        q = q.Where(all => orderStatus.Contains(all.Status));
    }

    // Paging code
    if (startAt == null)
    {
        q = q.Skip(0);
    }
    else
    {
        q = q.Skip((int)startAt);
    }

    if (count == null)
    {
        q = q.Take(100);
    }
    else
    {
        q = q.Take((int)count);
    }

    if (q != null)
    {
        return ((ILLBLGenProQuery)q).Execute<EntityCollection<OrderMasterEntity>>();
    }
}

First query:

exec sp_executesql N'SELECT DISTINCT TOP 50 [LPA_L1].[OrderNumber], [LPA_L1].[DataSource], [LPA_L1].[CustomerId], [LPA_L1].[StoreId], [LPA_L1].[TypeOfSale], [LPA_L1].[Status], ... FROM [Master].[dbo].[OrderMaster] [LPLA_1] (nolock)) [LPA_L1] WHERE ( ( ( ( ( ( ( ( [LPA_L1].[StoreId] IN (@StoreId1))) AND ( [LPA_L1].[Status] IN (@Status2, @Status3)))))))) ORDER BY [LPA_L1].[CreationDate] DESC',N'@StoreId1 varchar(18),@Status2 varchar(3),@Status3 varchar(3)',@StoreId1='1234',@Status2='OPN',@Status3='CLO'

Second query:

exec sp_executesql N'SELECT [Master].[dbo].[Cust_Master].[Customer_ID] AS [CustomerId], [Master].[dbo].[Cust_Master].[Data_Source] AS [DataSource], [Master].[dbo].[Cust_Master].[First_Name] AS [FirstName] ... FROM [Master].[dbo].[Cust_Master] (nolock) WHERE ( [Master].[dbo].[Cust_Master].[Customer_ID] IN (SELECT [LPA_L1].[CustomerId] FROM (SELECT [LPLA_1].[OrderNumber] AS [OrderNumber], [LPLA_1].[Data_Source] AS [DataSource], [LPLA_1].[Customer_ID] AS [CustomerId], [LPLA_1].[Dealer_ID] AS [StoreId], [LPLA_1].[Type_Of_Sale] AS [TypeOfSale], [LPLA_1].[Status] .... FROM [Master].[dbo].[OrderMaster] [LPLA_1] (nolock)) [LPA_L1] WHERE ( ( ( ( ( ( ( ( ( [LPA_L1].[StoreId] IN (@StoreId1))) AND ( [LPA_L1].[Status] IN (@Status2, @Status3)))))))))) AND [Master].[dbo].[Cust_Master].[Data_Source] IN (SELECT [LPA_L2].[DataSource] FROM (SELECT [LPLA_1].[OrderNumber] AS [OrderNumber], [LPLA_1].[Data_Source] AS [DataSource], [LPLA_1].[Customer_ID] AS [CustomerId], [LPLA_1].[Dealer_ID] AS [StoreId], [LPLA_1].[Type_Of_Sale] AS [TypeOfSale], [LPLA_1].[Status] ... FROM [Master].[dbo].[OrderMaster] [LPLA_1] (nolock)) [LPA_L2] WHERE ( ( ( ( ( ( ( ( ( [LPA_L2].[StoreId] IN (@StoreId4))) AND ( [LPA_L2].[Status] IN (@Status5, @Status6)))))))))))',N'@StoreId1 varchar(18),@Status2 varchar(3),@Status3 varchar(3),@StoreId4 varchar(18),@Status5 varchar(3),@Status6 varchar(3)',@StoreId1='1234',@Status2='OPN',@Status3='CLO',@StoreId4='1234',@Status5='OPN',@Status6='CLO'

Post the LLBLGen Pro version + buildnr - 2.6 Final (April 15, 2009) Runtime library version - 2.6.9.511 Post the template group + .NET version you're using - Adapter, .NET 3.5 Post the database type and version you're using -- SQL Server 2005, running with 2000 DQE compatibility mode

The code above is somewhat obfuscated due to NDA. If it's not clear or helpful, please let me know.

Edit: OrderMaster has a composite primary key on OrderNumber and Data_Source Cust_Master has a composite primary key on Customer_ID and Data_Source OrderMaster has a foreign key to Cust_Master, Customer_ID -> Customer_ID and Data_Source -> Data_Source

Mmm. yep the second query doesn't look good. Please try the following:

 q = q.Where(all => orderId.Contains(all.orderId));

Instead of that IQueryable concatenation, please use PredicateBuilder: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14144&StartAtMessage=0&#78965

q = q.WithPath(p);

Add the WithPath at the end, in the last projection.

Runtime library version - 2.6.9.511

That is not the latest one. Please download the latest runtime library version.

Thanks for the reply.

Here's the code I'm using now:

var predicate = PredicateBuilder.Null<OrderMasterEntity>();

using (DataAccessAdapter.NoLockAdapter adapter = new DataAccessAdapter.NoLockAdapter())
{

    LinqMetaData metaData = new LinqMetaData(adapter);

    predicate = predicate.And(all => storeId.Contains(all.storeId));

    predicate = predicate.And(all => contractStatus.Contains(all.Status));

    var q = metaData.OrderMaster.Where(predicate);

    if (startAt == null)
    {
        q = q.Skip(0);
    }
    else
    {
        q = q.Skip((int)startAt);
    }

    if (count == null)
    {
        q = q.Take(100);
    }
    else
    {
        q = q.Take((int)count);
    }

    PrefetchPath2 p = new PrefetchPath2((int)EntityType.ContractMasterEntity);

    p.Add(ContractMasterEntity.PrefetchPathCustMaster);

    q = q.WithPath(p);

    return ((ILLBLGenProQuery)q).Execute<EntityCollection<ContractMasterEntity>>();

}

I'm also using runtime version 2.6.9.1202.

Unfortunately, I still have the same problem. The second query comes in and pulls 4500+ customers.

Any further suggestions?

Thanks again.

Hi Alex.

I think I completely forgot the obvious thing: in order to use paging in combination with prefetchPath, you should modify yourAdapter.ParameterisedPrefetchPathThreshold. I quote the manual:

Starting with version 1.0.2005.1, LLBLGen Pro supports paging functionality in combination of Prefetch Paths. Due to the complex nature of the prefetch path queries, especially with per-node filters, sort expressions etc., paging wasn't possible, though due to the DataAccessAdapter.ParameterisedPrefetchPathThreshold threshold setting (See earlier in this section: Optimizing Prefetch Paths), paging can be made possible. If you want to utilize paging in combination of prefetch paths, be sure to set DataAccessAdapter.ParameterisedPrefetchPathThreshold to a value larger than the page size you want to use. You can use paging in combination of prefetch path with a page size larger than DataAccessAdapter.ParameterisedPrefetchPathThreshold

The difference with ParameterisedPrefetchPathThreshold is explained in Optimizing Prefetch Paths.

In brief, the prefetch query can't be built using the page information of the main query (TOP X ... rowcount , etc.), so the best option is to use this kind of query:

SELECT OrderID, CustomerID, OrderDate, ...
FROM Orders
WHERE CustomerID IN
( @customer1, @customer2, ... , @customer10)

and that is what ParameterisedPrefetchPathThreshold does. You have to set it to a larger value than your pageSize. The default is 50 and your pageSize is 100, so that could be the problem. Try setting the ParameterisedPrefetchPathThreshold to a value larger than your pageSize (you have to test this to see what is the best value for you). Example:

using (DataAccessAdapter.NoLockAdapter adapter = new DataAccessAdapter.NoLockAdapter())
{
    adapter.ParameterisedPrefetchPathThreshold = 150;
    LinqMetaData metaData = new LinqMetaData(adapter);

    predicate = predicate.And(all => storeId.Contains(all.storeId));

     ...

Another observation:

if (startAt == null)
    {
        q = q.Skip(0);
    }
    else
    {
        q = q.Skip((int)startAt);
    }

    if (count == null)
    {
        q = q.Take(100);
    }
    else
    {
        q = q.Take((int)count);
    }

In that code, I don't know what is the value of startAt. Be aware that the value in "Take" must be a multiple of the value in "Skip". To avoid confusion, the .TakePage(x,m) method is the recommended way (more info...).

Hope helpful

Thanks, daelmo!

This seems to have solved my issue.

One question, though.

As the manual says:

If you want to utilize paging in combination of prefetch paths, be sure to set DataAccessAdapter.ParameterisedPrefetchPathThreshold to a value larger than the page size you want to use.

I tried setting the ParameterisedPrefetchPathThreshold to count + 1, but it still produced the old query.

When I set ParameterisedPrefetchPathThreshold to count * 2, it works as expected.

If the only requirement is that the ParameterisedPrefetchPathThreshold be larger than the page size, why doesn't count + 1 work?

And, will count * 2 always work?

Thanks again!

Mmmm. I have to check the code to answer that. I will back with the answer.

AlexWalker wrote:

Thanks, daelmo!

This seems to have solved my issue.

One question, though.

As the manual says:

If you want to utilize paging in combination of prefetch paths, be sure to set DataAccessAdapter.ParameterisedPrefetchPathThreshold to a value larger than the page size you want to use.

I tried setting the ParameterisedPrefetchPathThreshold to count + 1, but it still produced the old query.

When I set ParameterisedPrefetchPathThreshold to count * 2, it works as expected.

If the only requirement is that the ParameterisedPrefetchPathThreshold be larger than the page size, why doesn't count + 1 work?

And, will count * 2 always work?

Thanks again!

The threshold is for the # of parameters in the filter query. If that # of parameters exceeds the limit, the query will be a subquery. If you have a normal 1 field PK/FK relationship, setting the threshold to 100 will give an IN clause filter if you have 100 or less parents. If you have a 2 field (composite/compound) PK/FK relationship, so 2 fields in the PK, this number is halved: so if you then have 50 parents, you'll get 100 values (number of fields in pk * number of parents), so if you want to have a 100 row page, you then have to set the threshold to 200.

It's not going to hurt much if you set the threshold too high, e.g. to 200 or 300.