使用NHibernate Linq查询自引用联接 [英] Querying a self referencing join with NHibernate Linq

问题描述

在我的应用程序中，我有一个Category域对象.类别具有属性父类别(类别类型)".

In my application I have a Category domain object. Category has a property Parent (of type category).

所以在我的NHibernate映射中，我有:

So in my NHibernate mapping I have:

    <many-to-one name="Parent" column="ParentID"/>

在切换到NHibernate之前，我在域模型上具有ParentId属性(映射到相应的数据库列).

Before I switched to NHibernate I had the ParentId property on my domain model (mapped to the corresponding database column).

这使查询所有顶级类别(ParentID = 0)变得容易:

This made it easy to query for say all top level categories (ParentID = 0):

where(c => c.ParentId == 0)

但是，此后我已经从域模型中删除了ParentId属性(由于NHibernate)，所以现在我必须像下面这样执行相同的查询(使用NHibernate.Linq):

However, I have since removed the ParentId property from my domain model (because of NHibernate) so I now have to do the same query (using NHibernate.Linq) like so:

        public IList<Category> GetCategories(int parentId) {
        if (parentId == 0)
            return _catalogRepository.Categories.Where(x => x.Parent == null).ToList();
        else
            return _catalogRepository.Categories.Where(x => x.Parent.Id == parentId).ToList();
    }

我看到的真正影响是生成的SQL. NHibernate而不是简单的从parentid = 0的类别中选择x，y，z"，而是生成左外部联接:

The real impact that I can see, is the SQL generated. Instead of a simple 'select x,y,z from categories where parentid = 0' NHibernate generates a left outer join:

SELECT this_.CategoryId    as CategoryId4_1_,
   this_.ParentID      as ParentID4_1_,
   this_.Name          as Name4_1_,
   this_.Slug          as Slug4_1_,
   parent1_.CategoryId as CategoryId4_0_,
   parent1_.ParentID   as ParentID4_0_,
   parent1_.Name       as Name4_0_,
   parent1_.Slug       as Slug4_0_

从类别分类this_ 左外部联接类别parent1_ 在this_.ParentID = parent1_.CategoryId上 在哪里this_.ParentID为空

FROM Categories this_ left outer join Categories parent1_ on this_.ParentID = parent1_.CategoryId WHERE this_.ParentID is null

效率似乎没有以前差很多.

Which doesn't seems much less efficient that what I had before.

查询这些自引用联接是否有更好的方法，因为这个原因很容易将ParentID放回我的域模型中.

Is there a better way of querying these self referencing joins as it's very tempting to drop the ParentID back onto my domain model for this reason.

谢谢

推荐答案

我的第一个反应是:是的-是这样的.不做任何事情，NHibernate总是尝试加载整个元素-这意味着它也加载了父元素.这真的是性能问题还是仅仅是美学问题? 而且我认为添加父ID不会对您有所帮助-因为它仍然会加载父项.

My first reaction would've been: yes - this is the way it is. Without doing anything NHibernate always tries to load the whole element - and this means that it loads the parent element too. Is this really a performance problem or is it just an aesthetical problem ? And I don't think that including the parent id would help you - because it still would load the parent item with it.

但是，如果您真的想优化此内容，请阅读以下文章 http://www.javalobby.org/java/forums/t20533.html .它是关于Hibernate的，但是它为您提供了有关如何处理此问题的一些想法以及针对您的问题的(可能的)解决方案.

But if you would really like to optimize this read the following article http://www.javalobby.org/java/forums/t20533.html. It is about Hibernate, but it gives you some ideas about how to handle this and a (possible) solution for your problem.

这篇关于使用NHibernate Linq查询自引用联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！