The operation cannot be completed because the DbCo

问题:

I'm new to EF and I'm trying to use an extension method which converts from my Database type User to my info class UserInfo.
I'm using database first if that makes a difference?

My code below gives the error

The operation cannot be completed because the DbContext has been disposed.

try
{
    IQueryable<User> users;
    using (var dataContext = new dataContext())
    {
        users = dataContext.Users
                  .Where(x => x.AccountID == accountId && x.IsAdmin == false);
        if(users.Any() == false)
        {
            return null;
        }
    }
    return users.Select(x => x.ToInfo()).ToList(); // this line is the problem
}
catch (Exception ex)
{
    //...
}

I can see why it would do it, but I also don't understand why the result of the where statement isn't being saved into the users object?

So I guess my main question is why doesn't it work and secondly what's the right way of using extension methods and EF?

回答1:

This question & answer lead me to believe that IQueryable require an active context for its operation. That means you should try this instead:

try
{
    IQueryable<User> users;

    using (var dataContext = new dataContext())
    {
        users = dataContext.Users.Where(x => x.AccountID == accountId && x.IsAdmin == false);

        if(users.Any() == false)
        {
            return null;
        }
        else
        {
            return users.Select(x => x.ToInfo()).ToList(); // this line is the problem
        }
    }


}
catch (Exception ex)
{
    ...
}

回答2:

Objects exposed as IQueryable<T> and IEnumerable<T> don't actually "execute" until they are iterated over or otherwise accessed, such as being composed into a List<T>. When EF returns an IQueryable<T> it is essentially just composing something capable of retrieving data, it isn't actually performing the retrieve until you consume it.

You can get a feel for this by putting a breakpoint where the IQueryable is defined, vs. when the .ToList() is called. (From inside the scope of the data context as Jofry has correctly pointed out.) The work to pull the data is done during the ToList() call.

Because of that, you need to keep the IQueryable<T> within the scope of the data context.

回答3:

You need to remember that IQueryable queries are not actually executed against the data store until you enumerate them.

using (var dataContext = new dataContext())
{

This line of code doesn't actually do anything other than build the SQL statement

    users = dataContext.Users.Where(x => x.AccountID == accountId && x.IsAdmin == false);

.Any() is an operation that enumerates the IQueryable, so the SQL is sent to the data source (through dataContext), and then the .Any() operations is executed against it

    if(users.Any() == false)
    {
        return null;
    }
}

Your "problem" line is reusing the sql built above, and then doing an additional operation (.Select()), which just adds to the query. If you left it here, no exception, except your problem line

return users.Select(x => x.ToInfo()).ToList(); // this line is the problem

calls .ToList(), which enumerates the IQueryable, which causes the SQL to be sent to the datasource through the dataContext that was used in the original LINQ query. Since this dataContext has been disposed, it is no longer valid, and .ToList() throws an exception.

That is the "why it doesn't work". The fix is to move this line of code inside the scope of your dataContext.

How to use it properly is another question with a few arguably correct answers that depend on your application (Forms vs. ASP.net vs. MVC, etc.). The pattern that this implements is the Unit of Work pattern. There is almost no cost to creating a new context object, so the general rule is to create one, do your work, and then dispose of it. In web apps, some people will create a Context per request.

回答4:

The reason why it is throwing the error is the object is disposed and after that we are trying to access the table values through the object, but object is disposed.Better to convert that into ToList() so that we can have values

Maybe it isn't actually getting the data until you use it (it is lazy loading), so dataContext doesn't exist when you are trying to do the work. I bet if you did the ToList() in scope it would be ok.

try
{
    IQueryable<User> users;
    var ret = null;

    using (var dataContext = new dataContext())
    {
        users = dataContext.Users.Where(x => x.AccountID == accountId && x.IsAdmin == false);

        if(users.Any())
        {
            ret = users.Select(x => x.ToInfo()).ToList(); 
        }

     }

   Return ret;
}
catch (Exception ex)
{
    ...
}

回答5:

This can be as simple as adding ToList() in your repository. For example:

public IEnumerable<MyObject> GetMyObjectsForId(string id)
{
    using (var ctxt = new RcContext())
    {
        // causes an error
        return ctxt.MyObjects.Where(x => x.MyObjects.Id == id);
    }
}

Will yield the Db Context disposed error in the calling class but this can be resolved by explicitly exercising the enumeration by adding ToList() on the LINQ operation:

public IEnumerable<MyObject> GetMyObjectsForId(string id)
{
    using (var ctxt = new RcContext())
    {
        return ctxt.MyObjects.Where(x => x.MyObjects.Id == id).ToList();
    }
}

回答6:

Change this:

using (var dataContext = new dataContext())
{
    users = dataContext.Users.Where(x => x.AccountID == accountId && x.IsAdmin == false);

    if(users.Any())
    {
        ret = users.Select(x => x.ToInfo()).ToList(); 
    }

 }

to this:

using (var dataContext = new dataContext())
{
    return = dataContext.Users.Where(x => x.AccountID == accountId && x.IsAdmin == false).Select(x => x.ToInfo()).ToList();
} 

The gist is that you only want to force the enumeration of the context dataset once. Let the caller deal with empty set scenario, as they should.

回答7:

Here you are trying to execute IQueryable object on inactive DBContext. your DBcontext is already disposed of. you can only execute IQueryable object before DBContext is disposed of. Means you need to write users.Select(x => x.ToInfo()).ToList() statement inside using scope

回答8:

using(var database=new DatabaseEntities()){}

Don't use using statement. Just write like that

DatabaseEntities database=new DatabaseEntities ();{}

It will work.

For documentation on the using statement see here.