Entity Framework attach query to new DbContext

Question

If you query the DbSet of a DbContext, the query is valid until the DbContext is disposed. The following will lead to an exception:

IQueryable<Video> allVideos = null;
using (var context = new MyDbContext())
{
    allVideos = context.Videos;
}
var firstVideo = allVideos.first();

Apparently the used DbSet is stored somewhere in the returned object that implements the IQueryable.

However, MSDN advises (Link)

When working with Web applications, use a context instance per request.

Of course I could use ToList() and return the result as a list of objects, but this is rather undesirable because I don't know the reason for the query.

Example: Suppose my database has a collection countries, which have cities, which have streets, which have houses, which have families which have persons which have names.

If someone asks for the IQueryable, then it could be that he wants to search for the name of the oldest person living on Downing Street nr 10 in London in the United Kingdom.

If I returned the sequence with a ToList(), all cities, streets, houses, persons, etc would be returned, which would be quite a waste if he only needed the name of this one person. That's the nice thing about deferred execution of Linq.

So I can't return ToList(), I have to return the IQueryable.

So what I'd like to do, is open a new DbContext, and somehow tell the query that it should use the new DbContext:

IQueryable<Video> allVideos = null;
using (var context = new MyDbContext())
{
    allVideos = context.Videos;
}
// do something else
using (var context = new MyDbContext())
{
    // here some code to attach the query to the new context
    var firstVideo = allVideos.first();
}

How to do this?

why do that, just call ToList() in the first context, to force the query to execute — 3dd
As @3dd suggested just execute the query. Remember that your queriable is just an expression being held onto by the context. It is either executed or discarded. Although it is possible to save expressions and execute them later you really don't need to in this case. — Stephen Brickner

Harald Coppoolse Harald Coppoolse · Accepted Answer · 2015-11-12T15:30:00

The local guru happened to pass by. He explained to me that the error in my design was that I already use a DbContext while I am only composing the query. My interface should be such that I only need the DbContext when actually materializing the requested objects.

The question was a simplified version of the following:

I have a DbContext, with several public DbSet properties. These properties mirror the actual database. I want to hide the actual database implementation in my Abstract Database Layer in order to protect my data. I don't want anyone to give access change the contents of the database without having checked whether these contents are correct.

This is easy: just don't expose your actual DbContext to the outside world, but expose a facade that hides the actually used DbContext. This facade communicates with the actual DbContext.

With most functions that return an IQueryable I need the DbContext to access the DbSets. That's why I thought to create a context, construct the query and Dispose the context. But because of the deferred execution the context is still needed.

The solution

The solution is not to create your own context, but let the caller construct the DbContext. This constructed DbContext will be one of the parameters of the function. In that case the external user can call several functions of my facade to concatenate the query, even mix with his own Linq queries on the DbContext without creating and disposing the context. So like others suggested:

Callers creates the dbContext
Caller calls several of my functions that return a query, pass the dbContext as parameter
Caller executes the query by using ToList() / ToArray() / First() / Count() etc.
Caller disposes the context

To make it even nicer, the dbContext parameter is used in an extension method:

public static IQueryable<Video> GetObsoleteVideos(this MyDbContext dbContext)
{
    // perform several difficult Linq statements on context
    // that will return all obsolete videos
    return ...
}

public static IQueryable<Video> GetThrillerVideos(this MyDbContext dbContext)
{
     return dbContext.Videos.Where(video => video.Genre == VideoGenre.Thriller);
}

usage:

using (var myContext = new MyDbContext())
{
    var difficultQuery = myContext.GetObsoleteVideos()
        .Where(video => video.Name == ...)
        .GetThrillerVideos()
        .Take(10);

    // Note: the query still deferred, execute it now, before Disposing myContext
    var result = difficultQuery.ToList();
}

This way (and especially if I create an interface) I am able to prohibit access to my DbSets. I am even free to internally reorganize my Db and DbContext without external users noticing anything.

Entity Framework attach query to new DbContext

2 Answers

The solution