Lambda expression to SQL query without external DLL

Question

I'd like to convert a lambda expression into a SQL query. To do this, I have done:

public class User
{
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public string Address {get; set;}
    public int Age {get; set;}
}

var user = new List<User>();
IQueryable<User> query = user.Where(x => x.Age >= 33).AsQueryable();

The 'query' variable is {System.Linq.Enumerable+WhereListIterator`1[Domain.User]}.

However I want the IQueryable<User> query = user.Where(x => x.Age >= 33).AsQueryable(); code becomes 'select FirstName, LastName, Address, Age from User where Age >= 33'

How can I do this?

Important note:

• I am not using EF because the data base is OLE DB.

Answer 1

When you compose queries, internally a new query is formed by creating a new (probably larger) Expression-tree (query.Expression), all of which sits on top of the provider (query.Provider).

If you don't currently have a provider (other than LINQ-to-Objects), then in theory you should still be able to take the expression tree from a query (via query.Expression), but you still have all the work ahead of you in terms of generating SQL from that. That's pretty much the entire purpose of the provider. It doesn't help that there is no single "OLE DB" syntax for anything - you still need to issue commands in the particular language variant that the actual endpoint expects.

So; you can get the lambda via something like:

var query = list.AsQueryable().Where(x => x.Age >= 35);
var lambda = query.Expression;

but to do this yourself you'd have to tease the expression tree apart carefully, discard the root list object, just taking the inner lambda to the Expression.Call (for the "Where"), then write your own SQL generator that understands some basic expression trees and understands the SQL variant that you want to emit.

Frankly, this is going to be a lot of work, and it probably isn't a great use of your time. If all you need to support is basic filters, it would probably be quicker to write a shunting-yard implementation of a basic filter DSL.