CODEWITHSUNDEEP
linqnhibernatelinq-to-nhibernate
Greg
Greg

Reputation: 3150

NHibernate Linq Query - Select Sub Queries

I'm new to NHibernate and not great at Linq, but this is seriously kicking my butt, and I can't find any really clear examples on SO.

I need to get Thread information from the database, but I need to include a subquery that does a count on the number of Posts on a particular thread.

Here is a SQL Statement

Select ID, ThreadName, 
       (Select Count(Posts.ID) From Posts Where ThreadID = Threads.ID) as Replies
From Threads

And Class Structure:

Class Thread
    Property ID as Integer
    Property ThreadName as String
    Property Replies as Integer
End Class

Class Post
    Property ID as Integer
    Property ThreadID as Integer
    Property PostText as String
End Class

Any help would be much appreciated. And bonus points to supply both a LINQ example and a one using the NHibernate syntax.

Upvotes: 1

Views: 1771

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123901

So the query would be like this:

C#

var query =
    from thrs in session.Query<YourNamespace.Thread>() // in C# Thread would need 
    select new YourNamespace.Thread  // precise NS to distinguish System.Threading
    {
        ID = thrs.ID,
        ThreadName = thrs.ThreadName,
        Replies = thrs.Posts.Count()
    };

var list = query.ToList(); // the above statement was executed

VB:

Dim query = From t As Thread In session.Query(Of Thread)()
                 Select New Thread With {
                     .ID = t.ID,
                     .ThreadName= t.ThreadName,
                     .Replies = t.Posts.Count                         
                 }
Dim list as List(of Thread) = query.ToList()

The very important think here is, that the Thread must have a mapping to the collection of Posts

C# 

public class Thread
{
    ...
    // really sorry for C# ... I will learn VB syntax ... 
    public virtual IList<Post> Posts { get; set; }

VB

Public Class Thread
    Public Overridable Property Posts As IList(Of Post)

If this collection of Posts, would be mapped in NHibernate, then the above LINQ syntax will work out of the box

Upvotes: 1

Related Questions