Executing client-side functions in LINQ queries

There have always been two kinds of LINQ in .NET: LINQ to Objects, and all the others. Pun aside, the fact is that there are two fundamentally different LINQ implementations and sometimes we don't even realize that. There's the one that applies to IEnumerable<T> instances, and is executed in memory at once—LINQ to Objects—and then there is a myriad of others that are instead interpreted from expressions originating from IQueryable<T> instances and then translated to a specific dialect, such as SQL, and only executed when requested. Their syntaxes are exactly the same, and we can only tell them apart if we know the source. Here is an example:

var blogs = from blog in Blogs
where blog.Name.Contains("Development")
select blog;

Depending on whether Blogs is an implementation of IQueryable<Blog> or IEnumerable<Blog>, you will get one implementation or the other. Now, let's consider that this code is going to be translated to...