IQueryable is deleted after use

Question

I have a 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 save this code as it is and use the result, I get an exception indicating that the data has been deleted. But if I like it:

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

Everything works perfectly. But my problem is that I want to use Linq Dynamic, which require IQueryable. Is there a way to return a local IQueryable so that I can continue working with it?

Answer 1

If you want to literally do what you ask

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

be careful, although what you return is no longer related to the data context, so for example, binding to relationships will not work as you would expect if the data context were not deleted.

Answer 2

The problem is that you create your data context and then fire it:

using (var ctx = Database.AccountingContext())

To make this happen, try 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 the method instead, now the problem is that you need to manage it yourself, and you may also need to consider that you are blocking more connections, so be sure to establish a connection .

Edit

I assumed that you also wanted to use relationships, etc.

Answer 3

You need to choose:

• Do not delete the context (without using statement) and get an IQueryable<User> , which is based on the database.
• Eliminating the context and getting an IQueryable<User> through ToList().AsQueryable() , which relies on Memory.

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

Answer 4

The problem is that you are deleting the DataContext. One option is to keep it while you use the query result.