Force an IQueryable to execute?

Question

I have a method that 'has no translation to SQL' that I want to perform on an IQueryable, is there a way to force the IQueryable to execute without having to store it in some intermediate class?

Overturf

This is a blind guess but if string.IsNullOrEmpty is the culprit then use 2 where clauses instead: .Where(o => o.Name != null).Where(o => o.Name.Length > 0). – Equation

Answer 1

Is the problem that you want your method to execute locally rather than in the database? If so, AsEnumerable is your friend. It's a very simple method, something like:

public IEnumerable<T> AsEnumerable(IEnumerable<T> source)
{
    return source;
}

The important thing is that it makes the compile-time type of the result IEnumerable<T> rather than IQueryable<T>, which means any LINQ query operators you call after that will be the LINQ to Objects ones instead of LINQ to SQL.

For example:

var query = context.Employees
                   // Filtering performed in SQL
                   .Where(emp => emp.IsFullTime)
                   .AsEnumerable()
                   // Projection performed locally; ComputeSalary has no
                   // SQL equivalent
                   .Select(emp => new { Employee = emp,
                                        Salary = ComputeSalary(emp) });

You could call ToList as suggested elsewhere, but if you're doing filtering and don't really need the full list in memory, calling AsEnumerable and filtering that result will be more efficient than loading everything first.

Langue

Does this technique with Linq to EF as well? It doesn't seem to be. – Hospitality

I don't understand what 'execute locally rather than in the database' mean. For those like me: IQueryable<IAccount> as = from a in x select a; then a.Count(); a.Foreach(...) will execute two SQL queries. – Equation

@Tymek: Yes, that execute two SQL queries. But by "execute locally" I mean "fetch all the results in the query so far from the database, but then perform the rest of the query in memory" - so you can perform filtering, projections etc which aren't applicable in SQL. – Langue

@Jon - understand now. I think an example is a must though. Hm...I'm too lame to have edit rights yet. Do you think you could add an example with something like IQueryable<IAccount> as = (from a in x select a).AsEnumerable<IAccount>().Where( o => o.Name != null); vs. IQueryable<IAccount> as = (from a in x select a).Where( o => o.Name != null); and just say that in one case the sql will have the where clause and the other will not? – Equation

@Tymek: I think it would be more appropriate to have an example where you really couldn't perform the querying in SQL. Will add one. – Langue

Important caveat to this answer: You have to use that IEnumerable before the dbcontext is disposed. AsEnumerable doesn't immediately execute the queryable and store the result (as the question title may suggest), so using(var ctx = new Ctx())someField = ctx.SomeFields.AsEnumerable(); won't work. Its only real use is to separate interpreted database calls from in-memory object manipulation. – Tinatinamou

Answer 2

List<Employees> myEmployees =  myqueryable.ToList();

and then you can do your linq stuff on that List.

Starnes

Answer 3

You get that message when you have written a query that LinqToSql doesn't know how to translate into SQL (which is what it says too).

I am not sure I get exactly what you're asking, but as far as I see, you have the following options:

1. Rewrite your query so that LinqToSql CAN translate it
2. Do as much of the query as you can on the Sql Server, then do the rest in memory (using linq to objects)
3. Sit down and cry

Assuming we rule out #3, let's look at the other 2 examples.

1. Rewriting it - to help with that, we need your linq query.

2. Here you take out the part that can't be translated from the initial query, then on your Iqueryable call ToList, and then apply the rest of the query on that list.

And can you execute the query without having to store it? Well, not really, you could always loop through the results and as such not store it in a variable, but obviously the results of the query needs to be stored somewhere.

Galle

One doesn't need to sit down to cry. – Equation

Very true - however I feel no man should be crying while standing. If you have to cry, you need to do it properly. – Galle