Queryable Repository

Say we have a Post domain class, with properties like Header, details, and PostDate etc.

We want do many different kind of query/search from task/service layer, but we don’t have to change both task and repository for every new type of search requirement.

It would be nice if PostRepository can take the predict to do query.

        public IQueryable<Post> SearchBy( Expression<Func<Post, bool>> predict)
             return Session.Query<Post>().Where(predict);

For unit test like this:

        public void should_do_search_for_keyword_and_date_range()
            var startDate = new DateTime(2012, 1, 1);
            var endDate = new DateTime(2011, 1, 1);
            var results = _repository.SearchBy(post => post.Header.Contains("asdf") && post.PostDate >= startDate && post.PostDate <= endDate);
            Assert.That(results, Is.Empty);

The generated sql:

NHibernate: select post0_.Post_ID as Post1_0_, post0_.Header as Header0_, post0_.Sub_Header as Sub3_0_, post0_.Description as Descript4_0_, post0_.Is_News_Clip as Is5_0_, post0_.Image_Name as Image6_0_, post0_.More_Url as More7_0_, post0_.Post_Date as Post8_0_, post0_.S_Date as S9_0_, post0_.C_Date as C10_0_, post0_.SU_ID as SU11_0_, post0_.CU_ID as CU12_0_ from Post_TAB post0_ where (post0_.Header like ('%'+@p0+'%')) and post0_.Post_Date>=@p1 and post0_.Post_Date<=@p2;@p0 = 'asdf' [Type: String (4000)], @p1 = 2012-01-01 12:00:00 AM [Type: DateTime (0)], @p2 = 2011-01-01 12:00:00 AM [Type: DateTime (0)]

The the task/service can play criteria in whatever way they want, while the repository won’t care.

In fact, the queryable itself is very flexible, we can just create a Query method in repository class to remove the predict expression from method:

        public IQueryable<Post> Query()
            return Session.Query<Post>();
public IEnumerable<PostedItemDto> FindPostsByCriteria(PostCriteria criteria)
 IQueryable<Post> results = _postRepository.Query();

 if (!(string.IsNullOrEmpty(criteria.Keyword)))
 results = results.Where(post =>
 || post.SubHeader.Contains(criteria.Keyword)
 || post.Description.Contains(criteria.Keyword))

 if ( criteria.FromDate != null ) results = results.Where(post => post.PostDate >= criteria.FromDate);

 if ( criteria.ToDate != null ) results = results.Where(post => post.PostDate <= criteria.ToDate);

 return new PostTranslator().Map(results.ToList());

Unfortunately, QueryOver in new NH can not do this yet. To achieve the same result, repository has to write in this way:

            return Session.QueryOver<Post>()
                .WhereRestrictionOn(post => post.Header).IsLike("%asdk%")
                .WhereRestrictionOn(post => post.PostDate).IsBetween(fromDate).And(endDate)

It looks clear, but we have to maintain it for criteria structure changes.

Even LinQ is not strong enough as this post indicated, but it has it’s advantage.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s