NHibernate - Querying from a collection of Value Types (non-Entity) to solve Select N+1

Question

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; }
}

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.

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>();

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.

I tried using Expressions.In:

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

...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?

Decorticate

Answer 1

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.

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.

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>

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>();

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'

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.

Monjan