Does this extension method efficiently materialize my IQueryable?

Question

So I recently discovered that you can force Entity Framework not to translate your projection into SQL by specifying a Func<T, TResult> to the .Select() extension method rather than an expression. This is useful when you want to transform your queried data, but that transformation should happen in your code rather than on the database.

For example, when using EF5's new Enum support and trying to project that to a string property in a DTO, whereas this would fail:

results.Select(r => new Dto { Status = r.Status.ToString() })

this would work:

results.Select(new Func<Record, Dto>(r => new Dto { Status = r.Status.ToString() }));

because in the first (expression) case, EF can't figure out how to translate Status.ToString() to something the SQL database could perform, but as per this article Func predicates aren't translated.

Once I had this working, it wasn't much of a leap to create the following extension method:

public static IQueryable<T> Materialize<T>(this IQueryable<T> q)
{
    return q.Select(new Func<T, T>(t => t)).AsQueryable();
}

So my question is - are there any pitfalls I should be wary of when using this? Is there a performance impact - either in injecting this do-nothing projection into the query pipeline or by causing EF to not send the .Where() clause to the server and thereby send all the results over the wire?

The intention is to still use a .Where() method to filter the results on the server, but then to use .Materialize() before .Select() so that the provider doesn't try to translate the projection to SQL Server:

return Context.Record
    .Where(r => // Some filter to limit results)
    .Materialize()
    .Select(r => // Some projection to DTO, etc.);

Looks good. `Where` should be sent to the database. However, why not simply use `ToList()` instead of `Materialize`? — Daniel Hilgarth, Sep 12 '12 at 11:37

Ladislav Mrnka · Accepted Answer · 2012-09-12T12:16:20.190

Simply using AsEnumerable should do the same:

return Context.Record
              .Where(r => // Some filter to limit results)
              .AsEnumerable() // All extension methods now accept Func instead of Expression
              .Select(r => // Some projection to DTO, etc.);

There is also no reason in your Materialize method to go back to IQueryable because it is not a real IQueryable translated to another query any more. It is just IEnumerable.

In terms of performance you should be OK. Everything before materialization is evaluated in the database and everything after materialization in your code. Moreover in both your and my example query has still deferred execution - it is not executed until something enumerates the query.

score 0 · Answer 2 · answered Oct 04 '13 at 14:22

0

There is one problem though: number of columns fetched to client. In first case, it would be something like select Status from Record and in another select Status, field2, field3, field4 from Record

answered Oct 04 '13 at 14:22

Ben

2,454
26
32

Does this extension method efficiently materialize my IQueryable?

2 Answers2