使用Linq-to-Sql表达式生成SQL子句 [英] Generate a SQL clause using a Linq-to-Sql Expression

问题描述

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

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)
{
   // ...
}

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

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

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

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.

我希望完成的工作是使我的存储库抽象出底层技术(Linq-to-Sql，Dapper等).这样，我就可以将Expression传递到存储库，让它生成正确的语句并使用正确的技术来执行它.

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.

推荐答案

您可以执行以下操作:

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

ToString()给您SQL表达式.然后，您可以使用regex提取WHERE子句.

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

这篇关于使用Linq-to-Sql表达式生成SQL子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！