NHibernate ICriteria and filters

I have a project where I need to access tables with millions of rows, the project was born without nhibernate so the database was not designed with ORM in mind. I decided to improve performances with an Indexed view that materialized a join between five tables, wrapped with another view that does a left outer joins with other 3 tables. Then I created an entity called XXXDto, and mapped with nhibernate, with update=false to prevent updating and pointed it to this view.

Everything runs fine, but now I have a little new feature to implement; that entity needs another property called Bookmark, and the value is specific of each user. In the software the user can search the aforementioned entity with some criteria, then he bookmarks the ones he likes most, and the software must signal bookmarked ones with a different image in the web ui. The problem is that bookmark table was already present in the database and has this schema.

This table works this way, if a user put a bookmark on an entity, a record with username and link_id (the foreign key to other table) is inserted, if the bookmark is removed the record is removed. Now I want to avoid the N+1 select, so I decided not to issue another query for each one of the original links, moreover I used ICriteria to recover entities, since the user can set complex filter through the UI and ICriteria makes really simple to compose the query.

Thanks to nhibernate I can use a formula in the mapping, but I need to put a condition on that formula, here is how I solved the problem.

1
2
3
4

<property 
name="Bookmark" 
formula="(select count(*) from Bookmark B where B.bookmark_link_id = Id and B.bookmark_user = :UserContext.UserName)" />
  

The formula is based on the fact that Bookmark is a boolean field, so if I issue a select count(*) everything is ok because if the bookmark is present the value is 1 if it is not present the value is 0, suitable for a boolean field. The condition is that bookmark_link_id = Id where id is the id column of the entity, and the user condition is B.Bookmark_User = :UserContext.UserName. This instruction uses a filter, a global parameter supported by nhibernate, that permits me to put parameters in mappings. Now I need to define this filter in a mapping.

1
2
3
4
5

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <filter-def name='UserContext'>
        <filter-param name='UserName' type='System.String'/>
    </filter-def>
</hibernate-mapping>

Now that the filter is defined I need to set the appropriate value in code before issuing the query. Since I wrapped the query in a Query object specific of that project I have a similar code that translate my Query object to a ICriteria + Session in nhibernate

1
2
3
4
5
6
7
8

foreach (Query.Filter filter in myQuery.Filters)
{
    IFilter nhibFilter = _session.EnableFilter(filter.Name);
    foreach (KeyValuePair<string, object> parameter in filter.Parameters)
    {
        nhibFilter.SetParameter(parameter.Key, parameter.Value);
    }
}

To create a filter you can simply call EnableFilter() method on a session, then you set specific filter on the IFilter object returned from the EnableFilter(). Here is the query generated by this mapping.

1
2
3

select top 20 this_.Id as Id31_0_, 
this_.Name,...., 
(select count(*) from Bookmark B where B.bookmark_link_id = this_.Id and B.bookmark_user = @p0) as formula1_0_ FRom..... 

The only drawback is that in formula mapping you must use the sql name of the column involved (in that example I need to use bookmark_link_id = Id where id is the real column name that contains id), but the query is correct and everything works perfectly.

alk.

Tags: NHibernate Formula