[SOLVED] LINQ query does not work without .ToList()

LINQ query does not work without .ToList()

Consider following LINQ-to-NHibernate queries:

var q1 = from se in query.ToList<SomeEntity>()
  where
  prop1 == "abc"
  select se;

var q2 = from se in q1
  where 
  m1(se.prop2) == "def"
  select  se;

q2 will not work with error: "The method m1 is not implemented". But when replace q2 with following query, everything goes ok:

var q2 = from se in q1.ToList<SomeEntity>()
  where 
  m1(se.prop2) == "def"
  select  se;

Why this happens? How can I get first query to work too? Is this something that happens for LINQ-to-NHibernate only or happens in all LINQ queries?

Solution

Because there is no way for the LINQ provider to translate the method m1 to a compatible SQL statement.

By calling ToList<SomeEntity>(), you are reading the entire thing into memory and then using LINQ to Objects to filter (and since the query doesn't get translated to SQL in that case, there is no problem running the query).

Unfortunately there is no easy way for you to get the first query to work. If you really need to use m1 to filter results, you'll have to read things into memory first.

This is not just a LINQ to nHibernate limitation either. This will happen in any situation where a LINQ provider uses Expression Trees to convert your code into another language (in this case it is trying to convert your C# code into SQL statements which is the same thing that LINQ to SQL and Entity Framework do).