I'm struggling with this one, have checked all the examples on Prefetch but can't seem to have it working.

Have the following tables:

---------------------- * 1 --------------- 1 * -------------- | CustomerNote | <---------> | Customer | <--------> | Package |

Customer can have many CustomerNotes. Customer can have many Packages.

What I want to do is run a LINQ-query of a Customer and gets its related CustomerNotes and Packages at the same time. Easy right?

I try the following:

var customers = from c in linqMetaData.Customer
                    .WithPath<CustomerEntity>(cusPath => cusPath
                        .Prefetch<CustomernoteEntity>(cust => cust.Customernote))
                        .WithPath<CustomerEntity>(cusPath => cusPath
                            .Prefetch<PackageEntity>(cust => cust.Package))
                select c;

The generated SQL that is produced are the following three:

Generated Sql query: 
    Query: SELECT DISTINCT `LPLA_1`.`user_id` AS `UserId`, `LPLA_1`.`status` AS `Status`, `LPLA_1`.`movedate` AS `Movedate`, `LPLA_1`.`server_id` AS `ServerId`, `LPLA_1`.`name` AS `Name`, `LPLA_1`.`company` AS `Company`, `LPLA_1`.`address1` AS `Address1`, `LPLA_1`.`address2` AS `Address2`, `LPLA_1`.`zip` AS `Zip`, `LPLA_1`.`city` AS `City`, `LPLA_1`.`country` AS `Country`, `LPLA_1`.`phone` AS `Phone`, `LPLA_1`.`mobile` AS `Mobile`, `LPLA_1`.`fax` AS `Fax`, `LPLA_1`.`email` AS `Email`, `LPLA_1`.`identificationnumber` AS `Identificationnumber`, `LPLA_1`.`billingperiod` AS `Billingperiod`, `LPLA_1`.`statuscontact` AS `Statuscontact`, `LPLA_1`.`movesent` AS `Movesent`, `LPLA_1`.`movesenttime` AS `Movesenttime`, `LPLA_1`.`movedonesent` AS `Movedonesent`, `LPLA_1`.`movedonesenttime` AS `Movedonesenttime`, `LPLA_1`.`movewelcomesent` AS `Movewelcomesent`, `LPLA_1`.`movewelcomesenttime` AS `Movewelcomesenttime`, `LPLA_1`.`hasweb` AS `Hasweb`, `LPLA_1`.`haseshop` AS `Haseshop`, `LPLA_1`.`hasserver` AS `Hasserver`, `LPLA_1`.`transferweb` AS `Transferweb`, `LPLA_1`.`transferdb` AS `Transferdb`, `LPLA_1`.`transfermail` AS `Transfermail`, `LPLA_1`.`transferdomain` AS `Transferdomain`, `LPLA_1`.`databasechanges` AS `Databasechanges`, `LPLA_1`.`dnschanges` AS `Dnschanges`, `LPLA_1`.`selfexternal` AS `Selfexternal`, `LPLA_1`.`acceptseregistrar` AS `Acceptseregistrar`, `LPLA_1`.`acceptterms` AS `Acceptterms`, `LPLA_1`.`orderdate` AS `Orderdate`, `LPLA_1`.`mailsent` AS `Mailsent`, `LPLA_1`.`username` AS `Username`, `LPLA_1`.`password` AS `Password`, `LPLA_1`.`ConvertedToCustomerDb`, `LPLA_1`.`TimeStampConvertedToCustomerDb`, `LPLA_1`.`CusIdInCustomerDb` FROM `customer` `LPLA_1`


Generated Sql query: 
    Query: SELECT `LPLA_1`.`user_id` AS `UserId` FROM `customer` `LPLA_1`


Generated Sql query: 
    Query: SELECT DISTINCT `package`.`package_id` AS `PackageId`, `package`.`user_id` AS `UserId`, `package`.`offer_id` AS `OfferId`, `package`.`price` AS `Price`, `package`.`checked` AS `Checked`, `package`.`mailStatus` AS `MailStatus`, `package`.`mailStatusTime` AS `MailStatusTime`, `package`.`dnsStatus` AS `DnsStatus`, `package`.`dnsStatusTime` AS `DnsStatusTime`, `package`.`username` AS `Username`, `package`.`correctprice` AS `Correctprice`, `package`.`fromDbIndex` AS `FromDbIndex`, `package`.`toDbIndex` AS `ToDbIndex`, `package`.`mysqlPass` AS `MysqlPass`, `package`.`websize` AS `Websize`, `package`.`ballouPrice` AS `BallouPrice` FROM `package` WHERE ( `package`.`user_id` IN (SELECT `LPLA_1`.`user_id` AS `UserId` FROM `customer` `LPLA_1`))

I have also tried the following:

var customers = from c in linqMetaData.Customer
                    .WithPath<CustomerEntity>(cusPath => cusPath
                        .Prefetch<CustomernoteEntity>(cust => cust.Customernote)
                        .Prefetch<PackageEntity>(cust => cust.Package))
                select c;

Which produces the following five (5!) SQL queries:

Generated Sql query: 
    Query: SELECT DISTINCT `LPLA_1`.`user_id` AS `UserId`, `LPLA_1`.`status` AS `Status`, `LPLA_1`.`movedate` AS `Movedate`, `LPLA_1`.`server_id` AS `ServerId`, `LPLA_1`.`name` AS `Name`, `LPLA_1`.`company` AS `Company`, `LPLA_1`.`address1` AS `Address1`, `LPLA_1`.`address2` AS `Address2`, `LPLA_1`.`zip` AS `Zip`, `LPLA_1`.`city` AS `City`, `LPLA_1`.`country` AS `Country`, `LPLA_1`.`phone` AS `Phone`, `LPLA_1`.`mobile` AS `Mobile`, `LPLA_1`.`fax` AS `Fax`, `LPLA_1`.`email` AS `Email`, `LPLA_1`.`identificationnumber` AS `Identificationnumber`, `LPLA_1`.`billingperiod` AS `Billingperiod`, `LPLA_1`.`statuscontact` AS `Statuscontact`, `LPLA_1`.`movesent` AS `Movesent`, `LPLA_1`.`movesenttime` AS `Movesenttime`, `LPLA_1`.`movedonesent` AS `Movedonesent`, `LPLA_1`.`movedonesenttime` AS `Movedonesenttime`, `LPLA_1`.`movewelcomesent` AS `Movewelcomesent`, `LPLA_1`.`movewelcomesenttime` AS `Movewelcomesenttime`, `LPLA_1`.`hasweb` AS `Hasweb`, `LPLA_1`.`haseshop` AS `Haseshop`, `LPLA_1`.`hasserver` AS `Hasserver`, `LPLA_1`.`transferweb` AS `Transferweb`, `LPLA_1`.`transferdb` AS `Transferdb`, `LPLA_1`.`transfermail` AS `Transfermail`, `LPLA_1`.`transferdomain` AS `Transferdomain`, `LPLA_1`.`databasechanges` AS `Databasechanges`, `LPLA_1`.`dnschanges` AS `Dnschanges`, `LPLA_1`.`selfexternal` AS `Selfexternal`, `LPLA_1`.`acceptseregistrar` AS `Acceptseregistrar`, `LPLA_1`.`acceptterms` AS `Acceptterms`, `LPLA_1`.`orderdate` AS `Orderdate`, `LPLA_1`.`mailsent` AS `Mailsent`, `LPLA_1`.`username` AS `Username`, `LPLA_1`.`password` AS `Password`, `LPLA_1`.`ConvertedToCustomerDb`, `LPLA_1`.`TimeStampConvertedToCustomerDb`, `LPLA_1`.`CusIdInCustomerDb` FROM `customer` `LPLA_1`


Generated Sql query: 
    Query: SELECT `LPLA_1`.`user_id` AS `UserId` FROM `customer` `LPLA_1`


Generated Sql query: 
    Query: SELECT DISTINCT `customernote`.`id` AS `Id`, `customernote`.`user_id` AS `UserId`, `customernote`.`note` AS `Note`, `customernote`.`timestamp` AS `Timestamp`, `customernote`.`ticketType` AS `TicketType` FROM `customernote` WHERE ( `customernote`.`user_id` IN (SELECT `LPLA_1`.`user_id` AS `UserId` FROM `customer` `LPLA_1`))


Generated Sql query: 
    Query: SELECT `LPLA_1`.`user_id` AS `UserId` FROM `customer` `LPLA_1`


Generated Sql query: 
    Query: SELECT DISTINCT `package`.`package_id` AS `PackageId`, `package`.`user_id` AS `UserId`, `package`.`offer_id` AS `OfferId`, `package`.`price` AS `Price`, `package`.`checked` AS `Checked`, `package`.`mailStatus` AS `MailStatus`, `package`.`mailStatusTime` AS `MailStatusTime`, `package`.`dnsStatus` AS `DnsStatus`, `package`.`dnsStatusTime` AS `DnsStatusTime`, `package`.`username` AS `Username`, `package`.`correctprice` AS `Correctprice`, `package`.`fromDbIndex` AS `FromDbIndex`, `package`.`toDbIndex` AS `ToDbIndex`, `package`.`mysqlPass` AS `MysqlPass`, `package`.`websize` AS `Websize`, `package`.`ballouPrice` AS `BallouPrice` FROM `package` WHERE ( `package`.`user_id` IN (SELECT `LPLA_1`.`user_id` AS `UserId` FROM `customer` `LPLA_1`))

No matter the difference in produced SQL queries (3 vs 5) the end result is still the same. They both produce the same result. And the result is that only the last prefetch will work. This means that in the above examples what I will get back is a Customer with its Packages (because thats the last Prefetch). No CustomerNote whatsoever.

If I go and change the order of the prefetches to do Package before CustomerNote then the result will once again contain only Customer and the related objects from the last Prefetch.

Is this the way it should work? Have I missed out on something?

I want to do this in LINQ if possible.

Please, anyone?

FYI I'm running mySQL 5 with this example. Think it shouldn't matter but anyway.

Apply the WithPath clause at the end:

var customers = (from c in linqMetaData.Customer select c)
                    .WithPath<CustomerEntity>(cusPath => cusPath
                        .Prefetch<CustomernoteEntity>(cust => cust.Customernote)
                        .Prefetch<PackageEntity>(cust => cust.Package));
                

Holy cow, it works. Having struggled for a day with this problem I feel embarrased how easy the solution is.

David, thanks a million. Muchas gracias!