在 C#/.NET3.5 中构建动态 sql 查询的最佳方式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/62987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way of constructing dynamic sql queries in C#/.NET3.5?
提问by Ben
A project I'm working on at the moment involves refactoring a C# Com Object which serves as a database access layer to some Sql 2005 databases.
我目前正在处理的一个项目涉及重构一个 C# Com 对象，该对象充当某些 Sql 2005 数据库的数据库访问层。
The author of the existent code has built all the sql queries manually using a string and many if-statements to construct the fairly complex sql statement (~10 joins, >10 sub selects, ~15-25 where conditions and GroupBy's). The base table is always the same one, but the structure of joins, conditions and groupings depend on a set of parameters that are passed into my class/method.
现有代码的作者使用字符串和许多 if 语句手动构建了所有 sql 查询，以构建相当复杂的 sql 语句（~10 个连接、>10 个子选择、~15-25 个条件和 GroupBy）。基表始终相同，但连接、条件和分组的结构取决于传递到我的类/方法中的一组参数。
Constructing the sql query like this does work but it obviously isn't a very elegant solution (and rather hard to read/understand and maintain as well)... I could just write a simple "querybuilder" myself but I am pretty sure that I am not the first one with this kind of problem, hence my questions:
像这样构建 sql 查询确实有效，但它显然不是一个非常优雅的解决方案（而且也很难阅读/理解和维护）......我可以自己编写一个简单的“查询构建器”，但我很确定我不是第一个遇到这种问题的人，因此我的问题是：
- How do youconstruct your database queries?
- Does C# offer an easy way to dynamically build queries?
- 您如何构建数据库查询？
- C# 是否提供了一种简单的方法来动态构建查询？
采纳答案by sgwill
I used C# and Linq to do something similar to get log entries filtered on user input (see Conditional Linq Queries):
我使用 C# 和 Linq 做一些类似的事情来过滤用户输入的日志条目（请参阅条件 Linq 查询）：
IQueryable<Log> matches = m_Locator.Logs;
// Users filter
if (usersFilter)
matches = matches.Where(l => l.UserName == comboBoxUsers.Text);
// Severity filter
if (severityFilter)
matches = matches.Where(l => l.Severity == comboBoxSeverity.Text);
Logs = (from log in matches
orderby log.EventTime descending
select log).ToList();
Edit: The query isn't performed until .ToList() in the last statement.
编辑：直到最后一条语句中的 .ToList() 才会执行查询。
回答by Bloodhound
You may want to consider LINQ or an O/R Mapper like this one: http://www.llblgen.com/
您可能需要考虑 LINQ 或这样的 O/R 映射器：http: //www.llblgen.com/
回答by Esteban Araya
This is the way I'd do it:
这是我的做法：
public IQueryable<ClientEntity> GetClients(Expression<Func<ClientModel, bool>> criteria)
{
return (
from model in Context.Client.AsExpandable()
where criteria.Invoke(model)
select new Ibfx.AppServer.Imsdb.Entities.Client.ClientEntity()
{
Id = model.Id,
ClientNumber = model.ClientNumber,
NameFirst = model.NameFirst,
//more propertie here
}
);
}
The Expressionparameter you pass in will be the dynamic query you'll build with the different WHERE clauses, JOINS, etc. This Expression will get Invokedat run time and give you what you need.
您传入的Expression参数将是您将使用不同的 WHERE 子句、JOINS 等构建的动态查询。此表达式将在运行时被调用并提供您需要的信息。
Here's a sample of how to call it:
这是如何调用它的示例：
public IQueryable<ClientEntity> GetClientsWithWebAccountId(int webAccountId)
{
var criteria = PredicateBuilder.True<ClientModel>();
criteria = criteria.And(c => c.ClientWebAccount.WebAccountId.Equals(webAccountId));
return GetClients(criteria);
}
回答by rhys
Its worth considering if you can implement as a parameterised strored procedure and optimise it in the database rather than dynamically generating the SQL via LINQ or an ORM at runtime. Often this will perform better. I know its a bit old fashioned but sometimes its the most effective approach.
如果您可以将其实现为参数化存储过程并在数据库中对其进行优化，而不是在运行时通过 LINQ 或 ORM 动态生成 SQL，则值得考虑。通常这会表现得更好。我知道它有点过时，但有时它是最有效的方法。
回答by rhys
If using C# and .NET 3.5, with the addition of MS SQL Server then LINQ to SQL is definitely the way to go. If you are using anything other than that combination, I'd recommend an ORM route, such as nHibernateor Subsonic.
如果使用 C# 和 .NET 3.5，加上 MS SQL Server，那么 LINQ to SQL 绝对是要走的路。如果您使用的不是该组合，我建议您使用 ORM 路线，例如nHibernate或Subsonic。
回答by Wiren
Unless executiontime is really important, I would consider refactoring the business logic that (so often) tends to find its way down to the datalayer and into gazillion-long stored procs. In terms of maintainabillity, editabillity and appendabillity I always try to (as the C# programmer I am) lift code up to the businesslayer.
除非执行时间真的很重要，否则我会考虑重构业务逻辑，这些业务逻辑（经常）趋向于向下到达数据层并进入数以亿计的存储过程。在可维护性、可编辑性和可追加性方面，我总是尝试（作为 C# 程序员）将代码提升到业务层。
Trying to sort out someone elses 8000 line SQL Script is not my favorite task.
试图整理别人的 8000 行 SQL 脚本并不是我最喜欢的任务。
:)
:)
//W
//W
回答by Darrel Miller
I understand the potential of Linq but I have yet to see anyone try and do a Linq query of the complexity that Ben is suggesting
我了解 Linq 的潜力，但我还没有看到有人尝试对 Ben 建议的复杂性进行 Linq 查询
the fairly complex sql statement (~10 joins, >10 sub selects, ~15-25 where conditions and GroupBy's)
相当复杂的 sql 语句（~10 个连接，>10 个子选择，~15-25 个条件和 GroupBy）
Does anyone have examples of large Linq queries, and any commentary on their manageability?
有没有人有大型 Linq 查询的示例，以及对其可管理性的任何评论？
回答by KristoferA
Linq to SQL together with System.Linq.Dynamic brings some nice possibilities.
Linq to SQL 与 System.Linq.Dynamic 一起带来了一些不错的可能性。
I have posted a couple of sample code snippets here: http://blog.huagati.com/res/index.php/2008/06/23/application-architecture-part-2-data-access-layer-dynamic-linq
我在这里发布了几个示例代码片段：http: //blog.huagati.com/res/index.php/2008/06/23/application-architecture-part-2-data-access-layer-dynamic-linq
...and here: http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/717004553931?r=777003863931#777003863931
...这里：http: //episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/717004553931?r=777003863931#777003863931
回答by Patrick Karcher
I'm coming at this late and have no chance for an upvote, but there's a great solution that I haven't seen considered: A combination of procedure/function with linq-to-object. Or to-xml or to-datatable I suppose.
我这么晚才来，没有机会投票，但是有一个很好的解决方案，我还没有考虑过：过程/函数与 linq-to-object 的组合。或 to-xml 或 to-datatable 我想。
I've been this in this exact situation, with a massive dynamically built query that was kindof an impressive achievement, but the complexity of which made for an upkeep nightmare. I had so many green comments to help the poor sap who had to come along later and understand it. I was in classic asp so I had few alternatives.
在这种确切的情况下，我一直是这样，使用大量动态构建的查询，这是一项令人印象深刻的成就，但其复杂性使维护成为噩梦。我有这么多绿色评论，以帮助后来不得不一起理解的可怜的sap。我使用的是经典的 asp，所以我几乎没有其他选择。
What I have done since is a combination of function/procedure and linq. Often the total complexityis less than the complexity of trying to do it one place. Pass some of the your criteria to the UDF, which becomes much more manageable. This gives you a manageable and understandable result-set. Apply your remaining distinctions using linq.
从那以后我所做的是function/procedure 和 linq的组合。通常，总的复杂性小于尝试在一个地方完成的复杂性。将您的一些标准传递给 UDF，这将变得更易于管理。这为您提供了一个可管理且易于理解的结果集。使用 linq 应用您剩余的区别。
You can use the advantages of both:
您可以利用两者的优点：
- Reduce the total records as much as possible on the server; get as many crazy joins taken care of on the server. Databases are goodat this stuff.
- Linq (to object etc.) isn't as powerful but is great at expressing complex criteria; so use it for various possible distinctions that add complexity to the code but that the db wouldn't be much better at handling. Operating on a reduced, normalized result set, linq can express complixity without much performance penalty.
- 尽可能减少服务器上的总记录；在服务器上处理尽可能多的疯狂加入。数据库擅长这些东西。
- Linq（到对象等）不是那么强大，但在表达复杂标准方面非常出色；所以将它用于各种可能的区别，这些区别会增加代码的复杂性，但数据库在处理方面不会更好。在简化的、标准化的结果集上运行，linq 可以表达复杂性而不会造成太大的性能损失。
How to decide which criteria to handle in the db and which with linq? Use your judgement. If you can efficiently handle complex db queries, you can handle this. Part art, part science.
如何决定在 db 中处理哪些标准以及在 linq 中处理哪些标准？使用你的判断。如果您可以有效地处理复杂的数据库查询，则可以处理此问题。部分艺术，部分科学。
