NHibernate-从值类型的集合中查询(非实体)以解决选择N + 1 [英] NHibernate - Querying from a collection of Value Types (non-Entity) to solve Select N+1

问题描述

我有一个代表Twitter推文的实体，如下所示:

I have an entity that represents a Tweet from Twitter like so:

public class Tweet
{
    public virtual long Id { get; set; }
    public virtual string Username { get; set; }
    public virtual string Message { get; set; }

    // other properties (snip)...

    public virtual ISet<long> VoterIds { get; protected set; }
}

我正在尝试在NHibernate中运行查询，该查询选择带有附加列的推文列表，该列用于指示特定用户是否已按其UserId为每个推文投票.当我为Tweet投票时，它存储在上方的"VoterIds"集合中.

I'm trying to run a query in NHibernate that selects a list of tweets with an additional column that denotes whether a particular user by their UserId has voted for each Tweet. When I user votes for a Tweet, it's stored in the 'VoterIds' collection above.

我正在使用值类型的集合，因为我只对Twitter UserId真正感兴趣，以确定用户是否已经对特定推文进行了投票.因此，为什么它是一个ISet<long>而不是ISet<Vote>

I'm using a collection of value Types for this, since I'm only really interested in the Twitter UserId to determine if a user has already voted for a particular tweet. Hence why it's an ISet<long> instead of ISet<Vote>

我正在尝试像这样使用投影:

I'm trying to use projections like so:

long userId = 123;

IList<TweetReport> tweets = Session.CreateCriteria<Tweet>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Id(), "Id")
        .Add(Projections.Property("Username"), "Username")
        .Add(Projections.Property("Message"), "Message")
        .Add(Projections.Conditional( //---- WHAT GOES HERE!!??
    .SetResultTransformer(Transformers.AliasToBean<TweetReport>())
    .List<TweetReport>();

我认为正确的方法是使用Projections.Conditional，但是我不确定如何使用它.有人可以帮我填写上面代码中的//---- WHAT GOES HERE!!??位.

I thought the correct method was to use Projections.Conditional, but I'm not sure how to use it. Can someone help me fill in the //---- WHAT GOES HERE!!?? bit in the above code.

我尝试使用Expressions.In:

I tried using Expressions.In:

.Add(Projections.Conditional(Expressions.In("VoterIds", new object[] { userId }),
    Projections.Constant(true), Projections.Constant(false)))

...但是它给了我一个不能使用带有InExpression的集合"错误.请帮忙！

...but it gave me a 'Cannot use collections with InExpression' error. Please help!

更新:我开始认为根本不可能查询值类型的集合，而我应该使用像这样的成熟实体:

Update: I'm beginning to think that it isn't possible to query collections of value types at all, and that I should be using a full-blown entity like so:

public virtual ISet<Vote> Votes { get; protected set; }

...会是这种情况吗?

...would this be the case?

推荐答案

您可以这样做，但是修改域模型以解决NHibernate的局限性会让您感到痛苦.可以使用HQL查询值集合，但是ICriteria对于使用逻辑构造查询确实非常方便.我知道如何使用ICriteria查询值集合的唯一方法是使用自定义SQL.这也很痛苦，并且将您的代码绑定到数据库(！)，但对我而言，这是三个弊端中的较小者.我的理由是，ICriteria最终将允许这种查询，并且以后可以减轻这种痛苦.

You can do that, but modifying the domain model to get around a limitation of NHibernate is painful to the soul. It's possible to query value collections with HQL, but ICriteria is really handy for constructing queries with logic. The only way I know how to query value collections using ICriteria is with custom SQL. This is painful also, and ties your code to your database (!), but to me it's the lesser of the three evils. My rationale is that ICriteria will eventually allow this sort of query and the pain can be refactored out later.

诀窍是在自定义SQL中使用子查询，以便可以联接到收集表.使用不会踩NHibernate别名的表别名也是一个好主意(在这种情况下为custom_sql_t_v).并注意{alias}和? NHibernate将换出的占位符.

The trick is to use a subquery in the custom SQL so that a join to the collection table is possible. Using a table alias that won't step on NHibernate aliases is also a good idea (in this case custom_sql_t_v). And note the {alias} and ? placeholders which NHibernate will swap out.

这是一个基于您的Tweet类被映射为这样的假设的示例...

Here's an example based on the assumption your Tweet class is mapped something like this...

<class name="Tweet" table="Tweet">
    <id name="Id" unsaved-value="0">
        <generator class="identity"/>
    </id>
    <version name="Version" unsaved-value="0"/>
    <property name="UserName"/>
    <property name="Message"/>
    <set name="Votes" table="Tweet_Votes">
        <key column="Tweet"/>
        <element type="Int64" column="Vote"/>
    </set>
</class>

这是使用T-SQL(即Microsoft SQL Server)的修改后的查询...

Here's the modified query using T-SQL (i.e. Microsoft SQL Server)...

IList<TweetReport> tweets = Session.CreateCriteria<Tweet>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Id(), "Id")
        .Add(Projections.Property("UserName"), "UserName")
        .Add(Projections.Property("Message"), "Message")
        .Add(Projections.Conditional(
            Expression.Sql(
                "EXISTS (SELECT 1 FROM [Tweet_Votes] custom_sql_t_v WHERE custom_sql_t_v.[Tweet] = {alias}.[Id] AND custom_sql_t_v.[Vote] = ?)",
                userId,
                NHibernateUtil.Int64),
            Projections.Constant(true),
            Projections.Constant(false)), "DidVote"))
    .SetResultTransformer(Transformers.AliasToBean<TweetReport>())
    .List<TweetReport>();

NHibernate(我使用NHibernate 2.1.2.4000)生成的最终SQL看起来像这样...

The final SQL generated by NHibernate (I used NHibernate 2.1.2.4000) looks like this...

exec sp_executesql N'SELECT this_.Id as y0_, this_.UserName as y1_, this_.Message as y2_, (case when EXISTS (SELECT 1 FROM [Tweet_Votes] custom_sql_t_v WHERE custom_sql_t_v.[Tweet] = this_.[Id] AND custom_sql_t_v.[Vote] = @p0) then @p1 else @p2 end) as y3_ FROM Tweet this_',N'@p0 bigint,@p1 char(1),@p2 char(1)',@p0=123,@p1='Y',@p2='N'

所有这一切的好处是，可以对字符串集合进行LIKE-我认为HQL无法做到这一点.

The upside of all this is that doing a LIKE against a string collection is possible -- something that I don't think can be done with HQL.

这篇关于NHibernate-从值类型的集合中查询(非实体)以解决选择N + 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！