How to Convert Lambda Expression To Sql?

Question

I am developing a small framework to access the database. I want to add a feature that makes a query using a lambda expression. How do I do this?

public class TestModel
{
    public int Id {get;set;}
    public string Name {get;set;}
}

public class Repository<T>
{
    // do something.
}

For example:

var repo = new Repository<TestModel>();

var query = repo.AsQueryable().Where(x => x.Name == "test"); 
// This query must be like this:
// SELECT * FROM testmodel WHERE name = 'test'

var list = query.ToDataSet();
// When I call ToDataSet(), it will get the dataset after running the made query.

You should use a O/RM framework that enables LINQ over Expressions, such as LINQ to SQL or Entity Framework. — Steven, Jun 11 '12 at 11:17
Do you want to implement a [query provider](http://msdn.microsoft.com/en-us/library/system.linq.iqueryprovider.aspx)? — phipsgabler, Jun 11 '12 at 11:19
Use [Linqpad](http://www.linqpad.net/) to know more about this. — Sunny, Jun 11 '12 at 11:21
@Steven I dont want to use a ORM. Can I do without using orm? — Sinan AKYAZICI, Jun 11 '12 at 11:54
@phg I looked to query provider. It seem complex. It can be helpful me. But I dont know how to use it. Can you show example to me ? — Sinan AKYAZICI, Jun 11 '12 at 12:00
@sinanakyazici: Writing your own Query Provider is EXTREMELY COMPLEX!! Don't waste your boss'es money in writing your own. You will fail anyway. Just work with the first version of Entity Framework (.NET 3.5) (and find out how much EF1 sucks) to understand how complex it is to write your own engine that allows converting expression trees to SQL. — Steven, Jun 11 '12 at 12:10
That's heavy stuff, indeed. [This](http://msdn.microsoft.com/en-us/library/bb546158.aspx) tutorial seemed quite good to me, but it's very detailed. Make sure you understand how `IQueriable` really works at first. I'd also try something simpler than parsing expressions at first, e.g. using an internal LINQ to SQL provider to simulate the `IQueryable`. — phipsgabler, Jun 11 '12 at 12:21
To get an idea of the depths of LINQ, go watch [this](http://channel9.msdn.com/shows/Going+Deep/Erik-Meijer-and-Bart-De-Smet-LINQ-to-Anything/). — phipsgabler, Jun 11 '12 at 12:29

score 17 · Answer 1 · edited Aug 13 '21 at 13:29

Go on and create a LINQ Provider (I am sure you don't want to do this, anyway).

It's a lot of work, so maybe you just want to use NHibernate or Entity Framework or something like that.

If your queries are rather simple, maybe you don't need a full blown LINQ Provider. Have a look at Expression Trees (which are used by LINQ Providers).

You can hack something like this:

public static class QueryExtensions
{
    public static IEnumerable<TSource> Where<TSource>(this Repo<TSource> source, Expression<Func<TSource, bool>> predicate)
    {
        // hacks all the way
        dynamic operation = predicate.Body;
        dynamic left = operation.Left;
        dynamic right = operation.Right;

        var ops = new Dictionary<ExpressionType, String>();
        ops.Add(ExpressionType.Equal, "=");
        ops.Add(ExpressionType.GreaterThan, ">");
        // add all required operations here            

        // Instead of SELECT *, select all required fields, since you know the type
        var q = String.Format("SELECT * FROM {0} WHERE {1} {2} {3}", typeof(TSource), left.Member.Name, ops[operation.NodeType], right.Value);
        return source.RunQuery(q);
    }
}
public class Repo<T>
{
    internal IEnumerable<T> RunQuery(string query)
    {
        return new List<T>(); // run query here...
    }
}
public class TestModel
{
    public int Id { get; set; }
    public string Name { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var repo = new Repo<TestModel>();
        var result = repo.Where(e => e.Name == "test");
        var result2 = repo.Where(e => e.Id > 200);
    }
}

Please, don't use this as it is. This is just a quick and dirty example how expression trees can be analyzed to create SQL statements.

Why not just use Linq2Sql, NHibernate or EntityFramework...

I tried to use linq provider. I did explained. But It didn't work. There are too much class. Dont I do a more simple way? — Sinan AKYAZICI, Jun 11 '12 at 12:09
@sinanakyazici I added a simple example for you to get the idea — sloth, Jun 11 '12 at 12:26
@sinanakyazici Yes, creating a linq provider is complex. That is why people don't write their own if they don't have to, but instead use existing ones. — sloth, Jun 11 '12 at 12:30
Thank you very much. You wrote another Where(). But I want to use Where() of Linq. So your wrote useless for me. Presumably, I can do using Linq Provider. — Sinan AKYAZICI, Jun 11 '12 at 12:36
Why Don't I use them (Linq2Sql, NHibernate and EntityFR)? Beacuse I have written already in my ORM. So I needn't them. — Sinan AKYAZICI, Jun 11 '12 at 12:44
+1 Though this would only work with arithmetic operators. This would however fail if operation.NodeType is a Call. For example this equals call won't work: e => e.Name.Equals("test") — suomi-dev, Dec 31 '20 at 18:45

score 2 · Answer 2 · answered Jun 11 '12 at 12:42

if you want to do things like

db.Employee
.Where(e => e.Title == "Spectre")
.Set(e => e.Title, "Commander")
.Update();

or

db
.Into(db.Employee)
    .Value(e => e.FirstName, "John")
    .Value(e => e.LastName,  "Shepard")
    .Value(e => e.Title,     "Spectre")
    .Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
.Insert();

or

db.Employee
.Where(e => e.Title == "Spectre")
.Delete();

Then check out this, BLToolkit

score 0 · Answer 3 · edited Dec 21 '12 at 22:53

You might want to look at http://iqtoolkit.codeplex.com/ Which is very complex and i dont recommend you to build something from scratch.

I just wrote something close to dkons's answer I will add it anyway. Just using fluent interface nothing more.

public class Query<T> where T : class
{
    private Dictionary<string, string> _dictionary;

    public Query()
    {
        _dictionary = new Dictionary<string, string>();
    } 

    public Query<T> Eq(Expression<Func<T, string>> property)
    {
        AddOperator("Eq", property.Name);
        return this;
    }

    public Query<T> StartsWith(Expression<Func<T, string>> property)
    {
        AddOperator("Sw", property.Name);
        return this;
    }

    public Query<T> Like(Expression<Func<T, string>> property)
    {
        AddOperator("Like", property.Name);
        return this;
    }

    private void AddOperator(string opName, string prop)
    {
        _dictionary.Add(opName,prop);
    }

    public void Run(T t )
    {
        //Extract props of T by reflection and Build query   
    }
}

Lets say you have a model like

class Model
    {
        public string Surname{ get; set; }
        public string Name{ get; set; }
    }

You can use this as :

static void Main(string[] args)
        {

            Model m = new Model() {Name = "n", Surname = "s"};
            var q = new Query<Model>();
            q.Eq(x => x.Name).Like(x=>x.Surname).Run(m);


        }

Property.Name is always null. In order to extract it you will have to do the following: var expression = (MemberExpression)property.Body; string name = expression.Member.Name; — Ben Pretorius, Jul 08 '14 at 18:33
And another thing is that u cant use dic think IF u have "LIKE " two times, what will happend then — Alen.Toma, Sep 19 '17 at 14:52

How to Convert Lambda Expression To Sql?

3 Answers3

Linked