CODEWITHSUNDEEP
nhibernatefluent-nhibernatenhibernate-mapping
YodasMyDad
YodasMyDad

Reputation: 9485

Fluent nHibernate Getting HasMany Items In Single Query

I have a Topic map which has many posts in it i.e… (At the bottom HasMany(x => x.Posts))

 public TopicMap()
{
    Cache.ReadWrite().IncludeAll();

    Id(x => x.Id);
    Map(x => x.Name);
    *lots of other normal maps*

    References(x => x.Category).Column("Category_Id");
    References(x => x.User).Column("MembershipUser_Id");
    References(x => x.LastPost).Column("Post_Id").Nullable();

    HasMany(x => x.Posts)
        .Cascade.AllDeleteOrphan().KeyColumn("Topic_Id")
        .Inverse();

*And a few other HasManys*
}

I have written a query which gets the latest paged topics, loops through and displays data and some posts data (Like the count of child posts etc..) . Here is the query

    public PagedList<Topic> GetRecentTopics(int pageIndex, int pageSize, int amountToTake)
    {
        // Get a delayed row count
        var rowCount = Session.QueryOver<Topic>()
                        .Select(Projections.RowCount())
                        .Cacheable().CacheMode(CacheMode.Normal)
                        .FutureValue<int>();

        var results = Session.QueryOver<Topic>()
                            .OrderBy(x => x.CreateDate).Desc
                            .Skip((pageIndex - 1) * pageSize)
                            .Take(pageSize)
                            .Cacheable().CacheMode(CacheMode.Normal)
                            .Future<Topic>().ToList();

        var total = rowCount.Value;
        if (total > amountToTake)
        {
            total = amountToTake;
        }

        // Return a paged list
        return new PagedList<Topic>(results, pageIndex, pageSize, total);
    }

When I use SQLProfiler on this, as I loop over the topics is does a db hit to grab all Posts from the parent topic. So if I have 10 topics, I get 10 DB hits as it grabs the posts.

Can I change this query to grab the posts as well in a single query? I guess some sort of Join?

Upvotes: 1

Views: 4295

Answers (2)

Diego Mijelshon
Diego Mijelshon

Reputation: 52753

In my opinion, the best way is defining a reasonable batch-size for the collection (rule of thumb: your default parent page size)

That way, after getting the parent items, you'll get a single query for each child collection type you iterate.

Upvotes: 0

Miroslav Popovic
Miroslav Popovic

Reputation: 12128

You can define eager fetching using Fetch.xxx on your HasMany property mapping. Available options are Fetch.Join(), Fetch.Select() and Fetch.SubSelect(). More info on each type of fetching can be found on NHibernate's documentation.

HasMany(x => x.Posts)
    .Cascade.AllDeleteOrphan().KeyColumn("Topic_Id")
    .Fetch.Join()
    .Inverse();

Upvotes: 1

Related Questions