Setup SQL Server Compact 4 to unit test NHibernate related code

Most of the time to fake data base SQL lite data base is used. But it has certain differences from SQL server. With release of SQL Compact 4 it becomes really good choice to mock data base calls. So in this post I will describe a way of setting up NHibernate to work with SQL CE local data base. So here are our goals:

1. Each test fixture has its own fresh DB instance
2. NHibernate SessionFactory is same for all the tests (performance is still important in tests)
3. Caching of NHibernate doesn’t stands on isolation way (each test will have clean factory, without any cached entities)
4. Works just after getting from source control with no additional configuration

First of all we need to install sql ce tools for visual studio. After done that we can add an empty data base file to our tests project:

lets call it db. This file is going to be the one that is going to be copied for each test and where NHibernate will create its tables.

Now lets create a base class for tests that are going to use NHibernate:

public class DbTests
{
    protected static ISessionFactory factory;
    static Configuration nhConfig;

    static DbTests()
    {
        File.Copy("db.sdf", "Temp.sdf", true);
        nhConfig = NhConfigure();
        factory = nhConfig.BuildSessionFactory();
    }

    [TestFixtureSetUp]
    public void Setup()
    {
        File.Copy("db.sdf", "Temp.sdf", true);
        new SchemaExport(nhConfig).Execute(true, true, false);
    }

    [TestFixtureTearDown]
    public void TearDown()
    {
        File.Delete("Temp.sdf");
    }

    static Configuration NhConfigure()
    {
        DomainMapper mapper = new DomainMapper();
        HbmMapping mappings = mapper.CompileMappingFor(new[] { typeof(TestEntity) });

        var configuration = new Configuration();
        configuration.SessionFactory()
            .Integrate.Using<MsSqlCe40Dialect>()
            .Connected.By<SqlServerCeDriver>()
            .Using("Data Source=Temp.sdf");

        configuration.AddDeserializedMapping(mappings, "domain");
        
        return configuration;
    }
}

What is done here is pretty straight forward. Each test fixture will get its own empty data base with new schema installed.

Also you will need to install SqlServerCompact package from nuget in order to get SqlServerCeDriver support.

There is an interesting bug when using identity columns with SQL CE. You can get NHibernate.AssertionFailure : null identifier exception. Here is how you can solve it.

The last thing we want to take care about is NHibernate cache. Each test probably will have its own ISession instance, so only second level caching should be handled. Here is how we can clean up it:

static void ClearCache()
{
    factory.EvictQueries();
    foreach (var collectionMetadata in factory.GetAllCollectionMetadata()) 
        factory.EvictCollection(collectionMetadata.Key);
    foreach (var classMetadata in factory.GetAllClassMetadata()) 
        factory.EvictEntity(classMetadata.Key);
}

Just add this method call to Setup and that it. As always working sample attached:

Source code sample doesn’t contain nuget packages and uses this way of working. So don’t be scared of everything red in ReSharper after open solution. Just build it.

Сложные запросы через ICriteria

Недавно пришлось сделать довольно таки интересный запрос с использованием NHibernate Criteria API. Думаю пример будет полезен в блоге. Итак рассмотрим следующую доменную модель:

 

Есть покупатель, у которого может быть много заказов, каждый из которых может содержать несколько продуктов.

Допустим, что необходимо построить страницу поиска, на которой можно запросить пользователей по следующим критериям:

1. Имя пользователя
2. Количество заказов
3. Общая сумма оплат по всем заказам

Для такого построения запроса я буду использовать следующий класс:

public class SearchCriteria
{
    public string UserName { get; set; }

    public int PageSize { get; set; }

    public int PageNumber { get; set; }

    public int? OrdersNumber { get; set;}

    public double? MinPayedMoney { get; set; }

    public double? MaxPayedMoney { get; set; }
}

Он содержит текущие условия поиска. Итак начнем. Самое простое что можно реализовать это поиск по имени пользователя:

Customer customerAlias = null;  // понадобится позже
var criteria = Global.CurrentSession.CreateCriteria(typeof(Customer), () => customerAlias);

if (!string.IsNullOrEmpty(searchCriteria.UserName))
{
    criteria.Add(SqlExpression.Like<Customer>(x => x.Name, searchCriteria.UserName, MatchMode.Anywhere));
}
criteria.SetMaxResults(searchCriteria.PageSize);
criteria.SetFirstResult(searchCriteria.PageSize * (searchCriteria.PageNumber - 1));

return criteria.List<Customer>();

Ничего интересного. Теперь добавим поиск по количеству товаров. Для этого необходимо будет использовать подзапрос. Делается это следующим образом:

if (searchCriteria.OrdersNumber.HasValue)
{
    ICriteria ordersCriteria = criteria.CreateCriteria<Customer>(x => x.Orders);

    DetachedCriteria computersCount = DetachedCriteria.For<CustomersOrder>();
    computersCount.SetProjection(Projections.RowCount());
    computersCount.Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

    ordersCriteria.Add(Subqueries.Eq(searchCriteria.OrdersNumber.Value, computersCount));
}

Для того, чтобы Nhibernate правильно выполнил join таблиц, мы используем customerAlias. В 6й строке мы указываем на то, что нас интересуют только заказы данного пользователя, а не общее количество заказов в таблице.

Подобным же образом необходимо сделать и запрос на общую сумму выплат:

DetachedCriteria payedAmout = DetachedCriteria.For<OrderLine>();
payedAmout.SetProjection(LambdaProjection.Sum<OrderLine>(x => x.TotalPrice));
payedAmout.CreateCriteria<OrderLine>(x => x.CustomersOrder)
    .Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

if (searchCriteria.MinPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Lt(searchCriteria.MinPayedMoney, payedAmout));
}

if (searchCriteria.MaxPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Gt(searchCriteria.MaxPayedMoney, payedAmout));
}

Собрав все воедино Nhibernate выполнит следующий SQL запрос:

SELECT top 20 this_.Id                as Id1_1_,
              this_.Name              as Name1_1_,
              customerso1_.Id         as Id3_0_,
              customerso1_.OrderDate  as OrderDate3_0_,
              customerso1_.CustomerId as CustomerId3_0_
FROM   Customers this_
       inner join CustomersOrders customerso1_
         on this_.Id = customerso1_.CustomerId
WHERE  this_.Name like '%test%' /* @p0 */
       and 2 /* @p1 */ = (SELECT count(* ) as y0_
                  FROM   CustomersOrders this_0_
                  WHERE  this_0_.CustomerId = this_.Id)
       and 100 /* @p2 */ < (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)
       and 500 /* @p3 */ > (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)

Обратите внимание на inner join в 7й строке. Если пользователь выполнил больше одного заказа, то в результате запроса SQL Server вернет столько строк, сколько заказов у пользователя. Чтобы избежать этого, необходимо добавить Distinct:

criteria.SetProjection(
                Projections.Distinct(
                    Projections.ProjectionList().Add(LambdaProjection.Property<Customer>(x => x.Id), "Id")
                                                .Add(LambdaProjection.Property<Customer>(x => x.Name), "Name")));

Теперь приведу весь код:

Customer customerAlias = null;  
var criteria = Global.CurrentSession.CreateCriteria(typeof(Customer), () => customerAlias);

if (!string.IsNullOrEmpty(searchCriteria.UserName))
{
    criteria.Add(SqlExpression.Like<Customer>(x => x.Name, searchCriteria.UserName, MatchMode.Anywhere));
}

if (searchCriteria.OrdersNumber.HasValue)
{
    ICriteria ordersCriteria = criteria.CreateCriteria<Customer>(x => x.Orders);

    DetachedCriteria computersCount = DetachedCriteria.For<CustomersOrder>();
    computersCount.SetProjection(Projections.RowCount());
    computersCount.Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

    ordersCriteria.Add(Subqueries.Eq(searchCriteria.OrdersNumber.Value, computersCount));
}

DetachedCriteria payedAmout = DetachedCriteria.For<OrderLine>();
payedAmout.SetProjection(LambdaProjection.Sum<OrderLine>(x => x.TotalPrice));
payedAmout.CreateCriteria<OrderLine>(x => x.CustomersOrder)
    .Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

if (searchCriteria.MinPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Lt(searchCriteria.MinPayedMoney, payedAmout));
}

if (searchCriteria.MaxPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Gt(searchCriteria.MaxPayedMoney, payedAmout));
}

criteria.SetProjection(
    Projections.Distinct(
        Projections.ProjectionList().Add(LambdaProjection.Property<Customer>(x => x.Id), "Id")
                                    .Add(LambdaProjection.Property<Customer>(x => x.Name), "Name")));

criteria.SetMaxResults(searchCriteria.PageSize);
criteria.SetFirstResult(searchCriteria.PageSize * (searchCriteria.PageNumber - 1));

criteria.SetResultTransformer(Transformers.AliasToBean<Customer>());

return criteria.List<Customer>();

Данный код формирует следующий SQL запрос:

SELECT distinct top 20 this_.Id   as y0_,
                       this_.Name as y1_
FROM   Customers this_
       inner join CustomersOrders customerso1_
         on this_.Id = customerso1_.CustomerId
WHERE  this_.Name like '%test%' /* @p0 */
       and 2 /* @p1 */ = (SELECT count(* ) as y0_
                  FROM   CustomersOrders this_0_
                  WHERE  this_0_.CustomerId = this_.Id)
       and 100 /* @p2 */ < (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)
       and 500 /* @p3 */ > (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)

Код выглядит как по мне страшновато. Но с другой стороны LINQ реализация врядли была бы проще. Все равно пришлось бы использовать синтаксис через extension методы и лямбда выражения.

Тут конечно можно еще кое что оптимизировать, но цель поста была показать как строить сложные запросы при помощи NHibernate. Надеюсь это поможет.

AdoNetBatchSize и StatelessSession

Для выполнения bulk операций над базой данных в NHibernate есть специальная StatelessSession. Интерфейс данного объекта отличается от стандартного Session, у него нет методов Save, SaveOrUpdate и т.д., зато есть Insert, Update, Delete.

Особенность StatelessSession в том, что она не следит за сохранёнными объектами, значит это следующее, рассмотрим следующий пример кода:

using(var transaction = Session.BeginTransaction())
{
    var someObject = Session.Get<SomeObject>(id);
    someObject.SomeProperty = "some new value";
    transaction.Commit();
}

В результате выполнения этого кода someObject будет обновлен не только в вашем приложении, но в и базе данных.

За подобное приходится платить производительностью, в частности именно это стало причиной того, что на OrmBattle Nhibernate получает далеко не самые лучшие отметки (кстати по этому поводу есть довольно хороший ответ от Ayende).

А теперь перейдем к основной теме. Показать хотелось бы следующее:

ISessionFactory sessionFactory = Fluently.Configure()
                                         .Database(MsSqlConfiguration.MsSql2008.ConnectionString("connString")
                                         .AdoNetBatchSize(40))  // important!
                                         .Mappings(x=>x.FluentMappings.AddFromAssemblyOf<TestHilo>())
                                         .BuildSessionFactory();

IStatelessSession session = sessionFactory.OpenStatelessSession();

for (int i = 0; i < 60; i++)
{
    var testHilo = new TestHilo
    {
        Field1 = "field" + i, 
        Field2 = i
    };

    session.Insert(testHilo);
}

session.Dispose();

Данный код должен вставить 60 записей в базу. На самом же деле вставится только 40. Происходит это из-за установленного AdoNetBatchSize (подробнее об этом в предыдущем посте). Когда это обнаружилось первое что захотелось сделать это вызвать session.Flush(). Но у StatelessSession нет такого метода :).

В общем решением будет следующее – выполнять данный цикл в рамках одной транзацкии:

using (ITransaction transaction = session.BeginTransaction())
{
    for (int i = 0; i < 60; i++)
    {
        var testHilo = new TestHilo
                                {
                                    Field1 = "field" + i,
                                    Field2 = i
                                };

        session.Insert(testHilo);
    }

    transaction.Commit();
}

Такой код вставит все 60 записей.

Sql DateTime и DateTime

Часто возникают SQL ошибки из-за использования константы DateTime.MinValue. Это происходит потому, что DateTime возвращает 01.01.0001, а минимально возможная SQL дата это 01.01.1753. Для того, чтобы избежать их используйте SqlDateTime.MinValue.