Property Mapping based on dynamic criteria at runtime

Question

Hi All,

I have been sifting through pages on Google looking for the answer to no avail, however I think I am just phrasing the question incorrectly.

The scenario is as follows: I have an entity which users are able to either vote for or against. For arguments sake lets call the entity a business.

I would like to have a property on my business class which indicates how the currently logged on user voted for that particular business. i.e. I would like to show a green tick where the user for up and a red cross where the user voted down.

In SQL this is straight forward, I can create a SQL function which does this resolution based on a parameter ":userid" more specifically which can be passed into the proc. i.e.

   SELECT
      BusinessId,
      CreatedOn,
      Username,
      [Content].ResolveBusinessVoteIndicator(:userid, P.BusinessId) AS VoteIndicator
   FROM 
      Content.Business P

The main issue here is that I have to create an entity which maps to a stored procedure result. Which is bad because now I cannot use HQL or Criteria, which would be the methods of choice.

Another option would be to do the query and then simply iterate through the result and set that property, which would work if you were looking at one business at a time, but I need to display a large list of businesses at one time.

So I guess the question is... Is there way to map a property using either HQL or Criteria where that mapping is based on a formula which requires input at runtime? Similar to passing in parameters to a stored procedure and mapping out the results.

Answer 1

you're really talking about a ViewModel, in this situation. you shouldn't have that property on the Business entity, but should be creating a view model that represents the business entity with the flag that you are talking about.

if you really want to do this with NHibernate, create a View in your database and map the view model to the view. then you can run HQL / Criteria against it like any other object. I've done this a lot and it works well.

You could also use a dataset to get the results from the stored proc, and bind to the dataset. remember that your domain model (the business entity) is not the same as your view model (the business-with-vote class) and probably shouldn't be run from the same data access code.

Greg Young talks about this approach a lot on his blog. for example: http://codebetter.com/blogs/gregyoung/archive/2010/02/15/cqrs-is-more-work-because-of-the-read-model.aspx

Answer 2

Hi Ross, Derick's answer is a sound one, if you want to create a view for each business requirement.

What you want to accomplish can be done with an IFilter. Consider this hbm.xml mapped property on the Business class

<property name="IsVoted" type="boolean" update="false" insert="false" formula="(SELECT [Content].ResolveBusinessVoteIndicator(:loggedOnUser.userId, BusinessId)"/>

and on the class itself

 public bool IsVoted {get;set;}

Note that the BusinessId parameter inside the ResolveBusinessVoteIndicator stored procedure must be the Id column of the Business table and NHibernate will create the appropriate allias for it. now, this depends on the following xml element which can be defined on a separate file (lets say filters.hbm.xml)

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <filter-def name="loggedOnUser">
        <filter-param name="userId" type="Int32"/>
    </filter-def>
</hibernate-mapping>

and at runtime you enable the filter programmatically like

nhSession.EnableFilter("loggedOnUser").SetParameter("userId", GetLoggedOnUserId());

before running your queries. Also, you can use this directly in hql (pseudo-hql: 'from Business b where b.IsVoted = true' to fetch all voted Businesses)