NHibernate - Getting up and running

Simple Queries on a single table with NHibernate

Index

Introduction to NHibernate
Simple Queries on a single table with NHibernate
Configuring Child Collections with NHibernate
Overriding Equals, GetHashCode and Database Design with NHibernate
Querying Collections Efficiently with NHibernate
Persisting objects back to the database with NHibernate

Introduction

There are many ways to get data using NHibernate, HQL, SQL, Criterion and QueryOver (using lambda expressions). Below is a very small sample of basic queries you can perform. Apart from the first 2 special methods, the rest use QueryOver, new to version 3. Things get more interesting when more than one table is involved, below will give you a feel for the syntax on how queries are built.

The profiler tool being used to capture the generated Statements is NHibernate Profiler. This is a highly recommended tool for any serious NHibernate development, a real time saver.

Using Session.Get<T>

This is a optimised function for returning data using the primary key value, returning a single object. The key value must exist in the database or null is returned.

        [Fact]
        public void CanGetEmployeeById() {
            Employee e = null; 

            using (var tx = Session.BeginTransaction()) {
                e = Session.Get(4);
                tx.Commit();
            }

            Assert.NotNull(e);
            Assert.Equal("Peacock", e.LastName);
            Assert.Equal("Margaret", e.FirstName);
        }

Using Session.Load<T>

Session.Load will always return a value (proxy object) whether it exists in the database or not. A trip to the database is not made, instead a proxy object is created with its Id set. All other values do not exist. This is typically used for loading lookup values returned from a view.

        [Fact]
        public void CanGetEmployeeThatDoesNotExist() {
            Employee e = null;

            using (var tx = Session.BeginTransaction()) {
                e = Session.Load(20);
                tx.Commit();
            }

            Assert.NotNull(e);
            Assert.Equal(20, e.EmployeeId);
        }

The the Test below, there is no Employee with an Id of 20 in the Employee table, but the proxy is still created.

Employee Proxy object returned by Session.Load

Filtering the results with criteria

The 2 tests below demonstrate how to narrow the results by specifying criteria using lambda expressions. These are translated into a "where" clause within Sql Server

        [Fact]
        public void CanGetEmployeeByLastname() {
            Employee e = null;

            using (var tx = Session.BeginTransaction()) {
                e = Session.QueryOver()
                    .Where(x => x.LastName == "Fuller")
                    .SingleOrDefault();

                tx.Commit();
            }

            Assert.NotNull(e);
            Assert.Equal(2, e.EmployeeId);
            Assert.Equal("Fuller", e.LastName);
            Assert.Equal("Andrew", e.FirstName);
        }
        [Fact]
        public void CanGetSalesRepsFromUk() {
            IList employees = null;

            using (var tx = Session.BeginTransaction()) {

                employees = Session.QueryOver()
                    .Where(x => x.Title == "Sales Representative")
                    .And(x => x.Country == "UK")
                    .List();

                tx.Commit();
            }

            Assert.NotNull(employees);
            Assert.Equal(3, employees.Count);
        }
Profiler showing filter on Sales Reps

Filtering the results using fluent Restrictions

The WhereRestrictionOn method allows you to filter using restrictions such as IsBewteen, IsEmpty, IsIn, IsNotEmpty, IsLike, IsNotNull and IsNull. Theres are translated into optimised Sql Statements.

        [Fact]
        public void CanGetEmployeesHiredIn1992() {
            IList employees = null;

            using (var tx = Session.BeginTransaction()) {
                employees = Session.QueryOver()
                    .WhereRestrictionOn(x => x.HireDate)
                        .IsBetween(new DateTime(1992,1,1))
                        .And(new DateTime(1992, 12, 31))
                    .List();

                tx.Commit();
            }

            Assert.NotNull(employees);
            Assert.Equal(3, employees.Count);
        }
Profiler showing Between clause on HireDate
        [Fact]
        public void CanGetFemaleEmployees() {
            IList employees = null;

            using (var tx = Session.BeginTransaction()) {
                employees = Session.QueryOver()
                    .WhereRestrictionOn(x => x.TitleOfCourtesy)
                        .IsIn(new [] { "Ms.", "Mrs." })
                    .List();

                tx.Commit();
            }

            Assert.NotNull(employees);
            Assert.Equal(5, employees.Count);
        }
Profiler showing In clause on TitleOfCourtesy

Summary

The key points to take away from the above are

  • Use Session.Get<T> to populate an object by a primary key
  • Use Session.Load<T> to load a proxy object by Id (typically lookup Id's)
  • There are many different ways to query data, this is a very small sample on a single table

Source Code

git clone https://github.com/mindfulsoftware/ORM.Nhibernate.git
(version: 6a9dcbfd7a5cb808c7f4be0d0715acc9cf7de5f3)