NHibernate and select one column only

You can make use of Projections when it comes to selecting from specific columns. For example sometime you may need to select values only from Id column instead of loading complete matching rows.

Lets quickly take a look at an example. Suppose we have a table called Customer with columns Id, FirstName, LastName, Street, City, PostalCode, Country. Now when doing our query we only want to select all Ids less then value 10. We can accomplish this by doing something like this

IList<int> customerIds = session.CreateCriteria<Customer>()
                .Add(Restrictions.Lt("Id", 10))
                .SetProjection(Projections.Property("Id"))
                .List<int>();

Select by foreign key and NHibernate

Recently I  came across NHibernate. We were thinking to replace our home grown persistence code generator with an ORM. We had two options either Nhibernate or Entity Framework. After evaluating these two awesome frameworks we picked NHibernate.


So far we were using our custom written code generator which was emitting code modeled around Active Record pattern. During the development you will find yourself in a situation quite often where you will be needing to load some objects by some foreign key. This will be the case in many-to-one relation. For example load all products belonging to category with Id equal to 4. This is very easy when you are writing your custom query for example in our case before NHibernate we used to write text query. Then execute it as datareader, iterate over reader to create objects. Finally return collection of all  objects.


Now when defining NHibernate mappings you will map your foreign key columns to properties returning object/objects of that related entity instead of just an id value. The reason behind this is that Nhibernate wants to make sure that when you are relating to some object then that object must exist in database.


Anyway if you are reading this topic I am quite sure you are already aware of the case and are looking for solution. So let say we are making use of Northwind Database where we have two tables called Categories and Products. One category can have many products. We want to list all products belonging to category with id equal to 4. Here is how I managed to do it with Nhibernate.

 IList<Products> products = session.CreateCriteria<Products>("P")  
           .CreateCriteria("Category", "C", NHibernate.SqlCommand.JoinType.InnerJoin)  
           .Add(Restrictions.Eq("C.Id", 4))  
           .List<Products>();  

Here is the SQL generated by NHibernate

NHibernate: SELECT this_.ProductID as ProductID6_1_, this_.Discontinued as Disconti2_6_1_
, this_.ProductName as ProductN3_6_1_, this_.QuantityPerUnit as Quantity4_6_1_
, this_.ReorderLevel as ReorderL5_6_1_, this_.UnitPrice as UnitPrice6_1_
, this_.UnitsInStock as UnitsInS7_6_1_, this_.UnitsOnOrder as UnitsOnO8_6_1_
, this_.CategoryID as CategoryID6_1_, this_.SupplierID as SupplierID6_1_
, c1_.CategoryID as CategoryID10_0_, c1_.CategoryName as Category2_10_0_
, c1_.Description as Descript3_10_0_, c1_.Picture as Picture10_0_ 
FROM Products this_ inner join Categories c1_ on this_.CategoryID=c1_.CategoryID 
WHERE c1_.CategoryID = @p0;@p0 = 4