Linq2Sql: Using IQueryable in select

Question

This is probably quite trivial but.... I have a quite complicated linq query that I have put in a IQueryable function that I'd like to reuse throughout the project. But when I use this in a select I get "System.Linq.IQueryable`1[LINQPad.User.Orders] Foo(System.Guid) doesn't have a translation to SQL": (very simplified to show what I've after)

void Main()
{
 (from e in Clients
 select new {
  e.CompanyName,
  Orders = Foo(e.Id).Count()
 }).Take(20).Dump();
}

IQueryable<Orders> Foo(Guid clientId)
{
 return Orders.Where(e => e.ClientId.Equals(clientId)); 
}

Doesn't work, but the following does:

void Main()
{
 (from e in Clients
 select new {
  e.CompanyName,
  Orders = Orders.Where(f => f.ClientId.Equals(e.Id)).Count()
 }).Take(20).Dump();
}

Is there any way I can rewrite the query without rewriting Foo?

Answer 1

The problem is that you can't just inject a function into your Linq query, because Linq-to-SQL or Linq-to-Entities doesn't know how to translate it. There's a good description of the problem, and a solution available at the LINQPad site.

An alternative is to bring the dataset into memory first (e.g. by using ToList()), but that won't really achieve exactly what you're after.