IQueryable is disposed after using

Question

I have some short code that looks like this:

public static IQueryable<User> SelectFromEmployee(int employee)
{
    using (var ctx = Database.AccountingContext())
    {
        return ctx.Users.Where(c => c.Employee_FK == employee);
    }
}

If i just keep this code as it is and use the result i get en exception telling me that the data is disposed. But if I do like this:

public static IEnumerable<User> SelectFromEmployee(int employee)
{
    using (var ctx = Database.AccountingContext())
    {
        return ctx.Users.Where(c => c.Employee_FK == employee).ToList();
    }
}

Everything works just fine. But my problem is that i want to use Linq Dynamic that require IQueryable. Is there any way to return a local IQueryable so i can continue working with it?

Answer 1

You need to choose:

1. Not disposing the context(without the using statement) and getting IQueryable<User>, which is relied on Database.
2. Disposing the context and getting IQueryable<User> through ToList().AsQueryable(), which is relied on Memory.

Please note that the important point is where data is through the deferred loading.

Answer 2

The problem is that you are creating your Data Context and then Disposing it:

using (var ctx = Database.AccountingContext())

Just to get your going, try this instead:

ObjectContext context = Database.AccountingContext();
public static IQueryable<User> SelectFromEmployee(int employee)
{
    return context.Users.Where(c => c.Employee_FK == employee);
}

What I did here is that I moved the Data Context outside of the method instead, the problem now is that you need to control the dispose of it by yourself and you might also need to consider that you are blocking for more connections, so be sure to dispose the connection.

Edit

I assumed that you wanted to be able to use relations etc as well.