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
