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!).

Can you shared the code fragment that throws the actual exception? Based on what you have above, this would happen after you've returned the list of `PromoCodes` and are then doing something with them. — Brendan Green, Jan 04 '16 at 02:37
If that's the case then it's because it's in a WCF data service. It's being converted to JSON. There is a _massive_ exception stack trace in the log file, but the only recognisable part is `WriteArrayOfPromoCodeToJson`, `WritePromoCodeToJson`, `WriteArrayOfCustomerPromoToJson`. — Ian, Jan 04 '16 at 02:40
only thing that comes to mind with what you shared so far is that you are trying to use your datacontext outside of your Using block. — Niklas, Jan 04 '16 at 02:40
Just from reading your error I would suggest instead of returning inside of your using (MsSqlDataContext db) to create an array variable, assign to it and call ToArray(), leave the using context, and then return. Does that change anything? — wentimo, Jan 04 '16 at 02:41
try declaring and array,and then adding items of your Customer promos to it 1 by 1 in a foreach loop as objects,see if returning that variable works,i believe what you are doing is making refs to what your datacontext contains,so as long as you do not make a new object that holds your data,accessing the array that u made in this code only reads from the datacontext which will be disposed after you leave your using block — Niklas, Jan 04 '16 at 02:45
@Ian, can you try strongly typing your returns? On this page https://msdn.microsoft.com/en-us/library/gg671236(v=vs.103).aspx at the bottom it says: When you call the Include method, the query path is only valid on the returned instance of the IQueryable of T. Other instances of IQueryable of T and the context itself are not affected. You can call this method multiple times on an IQueryable of T to specify multiple paths for the query. — wentimo, Jan 04 '16 at 03:02
@Virgil Neither `.Clone()` nor copying manually in a foreach loop made a difference. The new array throws the same error. — Ian, Jan 04 '16 at 03:23
@wentimo Sorry, I don't understand what you're asking. I'm not using `var`, nor generics. I'm explicitly returning `PromoCode[]`, so I'm not sure what you mean by "strongly type my returns". — Ian, Jan 04 '16 at 03:27
@Ian but the only thing that has the ability to dispose your datacontext is your using so imho if u remove your using it should work fine,but if it doesnt,then im out of ideas untill i see a better view of your code :) it also would help alot if u tell me whch line exactly causes the error :/ — Niklas, Jan 04 '16 at 03:28
@Virgil That's the essence of lazy-loading though, right? Not all data was read from the DB before the context was disposed of. Hence the need to `Include()`. I've updated the question with all the applicable code. I can upload the WCF log files somewhere if you want, but it was 1Mb after making 2 queries. It's a *lot* of text. — Ian, Jan 04 '16 at 03:38
@Ian, change return db.PromoCodes.Include(x => x.CustomerPromos).ToArray(); to return db.PromoCodes.Include(x => x.CustomerPromos).ToArray(); — wentimo, Jan 04 '16 at 03:39
@Ian this might be helpful [link](http://stackoverflow.com/questions/31939030/cant-access-related-data-after-disposal-of-datacontext-in-linq-to-sql) — Niklas, Jan 04 '16 at 03:52

score 10 · Accepted Answer · edited Mar 20 '20 at 15:26

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;.

score 1 · Answer 2 · edited May 23 '17 at 12:01

Edit: This did not solve the problem entirely. At the time of testing there wasn't any child data, and I wasn't trying to use it. This only allowed me to return the parent object, it doesn't return child objects. For the full solution see this answer.

Contrary to everything I've read, the answer is not to use .Include() but rather to change the context options.

using (MsSqlDataContext db = new MsSqlDataContext())
{
    db.DeferredLoadingEnabled = false; // THIS makes all the difference
    return db.PromoCodes.ToArray();
}

This link posted in the question comments (thanks @Virgil) hint at the answer. However I couldn't find a way to access LazyLoadingEnabled for LINQ to SQL (I suspect it's for EntityFramework instead). This page indicated that the solution for LINQ to SQL was DeferredLoadingEnabled.

Here is a link to the MSDN documentation on DeferredLoadingEnabled.

Eager-loading using LINQ to SQL with Include()

2 Answers2