Hello,

I want to prefetch entities several levels deep. Here's my code:

metaData.Customer.WithPath(p => p.Prefetch<PersonEntity>(customer => customer.Person).SubPath(personSubPath => personSubPath.Prefetch<ContactEntity>(person => person.Contact).SubPath(contactSubPath => contactSubPath.Prefetch(contact => contact.Address))));

What am I doing wrong here?

Don't know - it's kinda hard to tell if you don't tell us the result you were expecting, and the result you actually got...

What SQL do you see being generated when the query is run ? Do any of you child collections come back populated ?

Matt

Well, when I wrote this post, I thougt that this code doesn't compile, but I was wrong, my other code didn't. The SQL generated for this query is:

SELECT [DB].[dbo].[Customer].[CustomerNumber], [DB].[dbo].[Customer].[CustomerId] AS [Id], [DB].[dbo].[Customer].[PersonId] FROM [DB].[dbo].[Customer]

It doesn't have any of the prefetch information that I specified and of course the Customer.Person property gets a null value.

Does it work if you simplify the query just to fetch the first level (Person ?)

Please can you post the complete code that you are using, and the complete generated SQL in the above case ?

Matt

Does it work if you simplify the query just to fetch the first level (Person ?)

No, it doesn't.

Please can you post the complete code that you are using, and the complete generated SQL in the above case?

I've attached a solution, database schema file, and an llblgenproj file.

PrefetchPaths are executed as separate queries.

e.g.

            var metaData = new LinqMetaData();
            var q = (from c in metaData.Customers
                     select c).WithPath(p => p.Prefetch<OrdersEntity>(c => c.Orders).SubPath(x => x.Prefetch<OrderDetailsEntity>(o => o.OrderDetails)));

Generates the following:

Generated Sql query: 
    Query: SELECT [LPLA_1].[CustomerID] AS [CustomerId] FROM [Northwind].[dbo].[Customers] [LPLA_1] 

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [Northwind].[dbo].[Orders].[OrderID] AS [OrderId] FROM [Northwind].[dbo].[Orders]  WHERE ( ( ( [Northwind].[dbo].[Orders].[CustomerID] IN (SELECT [LPLA_1].[CustomerID] AS [CustomerId] FROM [Northwind].[dbo].[Customers] [LPLA_1] ))))

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [Northwind].[dbo].[Order Details].[OrderID] AS [OrderId], [Northwind].[dbo].[Order Details].[ProductID] AS [ProductId], [Northwind].[dbo].[Order Details].[UnitPrice], [Northwind].[dbo].[Order Details].[Quantity], [Northwind].[dbo].[Order Details].[Discount] FROM [Northwind].[dbo].[Order Details]  WHERE ( ( [Northwind].[dbo].[Order Details].[OrderID] IN (SELECT [Northwind].[dbo].[Orders].[OrderID] AS [OrderId] FROM [Northwind].[dbo].[Orders]  WHERE ( ( ( [Northwind].[dbo].[Orders].[CustomerID] IN (SELECT [LPLA_1].[CustomerID] AS [CustomerId] FROM [Northwind].[dbo].[Customers] [LPLA_1] )))))))

PrefetchPaths are executed as separate queries.

I can see only one statement generated for the query in Visual Studio output window. See the attached solution.

The database you have attached have no data.

If a query doesn't return results, no further prefetchPaths are processed.

Walaa wrote:

The database you have attached have no data.

If a query doesn't return results, no further prefetchPaths are processed.

Indeed, you are right. When data Is present, the queries are generated. Thanks.