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
– Steven, Commented Jun 11, 2012 at 11:17
@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
– Steven, Commented Jun 11, 2012 at 12:10

Maulik · Accepted Answer · 2021-08-13 13:29:42Z

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?
@sinanakyazici I added a simple example for you to get the idea
@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.
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.
Why Don't I use them (Linq2Sql, NHibernate and EntityFR)? Beacuse I have written already in my ORM. So I needn't them.

David DV · Accepted Answer · 2012-06-11 12:42:14Z

2

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

answered Jun 11, 2012 at 12:42

David DV

6744 silver badges9 bronze badges

Comments

Ryan Kohn · Accepted Answer · 2012-12-21 22:53:37Z

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;
And another thing is that u cant use dic think IF u have "LIKE " two times, what will happend then 😁

Collectives™ on Stack Overflow

How to Convert Lambda Expression To Sql?

3 Answers 3

6 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Linked

Related