How best to retrieve and update these objects in NHibernate?

Question

Hi,

I previously asked a question regarding modeling of a situation with Users, Items, and UserRatings. In my example UserRatings are associated with one User and one Item. A good answer was provided by Nathan Fisher and I've included the model he suggested below.

But I now have a question regarding retrieval of these objects.

The model links the entities by holding references to the entities.My question is, how best do I retrieve a particular UserRating to be updated? In this situation I would have the userID (from the asp.net auth session), and the itemID (from the URL). Also, there could be 1000s of ratings per user or item.

Back in the old school this would be as simple as one update query 'where x = userID and y=itemID. Easy. However the best way to accomplish this in NHibernate using a proper object model is not so clear.

A) I understand that I could create a repository method GetRatingByUserAndItem, and pass it both a User and Item object, which it would do an HQL/criteria query on to retrieve the Rating object. However to do this I assume that I would first need to retrieve User and the Item from the ORM before passing these back to the ORM in the query. I would then get the UserRating object, update it, and then have the ORM persist the changes. This seems ridiculously inefficent to me, compared to the old school method.

B) Maybe I could just new-up the UserRating object, and do a createorupdate type call the ORM (not sure on exact syntax). This would be better, but presumably I would still need to first retrieve the User and Item, which is still pretty inefficient.

C) Perhaps I should just retrieve the User (or the Item) from the ORM, and find the correct UserRating from its UserRatings collection. However, if I do that, how do I make sure that I'm not retrieving all of the UserRatings related to that User (or Item), but just the one related to the specific item and specific user?

D) It occured to me that I could just drop the full-blown references to User and Item from UserRating in the model, and instead have primitive references (UserID and ItemID). This would allow me to do something as simple as the oldschool method. Very tempting, but this just doesn't seem right to me - not very Object Oriented (and surely that's the main reason we are using an ORM in the first place!)

So, can anyone offer some sage advice? Am I on the right track with any of the options above? Or is there a better way that I have not considered?

Thanks in advace for your help! :)

UPDATE:

I've just posted a bounty for this, and understand this better, I would also like to know, using a similar approach, how best to perform the following queries:

• Retrieve all the Items which a user had NOT rated.
• Retrieve the Item(s) and Item rating(s) which the user had rated the lowest.

---

The Model follows below:

public class User 
{
    public virtual int UserId { get; set; }
    public virtual string UserName { get; set; }
    public virtual IList<UserRating> Ratings { get; set; }
}

public class Item 
{
    public virtual int ItemId { get; set; }
    public virtual string ItemName { get; set; }
    public virtual IList<UserRating> Ratings { get; set; }
}
public class UserRating 
{
    public virtual User User { get; set; }
    public virtual Item Item { get; set; }
    public virtual Int32 Rating { get; set; }
}

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Test" namespace="Test" >
    <class name="User">
        <id name="UserId" >
                <generator class="native" />
        </id>
        <property name="UserName" />
        <bag name="Ratings" generic="true" inverse="true" table="UserRating">
                <key column="UserId" />
                <one-to-many class="UserRating"/>
        </bag>
    </class>
    <class name="Item" >
        <id name="ItemId" >
                <generator class="native" />
        </id>
        <property name="ItemName" />
        <bag name="Ratings" generic="true" inverse="true" table="UserRating">
                <key column="ItemId" />
                <one-to-many class="UserRating"/>
        </bag>
    </class>
    <class name="UserRating" >
        <composite-id>
                <key-many-to-one class="User" column="UserId" name="User" />
                <key-many-to-one class="Item" column="ItemId" name="Item" />
        </composite-id>
        <property name="Rating" />
    </class>
</hibernate-mapping>

Answer 1

Normally, when you use an ORM, you want to implement the business logic (say: changing data) object oriented. This requires to load the objects from the database. NH allows you to load them once, and change it without any reference to any database related stuff, but just changing property values.

This said, it is not always as easy as that. Sometimes there are performance reasons which requires other ways to update data.

You could use HQL updates or even SQL updates.

Another, more classical way to accomplish this is to only load UserRatings. This requires to make it an independent entity (it needs an id, avoid the composite id anyway, replcae it with many-to-one references). Then you filter the UserRatings by user and item, load the items you want to change in the database, and change them using object oriented programming.

It is always a trade-off between performance and object oriented programming. You should try to make it as OO as possible, and only do optimizations if it is needed. Maintainability is important.

I would avoid moving foreign keys to the domain model.

Answer 2

I would choose option C. Your concerns about performance indicate you may be optimizing prematurely. I think it would be fine for you to have a GetUser(int userId) method, then look for the appropriate item in its Ratings collection, and update it.

This does, however, bring up a common problem that ORMs suffer called the N+1 SELECT problem. Looking at each UserRating to find the appropriate one would likely result in one SELECT statement per UserRating. There are several ways to address this. One being to change your mapping file to either disable lazy loading of the Ratings collection, or else load it using 'join' fetching - see this section of the NHibernate documentation.

Answer 3

Using HQL your query would look like this

Select From UserRating
Where ItemId=: @ItemId
and UserId=: @UserId

This will give you the UserRating object that you are after then you can updated in and save it as necessary

And alternative would be

Session.CreateCriteria(typeof(ClassLibrary1.UserRating))
                    .Add(Expression.Sql(String.Format("ItemId={0}",UserId)))
                    .Add(Expression.Sql(String.Format("UserId={0}",ItemId)))
                    .List<ClassLibrary1.UserRating>();

This was the simplest way I could get this to work. I am not happy with the embedded strings but it works.

Answer 4

public void UpdateRating( int userId, int itemId, int newRating, ISession session )
{
  using( var tx = session.BeginTransaction())
  {
    var ratingCriteria = session.CreateCriteria<UserRating>()
      .CreateAlias( "Item" "item" )
      .CreateAlias( "User" "user" )
      .Add( Restrictions.Eq( "item.ItemId", itemId ) )
      .Add( Restrictions.Eq( "user.UserId", userId ) );
    var userRating = ratingCriteria.UniqueResult<UserRating>();
    userRating.Rating = newRating;
    tx.Commit();
  }
}

You will need to test this as it has been a while since I used the criteria api but essentially what this does is creates alias for two association paths and then using those aliases, adds restrictions to the User and Item so that you only get the UserRating you are interested in.

Everything is done inside a transaction and by relying on NHibernate's dirty-state tracking, the change will be flushed to the database when the transaction commits.

Depending on the version of NHibernate you are using, you could also query using NHLinq or the NHLambda stuff that has been integrated and is now accessible via session.QueryOver<T>.

To retrieve a list of all the items that a user has not rated, you will need to use a subquery to identify all of the items the user has rated and then apply a not in clause to all of the items ( essentially get me all the items not in the items the user has rated ).

var ratedItemsCriteria = DetachedCriteria.For<UserRating>()
    .CreateAlias( "Item" "item" )
    .SetProjection( Projections.Property( "item.ItemId" ) )
      .CreateCriteria( "User" )
        .Add( Restrictions.Eq( "UserId", userId ) );
var unratedItemsCriteria = session.CreateCriteria<Item>()
  .Add( Subqueries.PropertyNotIn( "ItemId", ratedItemsCriteria ) );
var unratedItems - unratedItemsCriteria.List<Item>();

In general, I think most of your problems could be resolved by judicious application of google, nhforge and the nhibernate user mailing list.

Answer 5

1. Query the database (using HQL, Criteria, SQL query, etc.) for the UserRating you want to modify
2. Change the UserRating however you like
3. Commit your changes

In pseudocode, this would look something like this:

using (ITransaction transaction = session.BeginTransaction())
{
    UserRating userRating = userRatingRepository.GetUserRating(userId, itemId);
    userRating.Rating = 5;
    transaction.Commit();
}

This will involve two queries (as opposed to the one query "old school" solution). The first query (which happens in the GetUserRating call) will run a SQL "SELECT" to grab the UserRating from the database. The second query (which happens on transaction.Commit) will update the UserRating in the database.

GetUserRating (using Criteria) would look something like this:

public IList<UserRating> GetUserRating(int userId, int itemId)
{
    session.CreateCriteria(typeof (UserRating))
        .Add(Expression.Eq("UserId", userId))
        .Add(Expression.Eq("ItemId", itemId))
        .List<UserRating>();
}

Answer 6

I see that this Q has not been marked as answered, so I'll give it a shot. In my opinion, you have to look a lot at how the objects are used. It seems to me that you'd relate a UserRating more to an Item than to a user, simply because you'd display it next to the item in a UI. It doesn't feel that important to always display it for a user.

That is why I would remove the list of ratings from the user:

public class User 
{
    public virtual int UserId { get; set; }
    public virtual string UserName { get; set; }
}

If you want the ratings for a user, you'd get that through a repository.

I'd leave the Item class unchanged, since you always want to see the ratings with an item:

public class Item 
{
    public virtual int ItemId { get; set; }
    public virtual string ItemName { get; set; }
    public virtual IList<UserRating> Ratings { get; set; }
}

The UserRating class could be completely disconnected from the Item and User classes. Just keep the ids in there, so you could retrieve an Item or User from a repository if you need to:

public class UserRating 
{
    public virtual int UserId { get; set; }
    public virtual int ItemId { get; set; }
    public virtual Int32 Rating { get; set; }
}