NHibernate 查询的复杂条件：Linq 还是 ICriteria？
我在基本存储库模型中使用 NH2.1 和 FluentNH 配置和 Linq2NH。我正在尝试设置一个查询来消化 winform 中的各种表单字段。标准几乎是一系列文本框，每个文本框都有一个复选框，说明标准是否包含通配符（仅支持星号），因此逻辑是一系列：
...
&& Field1.IsNullOrBlank() || Field1 == Criteria1 || (Criteria1IsWildCard && Regex.Match(Field1, Criteria1.Replace("*",".*")))
&& Field2.IsNullOrBlank() || Field2 == Criteria2 || (Criteria2IsWildCard && Regex.Match(Field2, Criteria2.Replace("*",".*")))
现在，这对于 Linq2Objects 来说非常好，但它不会由于多种原因（自定义扩展方法、正则表达式检查等），Linq2NH 查询取得了很大的进展。
我创建了一个不同的搜索页面，该页面必须消化类似的数据，但是该查询是针对不同的存储库数据源执行的，该数据源需要将查询作为字符串 (SalesForce SOQL)。由于 NHibernate 有许多更复杂的、经过编译器检查的工具可用，因此我更愿意将 HQL 作为最后的选择。
其他相关信息：
- 检索和缓存整个表的内容以使用 Linq2Objects 过滤结果几乎是不可行的（该表大约有 15k 条记录），但任何不必执行此操作的选项都是首选。
- 用户需要拥有尽可能多的通配符；因此，使用 StartsWith()/EndsWith()/Contains() 解决方法是不可行的。
那么，问题是，如何设置它来针对 NHibernate 执行？
I'm using NH2.1 with FluentNH config and Linq2NH in a basic Repository model. I am attempting to set up a query that will digest various form fields from a winform. The criteria is pretty much a series of textboxes, each with a checkbox stating whether the criteria contains wildcards (only asterisks are supported), so the logic is a series of:
...
&& Field1.IsNullOrBlank() || Field1 == Criteria1 || (Criteria1IsWildCard && Regex.Match(Field1, Criteria1.Replace("*",".*")))
&& Field2.IsNullOrBlank() || Field2 == Criteria2 || (Criteria2IsWildCard && Regex.Match(Field2, Criteria2.Replace("*",".*")))
Now, this would be just great for Linq2Objects, however it's not going to get very far in a Linq2NH query for a number of reasons (custom extension methods, Regex checking, etc).
I created a different search page that has to digest similar data, however that query is executed against a different Repository datasource which requires the query as a string (SalesForce SOQL). Since NHibernate has so many more sophisticated, compiler-checked tools available, I would prefer HQL to be my last option.
Other relevant info:
- Retrieving and caching the entire table's contents in order to filter the results using Linq2Objects is barely feasible (the table is about 15k records), but any option that doesn't have to do this is much preferred.
- The user needs to have as many wildcards as necessary; therefore, it will be infeasible to use the StartsWith()/EndsWith()/Contains() workaround.
So, the question is, how would you set this up to execute against NHibernate?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
HQL、ICriteria、Linq-to-NH和任何其他 API 最终都会转换为 SQL。因此，由于 MS-SQL Server 和其他服务器不直接支持通配符，因此您不能在条件中使用通配符。HQL,ICriteria,Linq-to-NHand any other API are translated to SQL finally. So as MS-SQL Server and others does not support wild characters directly, you can not use wild characters in your criterias.我得到的答案是使用 StartsWith/EndsWith 方法来获取 LIKE 操作，然后用 % 替换任何星号。如果条件以星号开头，则使用 EndsWith，否则使用 StartsWith。这会将“abc*”转换为“abc%%”，“*abc”转换为“%%abc”，“*abc*”转换为“%%abc%”，所有这些都与非重复的 %% 通配符对应项评估相同在 LIKE 子句中。唯一无法按指定方式工作的情况是输入既不以通配符开头也不以通配符结尾的条件；如果将“a*c”与 abc、adc 和 abcde 进行比较，它将计算为“a%c%”而不是“a%c”，并且会返回所有这些而不是排除 abcde。足够接近我的目的了。
The answer I arrived at is to use the StartsWith/EndsWith methods to get a LIKE operation, then also replace any asterisks with %. If the criteria begins with an asterisk, use EndsWith, otherwise use StartsWith. This would turn "abc*" into "abc%%", "*abc" into "%%abc", and "*abc*" into "%%abc%" which all evaluate identically to non-duplicated %% wildcarded counterparts in a LIKE clause. The only scenario in which it would not work as specified is if entering criteria that neither begins nor ends with the wildcard; if comparing "a*c" against abc, adc, and abcde, it would evaluate to "a%c%" instead of "a%c", and would return all of these instead of excluding abcde. Close enough for my purposes.