Cannot Translate to SQL using Select(x => Func(x))

Question

I'm slowly learning the ins and outs of LINQtoSQL, but this is confusing me.

Here is the statement I have:

IQueryable<IEvent> events = (from e in db.getEvents()
                            select e).Select(x => SelectEvent(x, null));

What the SelectEvent does can be explained in this answer here. I am not using the .toList() function as I don't want potentially thousands of records brought into memory.

public IEvent SelectEvent(SqlServer.Event ev, EventType? type) {
    // Create an object which implements IEvent
    // I don't have the code in front of me, so forgive the lack of code
}

My question is really for the Select() method. I get the "Cannot translate to SQL" error and the Select() is listed in the error message.

Clueless on this one :-/.

Answer 1

Linq to SQL cannot translate arbitrary function calls like that to SQL. It has no idea what the function does. You'll have to construct whatever SelectEvent does using expressions.

Alternatively, if all you want to do is map the objects to something else after the query has gone through the database, just do it as you're looping through the results, or create a wrapping generator that does it for you.

Answer 2

The problem here is that in LinqToSQL your query is first translated to an expression tree and then later into actual running SQL code. There is only a small subset of C# code in expression tree form which can be converted to SQL.

In this case the expression tree is including a call to a method which resides in the current assembly (SelectEvent). There is no way for the convert to translate this method into code that can run in the SQL server so it throws an error.

To work around this try inlining the code for SelectEvent into the query expression.

Answer 3

It's complaining (reasonably) that it can't turn the call to SelectEvent into SQL.

If you put .AsEnumerable() before the .Select() C# will use the IEnumerable Select over the IQueryable Select:

interface IEnumerable<T> {
    ...
    // Actually, these are extension methods in Enumerable and Queryable.
    IEnumerable<T> Select<TResult>(Func<T, TResult> selector);
    ...
}

interface IQueryable<T> : IEnumerable<T> {
    ...
    IQueryable<T> Select<TResult>(Expression<Func<T, TResult>> selector);
    ...
    IEnumerable<T> AsEnumerable();
}

IEnumerable<IEvent> events = db.getEvents().AsEnumerable().Select(x => SelectEvent(x, null));

Or, if you could use a IEvent SelectEvent(SqlServer.Event) overload immediately, since it is not convertible to an Expression<>:

IEnumerable<IEvent> events = db.getEvents().Select(SelectEvent);

The IEnumerable Select executes the Func<T, TResult> selector once for each item returned from the source IEnumerable, the IQueryable Select passes the Expression<Func<T, TResult>> selector to the LINQ provider - which in the case of LINQ2SQL tries to generate the equivalent SQL.