17

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?

Kirschstein
  • 14,570
  • 14
  • 61
  • 79
  • 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). – tymtam Mar 01 '12 at 08:58

3 Answers3

32

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 2
    Does this technique with Linq to EF as well? It doesn't seem to be. – neontapir Jul 06 '11 at 22:13
  • I don't understand what 'execute locally rather than in the database' mean. For those like me: IQueryable as = from a in x select a; then a.Count(); a.Foreach(...) will execute two SQL queries. – tymtam Mar 01 '12 at 07:55
  • 2
    @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. – Jon Skeet Mar 01 '12 at 08:06
  • @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 as = (from a in x select a).AsEnumerable().Where( o => o.Name != null); vs. IQueryable 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? – tymtam Mar 01 '12 at 08:54
  • @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. – Jon Skeet Mar 01 '12 at 09:06
  • 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. –  Nov 30 '18 at 21:16
7
List<Employees> myEmployees =  myqueryable.ToList();

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

Thomas Stock
  • 10,927
  • 14
  • 62
  • 79
2

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.

kastermester
  • 3,058
  • 6
  • 28
  • 44