将Linq表达式转换为SQL Server查询 [英] Converting Linq expression to sql server query
问题描述
我正在使用某些crm框架，并且此框架没有任何内部orm，并且未使用实体框架，仅使用普通sql查询.
I am using some crm framework and this framework does not have any internal orm and is not using entity framework, only plain sql queries.
我对数据库中的每个表都有实体. 所以我有例如:
I have Entity for each table in the database. So I have for example:
public class Customer{
public string FirstName{get;set;}
public int Status{get;set;}
}
无论如何，我可以编写linq查询并将其转换为sql ，而无需使用实体框架或NHibernate ? 我正在寻找类似的东西.
Is there anyway I can write linq queries and convert them into sql without using entity framework or NHibernate? I am looking for something like.
IQueryable linq = from LinqProvider.Get<Customer>() int customer where customer.FirstName == "test" and Status > 1;
string sqlQuery = LinqProvider.ToSqlQuery(linq);
//Select * from Customer where FirstName = "test" and Status > 1
我将拥有一些高级功能，例如Join排序和聚合功能.
I would live to have some advanced functionality like Join sort and aggregation functionality.
推荐答案
注意以下两行之间的区别(lambda形式的linq):
Note difference between following 2 lines (linq in lambda form) :
var dataQ = Customer.Where(o=>(o.FirstName == "test" && o.Status > 1);
var dataL = Customer.Where(o=>(o.FirstName == "test" && o.Status > 1).ToList();
var dataS = Customer.SingleOrDefault(o=>(o.FirstName == "test" && o.Status > 1);
据我所知，linq查询被转换为lamba，然后进行了优化和自动编译(来自框架4.5).默认情况下，数据库上下文应启用延迟加载和开放式并发.延迟加载意味着在实际需要之前不会获取数据.在这种情况下，.ToList()和SingleOrDefault将强制重试数据.这意味着它们将显示在实体框架分析器中.
As far as I am aware linq queries are converted to lamba and then optimized and auto-compiled (from framework 4.5). By default your database context should have lazy loading and optimistic concurrency turned on. Lazy loading means data is not fetched before you actually need it. In this case .ToList() and SingleOrDefault will force data to be retried. That means they will show up in Entity Framework Profiler.
如果您不想使用它或不能使用它，则可以使用"ToTraceString"，但是它不能在dataL或dataS上使用，因为它们不是查询，而是具体实例.
If you do not want to use it or can not, then you can use ´ToTraceString´, not however it will not work on dataL or dataS because they are not queries, but concrete instances.
File.AppendAllText(traceFile, ((ObjectQuery)dataQ).ToTraceString());
return dataQ.ToList();
编辑
我所做的假设:
Edit
Assumptions that I made:
- 我的假设是您不能编写正确的SQL，但是对Linq有点熟悉.
- 您有不常用的数据库，该数据库将使用第三方提供程序，或者甚至无法这样做.
- 您(手动?)为数据库表创建了映射
现在您可以做的是先使用编码方法.您从这些类生成数据库.然后，您对其进行查询，并得到您的 SQL .我以为这很清楚.请注意，您可能还希望先进行代码优先迁移，因为您最有可能需要进行更改.
Now what you can do is use code first approach. You generate database from those classes. Then you query it and you get your SQL. I assumed this is clear. Note that you might want to get code-first-migrations as well, because you most likely need to make changes.
示例(仅适用于Google):
Examples (just google) :
- http://msdn.microsoft.com/en-us/data/jj193542.aspx
- http://msdn.microsoft.com/en-us/data/jj591621.aspx
- http://msdn.microsoft.com/en-us/data/jj193542.aspx
- http://msdn.microsoft.com/en-us/data/jj591621.aspx
举个例子: https://gist.github.com/margusmartsepp/f9fcc9178600ca53acf6
[Table("CustomerTest")]
public class Customer
{
[Key]
public int Id { get; set; }
public string FirstName { get; set; }
public int Status { get; set; }
}
public class CustomerContext : DbContext
{
public CustomerContext(): base("name=Program.CustomerContext"){}
public DbSet<Customer> Customers { get; set; }
}
//PM> Install-Package EntityFramework
//PM> Install-Package EntityFramework.SqlServerCompact
static void Main(string[] args)
{
using (var db = new CustomerContext())
{
var item = new Customer {FirstName = "test", Status = 2};
db.Customers.Add(item);
db.SaveChanges();
var items = db.Customers.Where(o => (o.FirstName == "test" && o.Status > 1));
Console.WriteLine(items.ToString());
}
Console.ReadKey();
}
示例输出:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[Status] AS [Status]
FROM [CustomerTest] AS [Extent1]
WHERE (N'test' = [Extent1].[FirstName]) AND ([Extent1].[Status] > 1)
这篇关于将Linq表达式转换为SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
