NHibernate在一对多集合中生成奇数sql

本文关键字：sql 一对多 集合 NHibernate | 更新日期: 2023-09-27 18:31:37

给定以下配置：

<class name="CategoryPerm" table="CUS_BCDynamicContent_CategoryPerms" lazy="true" >
    <id name="Id">
        <generator class="guid.comb" />
    </id>
    <many-to-one class="BCDynamicContent.Entities.Category, BCDynamicContent, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" name="Category">
        <column name="CategoryID" sql-type="uniqueidentifier" not-null="true" />
    </many-to-one>
    <property name="PortalGroupId">
        <column name="PortalGroupID" sql-type="uniqueidentifier" />
    </property>
    <bag name="GroupMemberships" cascade="none">
        <key column="ParentPrincipalID" property-ref="PortalGroupId" />
        <one-to-many class="BCDynamicContent.Entities.GroupMembership, BCDynamicContent, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null" />
    </bag>
</class>
<class name="GroupMembership" table="FWK_GroupMembership" lazy="true" mutable="false" >
    <id name="Id">
    </id>
    <property name="ParentPrincipalId">
        <column name="ParentPrincipalID" sql-type="uniqueidentifier" not-null="true" />
    </property>
    <property name="MemberPrincipalId">
        <column name="MemberPrincipalID" sql-type="uniqueidentifier" not-null="true" />
    </property>
</class>

和类：

public class Entity
{
    public virtual Guid Id { get; set; }
}

public class CategoryPerm : Entity
{
    public virtual Category Category { get; set; }
    public virtual Guid PortalGroupId { get; set; }
    public virtual IList<GroupMembership> GroupMemberships { get; set; }
    public CategoryPerm()
    {
        GroupMemberships = new List<GroupMembership>();
    }
}

public class GroupMembership : Entity
{
    public virtual Guid ParentPrincipalId { get; private set; }
    public virtual Guid MemberPrincipalId { get; private set; }
}

以及以下 linq 查询：

var categories = (from x in nhs.Linq<CategoryPerm>() where x.GroupMemberships.Any(y => y.MemberPrincipalId == PortalUser.Current.ID) select x ).ToList() ;

为什么NH提出的sql看起来像这样：（问题是"存在（）"部分"this_0_。Id = ParentPrincipalID" <-- 不应该这样做）

SELECT this_.Id            as Id290_0_,
       this_.CategoryID    as CategoryID290_0_,
       this_.PortalGroupID as PortalGr3_290_0_
FROM   CUS_BCDynamicContent_CategoryPerms this_
WHERE  this_.Id in (SELECT this_0_.Id as y0_
                    FROM   CUS_BCDynamicContent_CategoryPerms this_0_
                           inner join FWK_GroupMembership y1_
                             on this_0_.PortalGroupID = y1_.ParentPrincipalID
                    WHERE  exists(select 1
                                  from   FWK_GroupMembership
                                  where  this_0_.Id = ParentPrincipalID)
                           and y1_.MemberPrincipalID = 'b32f5d6c-490c-45e9-874a-c4d27d2862b8' /* @p0 */)

NHibernate在一对多集合中生成奇数sql

看起来这是 NH 2.x Linq 提供程序中的一个错误。 我最终去了ICriteria，现在它就像一个魅力。

nhs.CreateCriteria<CategoryPerm>("a").CreateCriteria("a.Category", "b").CreateCriteria("a.GroupMemberships", "c").Add(
                Restrictions.Eq("c.MemberPrincipalId", PortalUser.Current.ID.AsGuid)).List<CategoryPerm>();
相关文章：