使用 LINQ 使用流畅的 nHibernate 进行搜索，其中属性可能驻留在派生类的许多位置
我有一个类结构如下（伪代码）：
CompanyName (id, name) // Other information about the company
IUsefulSearchField // A field that can appear at any depth in the below
IUsefulSearchField2 // A field that can appear at any depth in the below
BaseClass (id) // Some other values needed in class & This class will be inherited by everything below
Class1: BaseClass (IUsefulSearchField, CompanyName) // Extra values not in the search
Class2: BaseClass (IUsefulSearchField, IList<CompanyName>, IUsefulSearchField2) // Extra values not in the search
Class3: BaseClass (CompanyName, IUsefulSearchField2)
Class4: BaseClass (IList<IUsefulSearchField>)
上面重复了多个分散的搜索字段（它们不能包含在基类中，因为子类中可能有一个或多个）
我需要搜索这些常见的字段和公司名称，我还需要限制 1 个或多个派生类型，例如（伪代码）
SELECT Object.ID, Object.Type, Object.CompanyName, Object.UsefulSearchField1,
null AS Object.UsefuleSearchField2
FROM Class1 UNION ALL (SELECT Object.ID, Object.Type, Object.CompanyName,
Object.UsefulSearchField1, Object.UsefuleSearchField2
FROM Class2 Inner Join IList<RelationshipToGet Companies> ) etc….
WHERE Object.Type in ('Class1', 'Class2', 'Class4') AND CompanyName Like 'Peps%'
AND (UsefulSearchField1 = 'Bond' OR UsefulSearchField1 IS NULL) AND
(UsefulSearchField2 > 1000 OR UsefulSearchField2 IS NULL)
每个表将包含 +10m 行，因此有合理数量的数据可以通过
我的问题是：我应该创建一个非规范化索引视图和我查询的相关搜索对象，返回对象类型和 ID，以便当用户单击搜索结果上的编辑时我可以重建对象 - 或 - 我是否应该针对创建 LINQ 查询各个部分的每个对象放置一个接口 - 我们目前正在这样做，并且事实证明它非常慢，因为它似乎可以优化，以便仅搜索存在的术语（即如果仅选择 Class1，则不需要查询其他类）
非常感谢您花时间阅读，如果需要更多信息，将追加
I have a class structure as follows (pseudo code):
CompanyName (id, name) // Other information about the company
IUsefulSearchField // A field that can appear at any depth in the below
IUsefulSearchField2 // A field that can appear at any depth in the below
BaseClass (id) // Some other values needed in class & This class will be inherited by everything below
Class1: BaseClass (IUsefulSearchField, CompanyName) // Extra values not in the search
Class2: BaseClass (IUsefulSearchField, IList<CompanyName>, IUsefulSearchField2) // Extra values not in the search
Class3: BaseClass (CompanyName, IUsefulSearchField2)
Class4: BaseClass (IList<IUsefulSearchField>)
The above is repeated with multiple Search fields scattered about (they can't be contained in the base class as there may be one or many in the subclass)
I need to search these common fields and company names, I also need to limit by 1 or many derived types e.g. (Pseudo code)
SELECT Object.ID, Object.Type, Object.CompanyName, Object.UsefulSearchField1,
null AS Object.UsefuleSearchField2
FROM Class1 UNION ALL (SELECT Object.ID, Object.Type, Object.CompanyName,
Object.UsefulSearchField1, Object.UsefuleSearchField2
FROM Class2 Inner Join IList<RelationshipToGet Companies> ) etc….
WHERE Object.Type in ('Class1', 'Class2', 'Class4') AND CompanyName Like 'Peps%'
AND (UsefulSearchField1 = 'Bond' OR UsefulSearchField1 IS NULL) AND
(UsefulSearchField2 > 1000 OR UsefulSearchField2 IS NULL)
The tables will contain +10m rows each so there is a reasonable amount of data to get through
My question is: Should I create a denormalised indexed view and a correlated search object which I query, returning the object type and ID so that I can rebuild the object when the user clicks edit on the search results -- OR --
Should I put an interface against each object which creates the individual parts of the LINQ query - we are currently doing this and it is proving to be very slow as it appear to be possible to optimise so that only the terms that are present are search (i.e. if only Class1 is selected the other classes do not need to be queried)
Many thanks for taking the time to read will append if more info needed
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这取决于您维护搜索对象的意愿。就性能而言，索引视图是最佳选择，任何 DBA 都会推荐该解决方案。然而 Lucene.net 是从 java 移植的，至少可以满足您的一些要求。有一些 关于 lucene 和 tekpub 的博客系列 有一个 有关搜索的整集。
就我个人而言，我没有使用 NHibernate Search 来实现任何高级功能，因此我不确定它是否能完全满足您的要求。不过，它可能值得一看。如果您想要我的推荐，那么索引视图。启动并运行真是太快了！
编辑：我会选择全新的 QueryOver api 或 Criteria。如果您使用“NHibernate Contrib”Linq 提供程序，您肯定会遇到一些限制。它只有 80% 的兼容性，但您可以使用 Criteria 和查询结束。如果您阅读 ayendes 帖子，您可以轻松地将其转换为 QueryOver 进行一些编译时检查，而不是魔术字符串（如果这困扰您）。
It depends on your willingness to maintain search objects. Performance wise the indexed view is the best option and any DBA would recommend that solution. However Lucene.net was ported from java to meet at least some of your requirements. There are a few blog series about lucene and tekpub has a whole episode about searching.
Personally I have not used NHibernate Search for anything advanced so I am not sure it will meet your requirements completely. It could be worth having a look at though. If you want my recommendation then the indexed view. It's darn quick to get up and running!
EDIT: I would chose either the brand new QueryOver api or Criteria. You will most certainly run into some limitations if you are using the "NHibernate Contrib" Linq provider. It's just 80% compatible but you can do amazing things with Criteria and QueryOver. If you read ayendes post you can easily convert that to QueryOver for some compile time checking instead of the magic strings if that bothers you.
请务必警惕 nHibernate LINQ 提供程序 v1 - 有限的 Join 支持，以及大量的错误 - 支持许多 LINQ 操作。我们正在等待迁移到 nHIbernate v3，它具有使用表达式树重写的 LINQ。只是一个警告伙伴。
Be very wary of the nHibernate LINQ provider v1 - limited Join support, and a myriad of bugs - many LINQ operations are supported. We're waiting to move to nHIbernate v3 which has a re-written LINQ that uses Expression Trees. Just a warning mate.