使用 Linq-to-Sql 表达式生成 SQL 子句

发布于 2024-12-17 18:20:56 字数 652 浏览 0 评论 0原文

我想创建一个存储库模型，它可以采用表达式并使用 Linq-To-Sql 生成所需的 SQL 语句。

例如，我有一个这样的函数：

// Possible criteria
Expression<Func<Purchase,bool>> criteria1 = p => p.Price > 1000;

// Function that should take that criteria and convert to SQL statement
static IEnumerable<Customer> GetCustomers (Expression<Func<Purchase,bool>> criteria)
{
   // ...
}

在函数内部，我想使用 Linq-To-Sql 将条件转换为 SQL 语句。

我知道您可以使用 DataContext.Log 查看执行的查询，使用 DataContext.GetCommand(query).CommandText 在执行之前查看完整的查询。但是，我只想生成整个表达式的一部分。

我希望完成的是让我的存储库抽象底层技术（Linq-to-Sql、Dapper 等）。这样我就可以将表达式传递到存储库，让它生成正确的语句并使用正确的技术来执行它。

原文

I would like to create a repository model that could take an Expression and use Linq-To-Sql to generate the required SQL statement.

For example, I have a function such as this:

// Possible criteria
Expression<Func<Purchase,bool>> criteria1 = p => p.Price > 1000;

// Function that should take that criteria and convert to SQL statement
static IEnumerable<Customer> GetCustomers (Expression<Func<Purchase,bool>> criteria)
{
   // ...
}

Inside the function, I would like to convert criteria to a SQL statement using Linq-To-Sql.

I am aware that you can use DataContext.Log to see the executed queries and DataContext.GetCommand(query).CommandText to see the full query before it is executed. However, I would like just a part of the entire expression generated.

What I am hoping to accomplish is to make my repository abstract the underlying technology (Linq-to-Sql, Dapper, etc). That way I could pass the Expression to the repository, have it generate the right statement and use the right technology to execute it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

葬心 2024-12-24 18:20:56

您可以这样做：

string sql = DataContext.GetTable<Customer>().Where(criteria).ToString();

ToString() 为您提供 SQL 表达式。然后，您可以使用正则表达式来提取 WHERE 子句。

You could do something like this:

string sql = DataContext.GetTable<Customer>().Where(criteria).ToString();

ToString() gives you the SQL expression. You could then use regex to pull out the WHERE clause.

回复收藏 0 原文

农村范ル 2024-12-24 18:20:56

这是我用来构建自己的谓词以在Where 函数中使用的代码摘录。编译器无法处理复杂对象的枚举，因此您必须自己处理。

本质上，代码传递了可枚举的（字符串代码、字符串交换）元组，然后构建一个表达式来检索具有 Security.Code == tuple.Code AND (Security.MasterExchangeForStocksId == tuple.exchange OR SecurityExchangeId) 的所有 Security 对象==元组.交换）。

CreateTrEntitiesAsync() 仅返回一个实体框架上下文，该上下文具有 DbSet Security 属性。

public async Task<Security[]> GetSecurities(IEnumerable<(string code, string exchange)> tickers)
{
    using (var ctx = await CreateTrEntitiesAsync())
    {
        var securityExpr = Expression.Parameter(typeof(Security), "security");
        Expression expr = null;
        Expression exprToadd;

        foreach (var item in tickers)
        {
            exprToadd = Expression.And(
                Expression.Equal(Expression.Property(securityExpr, nameof(Security.Code)), Expression.Constant(item.code)),
                Expression.Or(
                    Expression.Equal(Expression.Property(Expression.Property(securityExpr, nameof(Security.Exchange)), nameof(Exchange.MasterExchangeForStocksId)), Expression.Constant(item.exchange)),
                    Expression.Equal(Expression.Property(securityExpr, nameof(Security.ExchangeId)), Expression.Constant(item.exchange))
                )
            );

            if (expr == null)
                expr = exprToadd;
            else
                expr = Expression.Or(expr, exprToadd);
        }

        var criteria = Expression.Lambda<Func<Security, bool>>(expr, new ParameterExpression[] { securityExpr });
        var items = ctx.Securities.Where(criteria);
        return await items.ToArrayAsync();
    }
}

This is a code excerpt that I use to build my own predicate to use in the Where function. The compiler can't cope with ienumerables of complex objects, so you have to do it yourself.

Essentially, the code gets passed an ienumerable of (string code, string exchange) tuples, and then builds an expression to retrieve all Security objects that have Security.Code == tuple.Code AND (Security.MasterExchangeForStocksId == tuple.exchange OR SecurityExchangeId == tuple.exchange).

CreateTrEntitiesAsync() simply returns a Entity Framework context, which has a DbSet Security property.

public async Task<Security[]> GetSecurities(IEnumerable<(string code, string exchange)> tickers)
{
    using (var ctx = await CreateTrEntitiesAsync())
    {
        var securityExpr = Expression.Parameter(typeof(Security), "security");
        Expression expr = null;
        Expression exprToadd;

        foreach (var item in tickers)
        {
            exprToadd = Expression.And(
                Expression.Equal(Expression.Property(securityExpr, nameof(Security.Code)), Expression.Constant(item.code)),
                Expression.Or(
                    Expression.Equal(Expression.Property(Expression.Property(securityExpr, nameof(Security.Exchange)), nameof(Exchange.MasterExchangeForStocksId)), Expression.Constant(item.exchange)),
                    Expression.Equal(Expression.Property(securityExpr, nameof(Security.ExchangeId)), Expression.Constant(item.exchange))
                )
            );

            if (expr == null)
                expr = exprToadd;
            else
                expr = Expression.Or(expr, exprToadd);
        }

        var criteria = Expression.Lambda<Func<Security, bool>>(expr, new ParameterExpression[] { securityExpr });
        var items = ctx.Securities.Where(criteria);
        return await items.ToArrayAsync();
    }
}

回复收藏 0 原文

~没有更多了~