LINQ query does not work without .ToList()

Question

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?

Answer 1

Presumably the method m1 does not have a translation to SQL (at least, the NHibernate LINQ provider can't figure out how to). When you don't have the ToList, NHibernate is trying to figure out how to convert m1 to SQL. When you do the ToList, NHibernate isn't playing a role anymore and its LINQ-to-Objects that can handle the query. This is specific to ORMs that enable LINQ; LINQ-to-SQL and EF will suffer similar fates.

Answer 2

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

Answer 3

I would say that your original q2 query is being translated into an expression tree and then when NHibernate tries to parse it, it finds that the method is not a part of its implementation. Converting the query to a collection first with ToList() uses the LINQ functionality of the List which can support the m1 method.