Recently we had a following problem with NHibernate, and although I love NHiberante, it does not always behave as expected.

We have a Person class correctly mapped in NHibernate:

public class Person
{
	public int Id { get; set; }
	public int Name { get; set; }
}

We saw that sometimes we were getting a Person with Id equal to zero, from our repository:

public class PersonRepository
{
	private ISession _session;

	//...

	public Person LoadById(int id)
	{
		return _session.Load<person>(id);
	}
}

We narrowed the problem down and wrote this little test:

[Test]
public void LoadById_Loads_IdIsSet()
{
    _context.ExecuteInTransaction(() =>
		{
			Person person = _context.PersonRepository.LoadById(7);
			Assert.AreEqual(7, person.Id);
		}
	);
}

…which of course failed.

After initial surprise, we took a second look at the Person class and we saw that we are missing virtual keyword on properties. NHibernate is not able to create a correct Proxy object.

public class Person
{
	public virtual int Id { get; set; }
	public virtual int Name { get; set; }
}

This fixed the issue.
Strange thing is that we expect that NHibernate would throw an exception is such case.

Recently me and my team realized that several our queries, that include maximum limit on the recordset, are limited in the web application and not on the Oracle side.

First thought was that somebody simply forgot to add an invocation of SetMaxResults method, but our repository code was very obvious about it:

IQuery query = session.CreateQuery(hqlQuery);
query.SetMaxResults(100);

After some investigation it turned out that NHiberante is not able to use ROWNUM on the SQL side, when there is a fetch join on the collection.

This is HQL query we use:

select
    user
from
    User user
    /* next line causes no rownum check */
    inner join fetch user.EmployeeDatas EmployeeData
    left join fetch EmployeeData.Country country

And some NHibernate code:

public IList List(ISessionImplementor session, QueryParameters queryParameters)
{
	// ...
	if ( hasLimit && ContainsCollectionFetches )
	{
		log.Warn( "firstResult/maxResults specified with collection fetch; applying in memory!" );

NHibernate simply ignores the limit when executing the query and applies it on the result.

At some point I thought that it is not possible to even create such SQL query, but I was proven to be wrong. The query would look similar to this proof-of-concept:

SELECT
*
FROM
(
SELECT
*
FROM
TUSER u
WHERE
/* Add conditions here: */
EXISTS(…)
/* Here is DB-side limit: */
AND ROWNUM <= 14 ) AS u /* And now fetch all collections needed */ INNER JOIN TEMPLOYEEDATA d ON u.ID = d.USERID [/sql] So it's clear that it is possible to improve this in NHibernate. In our case change was simple: We changed one-to-many mapping to one-to-one.

But if you are not able to do this because of your requirements I would use custom SQL view and mapped it in NHibernate. The second idea would be to use NHibernate detached criteria and sub-queries.

Finally the unit test that we used to check if the limit is done on SQL database side:

[Test]
public void FindUsersBy_QueryWithLimit_LimitsOnSQLSide()
{
    using (LogChecker logChecker = new LogChecker("NHibernate", Level.Warn))
    {
        IList<user> users = this._context.ExecuteInTransaction(() =>
        {
            UserQuery q = new UserQuery();
            q.UserId = "ILMT";
            q.MaxNumberOfRecords = 14;
            return this._context.UserRepository.FindUsersBy(q);
        });
        Assert.IsEmpty(logChecker.Messages);
        Assert.AreEqual(14, users.Count);
    }
}

Here you can see the implementation of the LogChecker log4net log reader

Tables look as follows:

User
+ID
+PERSON_ID
+PRIMARY_USER

DeputyRole
+PERSON_ID

Disclaimer: Yes I know the db schema is broken, but we can not do anything about it.

Why didn’t we use standard propery-ref and column mapping but formula instead?

1. USER.PERSON_ID is not unique in USER table.
2. USER.PERSON_ID AND USER.PRIMARY_USER = ‘Y’ is unique in USER table.
3. We are using formula to get single record from USER (ID) with specified PERSON_ID and PRIMARY_USER = ‘Y’

In DeputyRole.hbm.xml we have:

<many-to-one name="User"
        class="DomainModel.Users.Entities.User"
        lazy="false"
        formula = "(SELECT u.ID FROM USER u WHERE
                                u.PRIMARYUSERID = 'Y'
                                AND u.PERSON_ID = PERSON_ID)"
/>

Can this be done in better way?

Today I needed to create unit test that checked if the NHibernate query was generated optimally.

Good thing is that, in case of an inefficient query, NHibernate puts a warning using log4net:

log.Warn( "firstResult/maxResults specified with collection fetch; applying in memory!" );

I wanted something like this:

[Test]
public void FindUsersBy_QueryWithLimit_LimitsOnSQLSide()
{
    using (LogChecker logChecker
        = new LogChecker("NHibernate", Level.Warn))
    {
        // Execute query using NHibernate

        // ....

        Assert.IsEmpty(logChecker.Messages);
    }
}

The problem is that it’s not that easy to attach MemoryAppender for a duration of unit test to the specified logger.

Anyway here’s the code:

public class LogChecker : IDisposable
{
    readonly Logger _logger;
    readonly Level _previousLevel;
    readonly MemoryAppender _appender = new MemoryAppender();

    public LogChecker(string logName, Level levelToCheck)
    {
        _logger = (Logger)LogManager.GetLogger(logName).Logger;
        _logger.AddAppender(_appender);
        _previousLevel = _logger.Level;
        _logger.Level = levelToCheck;
    }

    public List<string> Messages
    {
        get
        {
            return new List<loggingEvent>(_appender.GetEvents())
                  .ConvertAll(x => x.RenderedMessage);
        }
    }

    public void Dispose()
    {
        _logger.Level = _previousLevel;
        _logger.RemoveAppender(_appender);
    }
};