NHibernate - How to write this Query: Select parents & find child for each parent that matches a condition

Question

OK, first my simple Domain Model is 2 classes with a one-to-many relationship, a simple Parent -> child relationship. A 'Tweet' has one or more 'Votes', but each Vote belongs to just one Tweets etc.

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

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

public class Vote
{
    public virtual long Id { get; set; }
    public virtual long TwitterUserId { get; set; }
    public virtual DateTime VotedDate { get; set; }

    public virtual Tweet Tweet { get; set; }
}

I'm trying to write a query in either HQL, ICriteria or NHibernate LINQ, that selects all Tweets, but also add two columns that selects:

• A count of the number of votes, and...
• Whether a particular user has voted for that tweet, based on a particular TwitterUserId

With the two extra columns, I'm not expecting to get Tweet domain object back, and would probably need to run a Report query, that's OK. But I'm struggling to figure out how to write this query.

I know how to write this as a Stored Procedure, or using LINQ 2 SQL. If it helps I will express this as a LINQ to SQL query.

long userId = 123;

var tweets = from t in dataContext.Tweets
             where t.Application == app
             orderby t.PostedDate desc
             select new TweetReport()
             {
                 Id = t.Id,
                 Username = t.Username,
                 Message = t.Message,
                 TotalVotes = t.Votes.Count(),
                 HasVoted = t.Votes.Any(v => v.TwitterUserId == userId)
             };

I know this will work in LINQ 2 SQL, and generate reasonably efficient T-SQL, but I can't figure out how to write this in NHibernate.

---

Update: I tried running the above LINQ query in NHibernate by using the NHibernate LINQ provider built for NHibernate v2, eg:

var tweets = from t in Session.Linq<Tweet>()
             where (snip)

But it didn't work. Has LINQ support in Nhibernate 3.0 improved? I'm a bit reluctant to use version 3.0 because it's still alpha, but if this will work, then I might give it a go.

---

Update 2: Thanks to Diego Mijelshon's suggestion, I upgraded to NHibernate 3.0 alpha 2 and wrote the query in LINQ:

var tweets = from t in Session.Query<Tweet>()
             where t.App == app
             orderby t.PostedDate descending
             select t;

int totalRecords = tweets.Count();

var pagedTweets = (from t in tweets
                   select new TweetReport()
                   {
                       Id = t.Id,
                       TwitterId = t.TweetId,
                       Username = t.Username,
                       ProfileImageUrl = t.ImageUrl,
                       Message = t.Message,
                       DatePosted = t.PostedDate,
                       DeviceName = t.Device.Name,
                       DeviceUrl = t.Device.Url,
                       TotalVotes = t.Votes.Count(),
                       HasVoted = t.Votes.Any(v => v.TwitterUserId == userId)
                   })
                   .Skip(startIndex)
                   .Take(recordsPerPage)
                   .ToList();

return new PagedList<TweetReport>(pagedTweets,
    recordsPerPage, pageNumber, totalRecords);

Answer 1

It's exactly the same with NHibernate 3; just replace dataContext.Tweets with session.Query<Tweet>.

Alternatively, you can create a context class that exposes session.Query<Tweet> as an IQueryable<Tweet> Tweets property, then the code would be 100% unchanged.