Geek asks and answers

How to convert Lambda expression to Sql?

I am developing a small structure for accessing a database. I want to add a function that makes a request using a lambda expression. How to do it?

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

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

For instance:

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.
+5
source share
3 answers

Go ahead and create a LINQ provider (I'm sure you don't want to do this).

This is a lot of work, so maybe you just want to use the NHibernate or Entity Framework or something else like this.

If your queries are fairly simple, you may not need the full-size LINQ provider. Look at Expression Trees (which are used by LINQ providers).

- :

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

, . , SQL.

Linq2Sql, NHibernate EntityFramework...

+12

-

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


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


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

, BLToolkit

+2

You can look at http://iqtoolkit.codeplex.com/ This is very difficult, and I do not recommend you create something from scratch.

I just wrote something close to dkons answers, I will add anyway. Just using the free interface is 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   
    }
}

Let's 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);


        }
0
source

More articles:


All Articles