How to Convert Lambda Expression To Sql?

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.

Answers


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...


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


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);


        }

Need Your Help

Debian include-binaries format

debian apt

I am trying to build a precompiled debian package for my application.

How to find friends on Facebook by using their phone number via Facebook Graph API in C#

c# facebook-graph-api phone-number

I'm writing a tool to check a phone number that registered Facebook account or not by using C#. But I don't know which Graph API do Facebook support to C# to do this.