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?

Solution 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.

Solution 2

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

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

Solution 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.

Comments

[tymtam]

One doesn't need to sit down to cry.

[robinleathal]

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