Eager-loading using LINQ to SQL with Include()

Question

I have spent 2 days bashing my head against this problem, and I can't seem to crack it (the problem that is). The same code was working fine until I added database relationships, and I have since read a lot about lazy-loading.

I have two database tables with a 1:1 relationship between them. PromoCode table tracks codes, and has a PK column named id. CustomerPromo table has a column PromoId which is linked to the PromoCode table id. These two tables have no other relationships. I generated all this in SQL Server Management Studio, then generated the model from the database.

To make matters slightly more complicated, I'm doing this inside a WCF data service, but I don't believe that should make a difference (it worked before database relationships were added). After enabling logging, I always get an Exception in the log file with text:

DataContext accessed after Dispose.

My function currently returns all entries from the table:

using (MsSqlDataContext db = new MsSqlDataContext())
{
    // This causes issues with lazy-loading
    return db.PromoCodes.ToArray();
}

I have read numerous articles/pages/answers and they all say to use the .Include() method. But this doesn't work for me:

return db.PromoCodes.Include(x => x.CustomerPromos).ToArray();

I've tried the "magic string" version as well:

return db.PromoCodes.Include("CustomerPromos").ToArray();

The only code I've managed to get to work is this:

PromoCode[] toReturn = db.PromoCodes.ToArray();

foreach (var p in toReturn)
    p.CustomerPromos.Load();

return toReturn;

I've tried added a .Where() criteria to the query, I've tried .Select(), I've tried moving the .Include() after the .Where() (this answer says to do it last, but I think that's only due to nested queries). I've read about scenarios where .Include() will silently fail, and after all this I'm no closer.

What am I missing? Syntax problem? Logic problem? Once I get this "simple" case working, I also need to have nested Includes (i.e. if CustomerPromo table had a relationship to Customer).

Edit
Including all relevant code. The rest is either LINQ to SQL, or WCF Data Services configuration. This is all there is:

[WebGet]
[OperationContract]
public PromoCode[] Test()
{
    using (MsSqlDataContext db = new MsSqlDataContext())
    {
        return db.PromoCodes.Include(x => x.CustomerPromos).ToArray();
    }
}

If I call that through a browser directly (e.g. http://<address>:<port>/DataService.svc/Test) I get a reset connection message and have to look up the WCF logs to find out "DataContext accessed after Dispose.". If I make the same query through an AJAX call in a webpage I get an AJAX error with status error (that's all!).

Answer 1

I prematurely posted the previous answer when I didn't actually have any child data to fetch. At the time I was only interested in fetching parent data, and that answer worked.

Now when I actually need child data as well I find it didn't work completely. I found this article which indicates that .Include() (he says Including() but I'm not sure if that's a typo) has been removed, and the correct solution is to use DataLoadOptions. In addition, I also needed to enable Unidirectional Serialisation.

And to top it off, I no longer need DeferredLoadingEnabled. So now the final code looks like this:

using (MsSqlDataContext db = new MsSqlDataContext())
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<PromoCode>(p => p.CustomerPromos);
    db.LoadOptions = options;

    return db.PromoCodes.ToArray();
}

After setting Unidirectional Serialisation it will happily return a parent object without having to load the child, or explicitly set DeferredLoadingEnabled = false;.