LLBLGen Pro 2.5 Final SqlServer 2005 RunTime Version: 2.5.07.0822

I'm have an issue retrieving a collection when I have a one to many relationship. So my DB relations are set up like this

Order (one) --> (many) Order Cart (one) --> (one) Product

So I have a retrieve call where I want to retrieve all the orders that have a status of 'Authorized' and where all the products of the order carts in the order have their IsRetrieved bit set to true. I currently have the following code set up for this.

OrderCollection paidOrders = new OrderCollection();
            
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(OrderEntity.Relations.OrderCartEntityUsingOrderId);
relationsToUse.Add(OrderCartEntity.Relations.ProductEntityUsingProductId);

PrefetchPath path = new PrefetchPath(EntityType.OrderEntity);
path.Add(OrderEntity.PrefetchPathOrderCarts);

PredicateExpression filter = new PredicateExpression();
filter.AddWithAnd(OrderFields.OrderStatus == OrderStatus.Authorized);
filter.AddWithAnd(ProductFields.IsRetrieved == true);

paidOrders.GetMulti(filter, 0, null, relationsToUse, path);

return paidOrders;

The code works if my orders contain products that have IsRetrieved set exclusively to true or false. If all products are set to false the order is not retrieved. If all products are set to true the order is retrieved.

My problem occurs when I have an order that has a mix where some products are set to true and others to false. In this case the order is still retrieved, but I do not want to retrieve the order in this case. I only want to retrieve the orders that have all the product's IsRetrieve bit set to true.

Does anyone know if there is something that I am missing in my code, maybe in the relations or the PredicateExpression, or is this a know issue that I have uncovered?

Thanks

Order (one) --> (many) Order Cart (one) --> (one) Product

Are yousure about the above representation, or is it like: Order (one) --> (many) Order Cart (many) --> (one) Product

Can you post the SQL query that you want to execute?

Are you sure about the above representation, or is it like: Order (one) --> (many) Order Cart (many) --> (one) Product

I may not be correct in the way I represent it in my original post. The way it works is an order can have mutiple order carts and each order cart has exactly one product. So an order would look like this

      /---->OrderCart----->Product

Order ----->OrderCart----->Product ---->OrderCart----->Product

Can you post the SQL query that you want to execute?

Here is the query that is generated from the above code

exec sp_executesql N'SELECT DISTINCT [GCLUnitTest].[dbo].[Order].[OrderID] AS [OrderId], [GCLUnitTest].[dbo].[Order].[UserID] AS [UserId], 
[GCLUnitTest].[dbo].[Order].[OrderNumber], [GCLUnitTest].[dbo].[Order].[InvoiceNumber], [GCLUnitTest].[dbo].[Order].[OrderNotes], 
[GCLUnitTest].[dbo].[Order].[CustomerServiceNotes], [GCLUnitTest].[dbo].[Order].[OrderBillingAddressID] AS [OrderBillingAddressId], 
[GCLUnitTest].[dbo].[Order].[OrderCreditCardID] AS [CreditCardId], [GCLUnitTest].[dbo].[Order].[CreatedDate], 
[GCLUnitTest].[dbo].[Order].[OrderStatus], [GCLUnitTest].[dbo].[Order].[OrderShippingAddressID] AS [OrderShippingAddressId], 
[GCLUnitTest].[dbo].[Order].[PromoCodeID] AS [PromoCodeId] FROM (( [GCLUnitTest].[dbo].[Order]  INNER JOIN [GCLUnitTest].[dbo].[OrderCart]  ON  
[GCLUnitTest].[dbo].[Order].[OrderID]=[GCLUnitTest].[dbo].[OrderCart].[OrderID]) INNER JOIN [GCLUnitTest].[dbo].[Product]  ON  
[GCLUnitTest].[dbo].[Product].[ProductID]=[GCLUnitTest].[dbo].[OrderCart].[ProductID]) WHERE ( ( [GCLUnitTest].[dbo].[Order].[OrderStatus] = 
@OrderStatus1 AND [GCLUnitTest].[dbo].[Product].[IsRetrieved] = @IsRetrieved2))',N'@OrderStatus1 smallint,@IsRetrieved2 
bit',@OrderStatus1=2,@IsRetrieved2=1

I'm not asking about the generated SQL query, instead I'm asking about the query the query that you want to execute.