Linq-to-SQL - Prevent where function running query against database

Question

I have an IQueryable object which is the result of view (using a data model in Visual Studio).

I want to apply an additional filter on the object, but I don't want it to go back to the database and run the query again with the additional where clause. The query takes several seconds to run so I'd like linq to simply filter the current list rather than regenerating the query.

How do I prevent linq to sql going back to the database when I execute a .where() function?

Answer 1

use ToList() after first execution and use the generated list in the rest of your code.

var products = (from p in Products where p.Name.Contains("k") select p).ToList(); // this will go to database and fill the products list.

var x = products.Where(p => p.ProductionDate > DateTime.Now); // this will run in memory.

Answer 2

To answer the title of the question, you can generally use AsEnumerable to make subsequent bits execute locally:

var query = db.People
              .Where(p => p.Age > 18) // In database
              .AsEnumerable()
              .Where(p => p.Name == "Jon") // In .NET

The call to AsEnumerable doesn't do anything other than change the static type from IQueryable<T> to IEnumerable<T> so that the extension methods in Enumerable get called instead of those in Queryable.

However, if you reuse a query that's already executed, it will still execute again in the database... so (to answer the body of the question) if you want to reuse a query's results, you need to convert them into a list first, e.g.

var results = db.People
                .Where(p => p.Age > 18)
                .ToList();

Then accessing results won't go back to the database (unless it needs to fetch child values, of course). So you can do:

var subquery = results.Where(p => p.Name == "Jon");

That will perform the filtering in process instead.