NHibernate – generating WHERE IN … OR … queries

Let’s assume that we have a simple table GL_Task which looks like this
I was asked to rewrite simple SQL query

using NHibernate’s QueryOver API. As simple as it may seem, solution for this particular problem is not straightforward. My first (not so clever) attempt was simply combining WhereRestrictionOn and Where clause

Of course this query returns wrong data because of the fact, that NHiberante by default “joins” queries using AND operator. So above query will result in generating something like that

After a little digging I managed to write appropriate query

Although this code generates correct SQL, it is hard to use it in case of many OR statements. For example, adding one more OR force us to write

In my opinion this is a little bit hard to read, so I continued my searching to find more elegant solution. My third attempt was based on Restrictions.Disjunction

This was almost perfect solution. Adding new OR statements do not require us to do function nesting

However the ultimate solution is to just add NHibernate.Criterion namespace and use IsIn extension method. This operation simplify our query into single lambda expression

Source code for this post can be found here

NHibernate – generating WHERE IN … OR … queries

NHhibernate – multiple queries in one roundtrip to server

Usually when we need to retrieve data from database server, we write code which looks like that

This code will execute two queries,each of them in separate roundtrip to the server.
This is the most popular way of getting data for multiple tables etc. However it is possible to enhance the code above and execute several queries in only one roundtrip to the database server. NHibernate provides us with three different mechanisms to accomplish this goal:

  • MultiQuery
  • First way to execute several queries at once is MultiQuery. In my opinion this is not very handy mechanism because of the fact, that we must use HQL. Here is an example of usage

    As You can see thanks to CreateMultiQuery method we are able to aggregate multiple queries. In order to get access to results, we have to execute code below

    Now we can get result of specific query using indexer

    If we don’t like this approach, the CreateMultiQuery method has an overload which allows to give “names” to queries. Thanks to this we can get result of query by its name.

  • MultiCriteria
  • Much better way of creating multiple queries is MultiCriteria. In this case we can use QueryOver API, so we don’t have to rely on HQL query strings.

    As You can see the usage of CreateMultiCriteria method is pretty consistent with CreateMultiQuery. Unfortunately we still have to retrieve data from specific query by index or by query name.

  • Future
  • My personal favorite for creating multi queries is Future. The usage is pretty straightforward

    As You can see, we can leverage QueryOver API and instead of calling List we call Future. NHibernate will aggregate our queries and will execute them once we start to iterate over one of them.

Despite the fact that all three mechanism has completely different syntax ,SQL created by all of them is the same and looks like that
multiple queries

NHhibernate – multiple queries in one roundtrip to server

NHibarnate – using event listeners to set entity modification date

I think, that it is a common practice to store information about creation and modification date of entities. However keeping appropriate columns up to date manually is error prone. We must remember that every time we modify entity, we also have to change its modification date. Fortunately thanks to event listeners, NHibernate can do all of this boring stuff for us. Let’s start from creating class which implements two interfaces:

  • IPreInsertEventListener
  • IPreUpdateEventListener

As You probably guessed OnPreInsert method is invoked before insert, and OnPreUpdate is called before update of entity. Having prepared the basic skeleton of class, it is time to configure NHibernate to use our event listeners. The basic configuration is taken from my previous post about NHibernate automappings, so I will only extend it by appending event listeners

Now we can put some logic into OnPreInsert and OnPreUpdate functions. First of all we have to somehow identify entities which hold information about creation and modification time. In order to do that I created simple interface IDateInfo

In the next step we have to modify OnPreInsert and OnPreUpdate methods, so that they can set creation and modification date of entities which implement IDateInfo interface.

Unfortunately at this moment saving entity (which implements IDateInfo interface) will throw exception
Despite the fact that we set values of ModificationDate and CreationDate properties , NHiberante seems not to notice that. That is why we have to manually update values of State object from PreInsertEvent and PreUpdateEvent. According to the documentation State object from class PreInsertEvent holds values which should be inserted and State object from class PreUpdateEvent holds values which should be updated. Function which update values of state object might look like that

Updated NHListener class now looks like that

As You can see, after setting values of IDateInfo I also call function SetState and update state of appropriate properties. From now everything works fine and our entities can be saved without problems. Source code for this post can be found here

NHibarnate – using event listeners to set entity modification date

FluentNhibernate – mapowanie kolumn typu time

Ostatnio napotkałem na dość ciekawy wyjątek podczas wykonywania NHibernatowego inserta. W bazie danych mam prostą tabelę
Do takiej tabeli został stworzony model

oraz mapping

Niestety ku mojemu zaskoczeniu próbując zapisać do bazy obiekt typu Appointment dostałem następujący wyjątek
SqlException: Operand type clash: bigint is incompatible with time
Mówiąc szczerze wyjątek ten niewiele mi powiedział, zwłaszcza, że zapytanie wygenerowane przez NHibernata (przechwycone w NHibernateProfilerze) wyglądało jak najbardziej poprawnie. Jednakże zapytanie przechwycone przez SqlProfilera wyglądało w następujący sposób

Widać tutaj, że drugi parametr (@p2) został oznaczony jako bigint, a nie time (tak jak jest to oznaczone w bazie). Przyczyną takiego stanu rzeczy jest zły mapping. Z tego co wyczytałem, jeżeli chcemy aby poprawnie zmapować TimeSpana na kolumnę typu time, musimy zmodyfikować interesujący nas mapping na następujący.

Mając taki mapping nasze zapytanie zostanie wygenerowane poprawnie i operacja insertu zakończy się powodzeniem

FluentNhibernate – mapowanie kolumn typu time