Tarun Kohli

NHibernate, Linq and Lazy Collections

For the past few months we have been busy building a services framework for one of our clients so that they can build all of their enterprise web services without ever having to worry about the cross cutting concerns and persistence for their application.

All the cross cutting concerns like Logging, Exception Handling and Security has been built using Spring.NET and the  abstraction over persistence layer has been built on NHibernate. The abstraction handles getting the session from a configured session factory and manages the basic CRUD operations. One of the things that we support in this abstraction is the ability to page the results i.e. get data in chunks.

We have been using the regular ICriteria and IQuery approaches to get the page data but one of our team members recently discovered the simplicity and power of Linq to get paged data with a single statement. Pretty Cool! But, it comes with certain performance penalties. More on this later.

First, below is our code to do pagination via NHibernate. In this code, we are fetching Resources for a given Member.

        public List<Resource> GetResources(int userId, int startRowNumber, int endRowNumber)
        {
            using (new PerfTimer("Query based MemberRepository.GetResources()"))
            {
                Member member = GetByUserId(userId);
                IQuery query = Session.CreateFilter(member.Resources, "");
                query.SetFirstResult(startRowNumber).SetMaxResults(endRowNumber - startRowNumber);
                return query.List<Resource>().ToList<Resource>();
            }
 
        }

We initialize the root object, Member, and get its children, Resources, off of it. It’s pretty standard but now see the below code for Linq, which is extremely clean –

        public List<Resource> GetResources(int userId, int startRowNumber, int endRowNumber)
        {
            using (new PerfTimer("Linq based MemberRepository.GetResources()"))
            {
                Member member = GetByUserId(userId);
                return member.Resources.Skip(startRowNumber).Take(endRowNumber - startRowNumber).ToList();
            }
        }

It’s beautiful but it comes with performance cost. Here is the SQL it generates –

exec sp_executesql N’SELECT resources0_.FK_UserChildId as FK1_1_, resources0_.FK_ResourceId as FK2_1_, resource1_.PK_ResourceId as PK1_0_0_, resource1_.FK_ContentChildId as FK2_0_0_, resource1_.Title as Title0_0_, resource1_.Url as Url0_0_, resource1_.CreationDate as Creation5_0_0_ FROM workspace.tbl_ResourceUserAssoc resources0_ left outer join workspace.tbl_Resource resource1_ on resources0_.FK_ResourceId=resource1_.PK_ResourceId WHERE resources0_.FK_UserChildId=@p0′,N’@p0 int’,@p0=1

Compared to what IQuery generates

exec sp_executesql N’SELECT TOP 3 PK1_0_, FK2_0_, Title0_, Url0_, Creation5_0_ FROM (select resource0_.PK_ResourceId as PK1_0_, resource0_.FK_ContentChildId as FK2_0_, resource0_.Title as Title0_, resource0_.Url as Url0_, resource0_.CreationDate as Creation5_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from workspace.tbl_ResourceUserAssoc resources1_ inner join workspace.tbl_Resource resource0_ on resources1_.FK_ResourceId=resource0_.PK_ResourceId where resources1_.FK_UserChildId = @p0) as query WHERE query.__hibernate_sort_row > 4 ORDER BY query.__hibernate_sort_row’,N’@p0 int’,@p0=1

IQuery only gets the rows that are asked for whereas Linq gets everything and then skips the rows in memory! This is the unit test that I had setup to test the behavior

        [TestMethod]
        public void TestPagedResourcesViaMember()
        {
            MemberRepository memberRepository = new MemberRepository();
            List<Resource> resources = memberRepository.GetResources(1, 4, 7);
            Assert.IsNotNull(resources);
            Assert.IsTrue(resources.Count >= 1);
        }

Thus, the best approach to stick to IQuery or ICriteria to do pagination and keep away from Linq, for now.

 

Related Articles

#Tech

NHibernate, Linq and Lazy Collections

For the past few months we have been busy building a services framework for one of our clients so that they can build all of their enterprise web services without ever having to worry about the cross cutting concerns and... Read more
#Tech

Page Redirects using Spring.NET

Who is responsible for page redirects in ASPNET MVP – The View or the Presenter None of the above, it is you :) On a serious note, it is the View as one shouldn’t pollute the Presenter with the page navigation... Read more