NHibernate and Linq

Summary

In my last post, I showed how to get NHibernate up and running. This article will talk about using Linq with NHibernate. I’m also going to add another table to the mix and show a sample query that joins these two tables together.

Using Linq

If you typed in the project from my last post or you downloaded the project (download it here), then you can continue on with this part of the task. One of the static objects provided in the IProductRepository.cs file was the NHibernateHelper class. I’m going to use the OpenSession() method available from this class to create a context for my test query. In the Program.cs file (the starting method of your program), add some headers:

using System;
using System.Linq;
using NHibernate;
using NHibernate.Linq;

Now add the following code to your "Main" method:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = from p in session.Query<Product>() select p;

    foreach (var item in query)
    {
        Console.WriteLine(item.Name);
    }
}

Console.ReadKey();

Now, when you execute your program, you’ll see a list of all the product names in the console window (as well as the query that was sent). The key to making this work is to include the NHibernate.Linq (as well as the normal System.Linq). Then you can use Linq similar to Entity Framework. One difference in the query you might have noticed is the “session.Query()” syntax. “Query” is NHibernate version 3. Versions 2 and earlier used “Linq” in the syntax. Entity Framework only uses the table name (like “session.Product”).

Adding a Second Table

Now, let’s add a second table. First, I’m going to create the table in MS SQL Server, called “Store”. This will be the table that contains a list of stores where the products are located. You can use this SQL script to create the new table:

USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Store](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Add a foreign key field to your product table:

ALTER TABLE dbo.Product ADD Store2 int NULL

Then populate the store table with two records:

INSERT INTO store
(
Name
)
VALUES
(
'Shop X'
)
GO
INSERT INTO store
(
Name
)
VALUES
(
'East Market'
)
GO

Then doctor up the product foreign key to point to these two stores:

UPDATE product SET store=1

UPDATE product SET store=2 WHERE id > 2

OK, one more SQL step to get the data in synch with what I’m doing here. Create a relational integrity constraint between the product foreign key and the store table:

ALTER TABLE product ADD FOREIGN KEY (store) REFERENCES Store(Id)

On to the Code

Now we need to add a “store.hbm.xml” file and a “store.cs” file, just like we did with product. Add the store.hbm.xml file to the mappings directory. Make sure the “Build Action” property is set to “Embedded Resource”. Add this code to the file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NHibernateTestBlog" namespace="NHibernateTestBlog">

  <class name="Store" table="Store">
    <id name="Id" column="Id" type="Int32">
      <generator class="identity" />
    </id>
    <property name="Name" column="Name" />
    <property name="Address" column="Address" />
    <property name="City" column="City" />
    <property name="Zip" column="Zip" />
  </class>


</hibernate-mapping>

Now create the store.cs file inside your domain folder, and copy this into it:

namespace NHibernateTestBlog
{
    public class Store
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Address { get; set; }
        public virtual string City { get; set; }
        public virtual string Zip { get; set; }
    }
}

At this point, we can write a query to join these two tables together. Go to “Program.cs” and change your query to look like this:

var query = from p in session.Query<Product>() 
            join s in session.Query<Store>() on p.Store equals s.Id
            where s.Id == 2
            select p;

When you run your program, you’ll see the three products that belong to store number 2.

Summary

At this point I’ve only covered an example where multiple tables are joined using Linq in NHibernate. One minor issue to note is that I did not set the foreign key (product.store) to a not null field. It’s best to do this to prevent someone from attempting to insert a record with a null for this field (the constraint in SQL will complain).

There is also a method to setup many-to-one list objects in NHibernate that I’m not going to dive into yet. For an example of this technique I will refer you to this article: Playing with NHibernate – Inverse and Cascade mapping attributes.

Leave a Reply