如何将Lambda表达式转换为Sql? [英] How to Convert Lambda Expression To Sql?

问题描述

我正在开发一个小的框架来访问数据库.我想添加一个使用lambda表达式进行查询的功能.我该怎么做?

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

例如:

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.

推荐答案

继续并创建一个 LINQ提供程序(我确信您还是不想这样做).

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

大量的工作，所以也许您只想使用 NHibernate 或

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

如果您的查询非常简单，则可能不需要完整的LINQ提供程序.看看表达式树(由LINQ提供程序使用).

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

您可以 hack 这样的内容:

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

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.

为什么不只使用Linq2Sql，NHibernate或EntityFramework ...

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

这篇关于如何将Lambda表达式转换为Sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！