Linq to NHibernate

NHibernate's Answers — Wed, 26 Nov 2008 17:13:20 GMT

The popularity of NHibernate is steadily increasing. At the same time people get used to LINQ. Now there exists a LINQ to NHibernate provider since quite some time. It's not a complete implementation of a LINQ provider but it is still quite useful. Most of the day to day problems we face when developing typical business application can be solved by using this provider. And if there is a query that cannot be executed against the provider we still have the option to falling back to the hibernate query language (HQL).

In this post I'll give you an introduction on how you can use NHibernate in conjunction with LINQ. Let's start with the definition of a domain model we are going to use when querying the database by using LINQ expressions or LINQ query operators.

The domain model

I have the following simple domain model. A person can have a set of assigned tasks.

The entities

I want to keep the entities as simple as possible for this example. So their implementation is as follows

public class Person

    public Person()

        Tasks = new List<Task>();

    public virtual long Id { get; set; }

    public virtual string Lastname { get; set; }

    public virtual string Firstname { get; set; }

    public virtual IList<Task> Tasks { get; set; }

public class Task

    public virtual long Id { get; set; }

    public virtual string TaskName { get; set; }

    public virtual DateTime DueDate { get; set; }

one thing worth noting is the fact that I instantiate a new empty Tasks collection in the constructor of the Person class.

The mapping

For the mapping of my domain model I am going to use the Fluent NHibernate framework (please refer to my previous posts for a detailed introduction to this framework: part 1, part 2, part 3, part 4) .

The Task is very easy to map

public class TaskMap : ClassMap<Task>

    public TaskMap()

        Id(x => x.Id);

        Map(x => x.TaskName);

        Map(x => x.DueDate);

And the person is nothing more complicated

public class PersonMap : ClassMap<Person>

    public PersonMap()

        Id(x => x.Id);

        Map(x => x.Firstname);

        Map(x => x.Lastname);

        HasMany<Task>(x => x.Tasks)

            .LazyLoad()

            .Cascade.All();

Just let's have a look at the mapping of the Tasks collection. I want the tasks to only be lazy loaded when loading a person entity (LazyLoad). And I want NHibernate to automatically save or update any tasks associated with a given person if ever the person is updated (Cascade.All).

Test the mapping

One quick test I want to always do is to check whether my mappings work as expected. Since I use Fluent NHibernate this is very easy and straight forward. A lot of infrastructure code is available to me to leverage and thus simplify my unit test regarding mapping of the domain entities.

The base test fixture

First I want to present my base class I use for all of my tests. Every test fixture used in this post will inherit from this base class.

public class FixtureBase<TModel> where TModel : PersistenceModel, new()

    protected SessionSource SessionSource { get; set; }

    protected ISession Session { get; private set; }

    [SetUp]

    public void SetupContext()

        var cfg = new SQLiteConfiguration()

                        .InMemory()

                        .ShowSql();

        SessionSource = new SessionSource(cfg.ToProperties(), new TModel());

        Session = SessionSource.CreateSession();

        SessionSource.BuildSchema(Session);

        Context();

        Because();

        Session.Flush();

        Session.Clear();

    [TearDown]

    public void TearDown()

        TearDownContext();

        Session.Close();

        Session.Dispose();

    protected virtual void Context()

    protected virtual void Because()

    protected virtual void TearDownContext()

First of all my base fixture has a generic parameter TModel. TModel represents the persistence model used during the tests. The persistence model must inherit from the PersistenceModel (provided by Fluent NHibernate) and it must have a default constructor.

In the SetupContext method (which is executed before each test)

I define my configuration I want to use. Most of the time I use the SQLite database in in-memory mode for my database related tests. Fluent NHibernate provides me such a configuration object. For debugging purposes I declare that I want to see the SQL statements generated by NHibernate (ShowSql).
Second I create a session source and pass the above configuration as well as an instance of the model used to the constructor of the session source.
Then I create a new session object. Note that since I am using SQLite in in-memory mode I have to use the same session object during the whole test (including setup and tear down) since the database schema (and the data) is destroyed when the session is closed.
I now use the Session object created above to generate the database schema. The schema is generated from the information provided by the model (which consists of all mappings)
Now I call the virtual Context and Because methods. The reason is that I want my unit test to be more BDD like
Finally I flush all pending operations from the session to the database and then clear the session.

The persistence model

The persistence model I am using is very straight forward. I just include all class mappings that are in the same assembly as the PersonMap.

public class TestModel : PersistenceModel

    public TestModel()

        addMappingsFromAssembly(typeof(PersonMap).Assembly);

I could also explicitly add mappings if I want so

public class TestModel : PersistenceModel

    public TestModel()

        addMapping(new PersonMap());

        addMapping(new TaskMap());

The test

Writing a test for the mapping is really straight forward with the aid of the PersistenceSpecification class provided by Fluent NH.

[Test]

public void can_add_person_without_tasks()

    new PersistenceSpecification<Person>(Session)

        .CheckProperty(x => x.Firstname, "Gabriel")

        .CheckProperty(x => x.Lastname, "Schenker")

        .VerifyTheMappings();

or a test which also tries to add tasks

[Test]

public void can_add_person_with_tasks()

    var tasks = new[]

                        new Task {TaskName = "Task 1", DueDate = DateTime.Today.AddDays(5)},

                        new Task {TaskName = "Task 2", DueDate = DateTime.Today.AddDays(6)},

                        new Task {TaskName = "Task 3", DueDate = DateTime.Today.AddDays(3)},

};

    new PersistenceSpecification<Person>(Session)

        .CheckProperty(x => x.Firstname, "Gabriel")

        .CheckProperty(x => x.Lastname, "Schenker")

        .CheckList(x=>x.Tasks, tasks)

        .VerifyTheMappings();

Do I need to tell that the tests pass successfully? It's really that easy... I have not needed a single line of XML so far. And no "magic strings" are involved. For me it's really a joy to work like this.

And now finally we get LINQ into the play

The context

In LINQ the notion of a context is very important. It represent kind of a facade to the database. LINQ to NHibernate provides us a base class from which we can derive when we define our own context. In our simplified model we have only 2 entities and thus our context needs just two members to have access via LINQ to either the persons or tasks.

public class SampleContext : NHibernateContext

    public SampleContext(ISession session)

        : base(session)

{ }

    public IOrderedQueryable<Person> Persons

        get { return Session.Linq<Person>(); }

    public IOrderedQueryable<Task> Tasks

        get { return Session.Linq<Task>(); }

Testing

Now I want to implement a first test to check whether LINQ to NHibernate does indeed work as expected. First I have to setup a context where I have some person object with tasks in the database

public class a_repository_with_persons_having_tasks : Person_Fixture

    protected Person[] persons;

    private IList<Task> tasks1, tasks2, tasks3;

    protected override void Context()

        base.Context();

        tasks1 = new[]

                         new Task {TaskName = "Task 1.1", DueDate = DateTime.Today.AddDays(5)},

                         new Task {TaskName = "Task 1.2", DueDate = DateTime.Today.AddDays(6)},

                         new Task {TaskName = "Task 1.3", DueDate = DateTime.Today.AddDays(3)},

};

        tasks2 = new[]

                         new Task {TaskName = "Task 2.1", DueDate = DateTime.Today.AddDays(5)},

                         new Task {TaskName = "Task 2.2", DueDate = DateTime.Today.AddDays(6)},

                         new Task {TaskName = "Task 2.3", DueDate = DateTime.Today.AddDays(3)},

};

        tasks3 = new[]

                         new Task {TaskName = "Task 3.1", DueDate = DateTime.Today.AddDays(5)},

                         new Task {TaskName = "Task 3.2", DueDate = DateTime.Today.AddDays(6)},

                         new Task {TaskName = "Task 3.3", DueDate = DateTime.Today.AddDays(2)},

};

        persons = new[]

                          new Person {Firstname = "Gabriel", Lastname = "Schenker", Tasks = tasks1},

                          new Person {Firstname = "John", Lastname = "Doe", Tasks = tasks2},

                          new Person {Firstname = "Ann", Lastname = "Moe", Tasks = tasks3},

};

        foreach (var person in persons)

            Session.Save(person);

Now I can write a test for the case where I want to retrieve all persons from the database

[TestFixture]

public class when_querying_all_persons : a_repository_with_persons_having_tasks

    private IEnumerable<Person> list;

    protected override void Because()

        list = from p in db.Persons

               select p;

    [Test]

    public void should_return_all_persons()

        list.Count().ShouldEqual(persons.Length);

The query generated by the above test is

SELECT  this_.Id as Id0_0_,

        this_.Lastname as Lastname0_0_,

        this_.Firstname as Firstname0_0_

FROM [Person] this_

That's exactly what we were expecting!

Using Where to get a filtered list

Now let's do some more interesting stuff. I want to have a filtered list of persons

[TestFixture]

public class when_retrieving_filtered_list_of_persons : a_repository_with_persons_having_tasks

    [Test]

    public void can_filter_by_LastName()

        var list = db.Persons.Where(x=>x.Lastname=="Doe");

        list.Count().ShouldEqual(1);

    [Test]

    public void can_filter_by_task()

        var list = from p in db.Persons

                   from t in p.Tasks

                   where t.DueDate == DateTime.Today.AddDays(3)

                   select p;

        list.Count().ShouldEqual(2);

In the first test I use the Where extension method defined by LINQ to filter my collection of persons. The extension methods defined by LINQ are also called query operators. The select statement generated by Linq2NH is

SELECT count(*) as y0_

FROM [Person] this_

WHERE this_.Lastname = @p0;

@p0 = 'Doe'

In the second test I use the new language extension introduced for C# (also called a query expression) to get a filtered set of persons. This time we get the following select statement sent to the database

SELECT          count(*) as y0_

FROM            [Person] this_

left outer join [Task] t1_ on this_.Id=t1_.Person_id

WHERE t1_.DueDate = @p0;

@p0 = '29.11.2008 00:00:00'

Obviously also joins between tables work as expected.

Getting ordered lists

Often we need an ordered list of items, in this case persons ordered by their last name.

[Test]

public void can_order_by_LastName()

    var list = db.Persons.OrderBy(x => x.Lastname);

    list.First().Lastname.ShouldEqual("Doe");

The query generated is

SELECT  this_.Id as Id0_0_,

        this_.Lastname as Lastname0_0_,

        this_.Firstname as Firstname0_0_

FROM [Person] this_

ORDER BY this_.Lastname asc

Code

The code accompanying this post can be found here.

Summary

It is straight forward to use LINQ to NHibernate. Although the Linq2NH provider is not fully implemented it is more than sufficient for most scenarios we encounter in typical projects. And if ever we encounter a query that cannot be executed through the Linq2NH provider we can still implement the query by using HQL instead.

Enjoy

A Subtext Community

Type analyzer - Interesting findings

INTJ - The Scientists