Painless filtering in NHibernate

The Stage

Let’s assume, there exists an application, which is already heavily relying on NHibernate. This app is supposed to be extended. All wordings are currently available in a single locale and re stored in the database. This is matter to change, since we want to introduce multi-language capabilities for our wordings.

The Plan

The simplest way to achieve the requirement would be to extend the domain-model in such a way, that a new column would be added to the tables in the database. This column would contain the locale of the text. Instead of a single row for each text, we would end up with a single row for each locale for each text.

In order to retrieve a certain text from the wording-table of the database, we not only need to know which text to get, but we also need to know which local of the text we need to get. So this would end up in an additional criteria when retrieving data from the database. This is the point where NHibernates comes into play. NHibernate offers global filters, which can be used in conjunction with regular queries.

I will demonstrate how to make use of these filter by showing a little example.

The Implementation

Let’s take a small app, which is persisting pizza and toppings using NHibernate. Therefore we have POCOs and according mappings.

namespace Pizza.Entities
{
    /// <summary>
    /// Business Entity representing a pizza-topping
    /// </summary>
    public class Topping
    {
        /// <summary>
        /// The internal, unique id of this topping
        /// </summary>
        /// <value>The id.</value>
        public virtual int Id { get; set; }
        /// <summary>
        /// the name of the topping
        /// </summary>
        /// <value>The name.</value>
        public virtual string Name { get; set; }
    }
    /// <summary>
    /// Business Entity representing a pizza
    /// </summary>
    public class Pizza
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="Pizza"/> class
        /// </summary>
        public Pizza()
        {
            Toppings = new List<Topping>();
        }
        /// <summary>
        /// The internal, unique id of this pizza
        /// </summary>
        /// <value>The id.</value>
        public virtual int Id { get; set; }
        /// <summary>
        /// the name of the pizza
        /// </summary>
        public virtual string Name { get; set; }
        /// <summary>
        /// the toppings, which are being used by the pizza
        /// </summary>
        public virtual IList<Topping> Toppings { get; private set; }
    }
}
<class name="Topping" table="Toppings">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
</class>
<class name="Pizza" table="Pizzas">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
  <list name="Toppings" table="PizzaToppings" cascade="all">
    <key column="PizzaId" />
    <index column="Position"/>
    <many-to-many column="ToppingId" class="Topping" />
  </list>
</class>

As you can see, pizza as well as toppings each have a name. A pizza also has a set of toppings.

So far, so good. To support multiple locales we could go several ways. We could introduces a property called Name_de as well as Name_en to support german (DE) and englisch (EN) locales. An alternative approach would be, to add a property called Locale, which hold the locale of the name. If I would like to get english pizzas I would need to filter for pizza entities, which have a locale of “en”.

OK, let’s go with the second alternative. But how do I restrict my queries to only fetch pizza entities with locale=en? With NHibernate-Filter!

First of all we need to define a filter.

<filter-def name="localized">
  <filter-param name="myLocale" type="System.String"/>
</filter-def>

The next step is, to include this newly created filter in the mappings.

<class name="Topping" table="Toppings">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
  <property name="Locale"/>
  <filter name="localized" condition=":myLocale = Locale"/>
</class>
<class name="Pizza" table="Pizzas">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
  <property name="Locale"/>
  <list name="Toppings" table="PizzaToppings" cascade="all">
    <key column="PizzaId" />
    <index column="Position"/>
    <many-to-many column="ToppingId" class="Topping">
      <filter name="localized" condition=":myLocale = Locale"/>
    </many-to-many>
  </list>
  <filter name="localized" condition=":myLocale = Locale"/>
</class>

This way I specify that by using the filter I want to restrict my results by the column Locale. It’s important to notice, that this will only happen when I’m actually using this filter. When I’m not using the filter this restriction is being ignored. Thus: the filter is optional!

OK, so how does this actually work in my application – how do I use such a filter? What I don’t want: I don’t want to change all my existing queries to include a filter for the locale column. Instead I’m just activating the previously defined filter and assign a certain value.

// enable the filter
_session.EnableFilter("localized").SetParameter("myLocale", "de");
// do some plain ol’ querying
var pizzaList = _session.CreateCriteria<Entities.Pizza>().List<Entities.Pizza>();

That’s it. There is nothing more to it. I can leave all my queries the way they are and just execute them like before. Let’s take a look at the SQL statements that are being created, because they will show the usage of the filter.

SELECT this_.Id as Id2_0_, this_.Name as Name2_0_, this_.Locale as Locale2_0_
    FROM Pizzas this_
    WHERE @p0 = this_.Locale;@p0 = 'de'

This shows the strength of NHibernate-Filters. Besides the modification of the mappings and the activation of the filter I don’t need to modify anything. I don’t need to alter my queries. The filter is being applied by NHibernate behind the scenes.

This also works in more complex scenarios. Let’s assume I have a pizza with several toppings. I only want to get a pizza in german – and of course I also want to get german toppings as well. This works as too.

var multiPizza = new Entities.Pizza {Name = "Multikulti", Locale = "de"};
multiPizza.Toppings.Add(new Topping {Name = "Käse", Locale = "de"});
multiPizza.Toppings.Add(new Topping {Name = "Mushrooms", Locale = "en"});
_session.Save(multiPizza);
_session.Flush();
_session.Clear();
var fromDb = _session.CreateCriteria<Entities.Pizza>().Add(Expression.Eq("Id", multiPizza.Id)).List<Entities.Pizza>()[0];
Assert.That(fromDb.Locale, Is.EqualTo("de"),"pizza locale");
Assert.That(fromDb.Toppings,Has.Count.EqualTo(1));
foreach (var topping in fromDb.Toppings)
{
    Assert.That(topping.Locale, Is.EqualTo("de"),"topping locale");
}

I already activated and set the filter in the constructor of my class – so I don’t need to do this over again. At least not as long as I reuse the same session. Let’s take a look at the SQL send tot eh database – this is just impressive!

SELECT this_.Id as Id2_0_, this_.Name as Name2_0_, this_.Locale as Locale2_0_
    FROM Pizzas this_
    WHERE @p0 = this_.Locale
        AND this_.Id = @p1;@p0 = 'de', @p1 = 4
SELECT toppings0_.PizzaId as PizzaId1_, toppings0_.ToppingId as ToppingId1_, toppings0_.Position as Position1_, topping1_.Id as Id1_0_, topping1_.Name as Name1_0_, topping1_.Locale as Locale1_0_
    FROM PizzaToppings toppings0_ left outer join Toppings topping1_ on
        toppings0_.ToppingId=topping1_.Id
    WHERE  @p0 = topping1_.Locale
        and toppings0_.PizzaId=@p1;@p0 = 'de', @p1 = 4

Conclusion

The use of NHibernate-Filters introduces a very transparent way to extend the applicationlogic to include additional filter criteria.

  • Instead of storing a newly localized text in a seperate column, this doesn’t require any schema-changes.
  • Only the rows to a certain locale are being retrieved, this might reduced bandwith.

6 Comments

  1. Don’t forget about the deleted items. Last SQL query is incorret – won’t return rows from table toppings0 while there is no matching row in table toppings1

  2. The table toppings0 (sql alias generated by NHibernate!) is actually the many-to-many mapping table of pizza to toppings. So I select all elements of the mapping table for the give pizza, to get all the toppings (actually the topping ids) of the pizza. Then I select all toppings with the given locale (from toppings1, which is the actual toppings table).

    So you’re right, I won’t return toppings of a pizza which might not exists in the given local, but which are associated with pizza (in the many-to-many table). That might not be good in a real world application.

    Point of this post was however to show the usage of NHibernate-filters and how you could use them the create partitioned data applications.

  3. Thanks for posting this! I am curious about your left outer join. Will this work for a pizza that has no toppings? I would think the where clause will prevent those pizzas from being returned. The filter doesn’t seem compatible with left outer joins, in my experience.

  4. Well, Toppings are actually lazy-loaded, so when you load a pizza with toppings two select-statements are being created (as can be seen in the last sql-statement in my post). So if the Pizza has no toppings, then no toppings are being returned. This should not have any effect on the pizza loading. The left-outer-join only applies to the toppings and locales.

  5. I’ve got a scenario where I’d like to use filters together with table-per-class inheritance. The problem is, that the resulting where clause, adds its predicate to the table of the inherited entity (e.g. FamilyPizza), not the table of the base entity (e.g. Pizza). Any indea about a solution?

Leave a Comment.