Hi all,
I am using LLBLGen 2.6 using the adapter model. I have a problem with the following queries (against SQL Server 2008 Express)
var customers = (from customer in meta.Customer.WithPath(p => p.Prefetch<OrderEntity>(y => y.Order))
join order in meta.Order on customer.Id equals order.CustomerId
select customer).ToList();
This is a simple join statement for customers with orders.
The problem is that the prefecth returns no results.
So customer.Order always return zero orders. I am quite puzzled by this and at my wits ends. Any suggestion what could be the problem with this simple query?
I checked using a profiler and this is the code generated
SELECT DISTINCT [LPA_L1].[Id], [LPA_L1].[Name], [LPA_L1].[CustomerTypeId], [LPA_L1].[IsEnabled], [LPA_L1].[DateCreated], [LPA_L1].[CreatedBy], [LPA_L1].[LastModified], [LPA_L1].[LastModifiedBy] FROM ( (SELECT [SpeedServices].[dbo].[Customer].[ID] AS [Id], [SpeedServices].[dbo].[Customer].[Name], [SpeedServices].[dbo].[Customer].[CustomerType] AS [CustomerTypeId], [SpeedServices].[dbo].[Customer].[IsEnabled], [SpeedServices].[dbo].[Customer].[DateCreated], [SpeedServices].[dbo].[Customer].[CreatedBy], [SpeedServices].[dbo].[Customer].[LastModified], [SpeedServices].[dbo].[Customer].[LastModifiedBy] FROM [SpeedServices].[dbo].[Customer] ) [LPA_L1] INNER JOIN [SpeedServices].[dbo].[Order] [LPA_L2] ON [LPA_L1].[Id] = [LPA_L2].[CustomerID])
The reverse though works just fine
var ordersWithCustomer = Query.Get<List<OrderEntity>>(meta =>
{
var orders = (from order in meta.Order.WithPath(p => p.Prefetch<CustomerEntity>(y => y.Customer))
join customer in meta.Customer on order.CustomerId equals customer.Id
select order).ToList();
return orders;
});
Prefetch also works fine if I don't join them
var customersWithOrders = Query.Get<List<CustomerEntity>>(meta =>
{
var customers = (from customer in meta.Customer.WithPath(p => p.Prefetch<OrderEntity>(y => y.Order))
select customer).ToList();
return customers;
});
With above code, customer.Order returns results.
The following code also works fine
var customersWithOrders = Query.Get<List<CustomerEntity>>(meta =>
{
var customers = (from customer in meta.Customer.WithPath(p => p.Prefetch<OrderEntity>(y => y.Order))
where
(
from order in meta.Order
select order.CustomerId).Contains(customer.Id)
select customer).ToList();
return customers;
});
With above code, customer.Order returns results.
So I wonder if I encounter a limitation in the prefetch capabilities or is there something more?
