Loading related objects without multiple database calls

Question

I'm just learning LINQ to SQL and have run into the following obstacle.

I have a the following 3 tables:

1. PC (PCGUID, ParentPCGUID, ModelName, RetailerGUID)
2. Offer (OfferGUID, ParentGUID, Title)
3. Retailer (RetailerGUID, Name)

With the following relationships:

1. PC 1:N Offer
2. Retailer 1:N PC
3. Retailer 1:N Offer
4. PC 1:N PC (self referencing)

I need to grab records from PC with only 1 record from PC.Offer (with specific where clauses) and only 1 record from PC.Retailer.Offer (with specific where clauses) and, hopefully, with only one hit on the database.

I have the follow query so far (I've tried about a hundred so far), but it hits the db multiple times.

    var q = from pc in PCs
        where pc.PCGUID == guid || pc.ParentPCGUID == guid
        select new { 
            PC = pc,
            PCOffers = pc.Offers.FirstOrDefault(),
            RetailOffers = pc.Retailer.Offers.FirstOrDefault()
        };

If I bring only one property of the offer table like so:

    var q = from pc in PCs
        where pc.PCGUID == guid || pc.ParentPCGUID == guid
        select new { 
            PC = pc,
            PCOffers = pc.Offers.FirstOrDefault().Title,
            RetailOffers = pc.Retailer.Offers.FirstOrDefault().Title
        };

It works just fine and only hits the db once, but I need the whole offer object. Any help would be greatly appreciated.

Thanks

Answer 1

You might want to try something like this, but I'm not sure it will help.

YourDataContext db = new YourDataContext();
db.DeferredLoadingEnabled = false;

DataLoadOptions loadOptions = new DataLoadOptions(); 
loadOptions.LoadWith<PC>(p => p.Offers); 
loadOptions.LoadWith<PC>(p => p.Retailer); 
loadOptions.LoadWith<Retailer>(r => r.Offers); 

db.LoadOptions = loadOptions;