NHibernate – generating WHERE IN … OR … queries

2014-08-182016-01-30 TPodolak.NET, c#, Disjunction, Nhibernate, QueryOver, Restrictions

Let’s assume that we have a simple table GL_Task which looks like this

I was asked to rewrite simple SQL query

SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' OR Id IN (3,4 /*more id's to come*/)

1	SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' OR Id IN (3,4 /more id's to come/)

using NHibernate’s QueryOver API. As simple as it may seem, solution for this particular problem is not straightforward. My first (not so clever) attempt was simply combining WhereRestrictionOn and Where clause

var result = session.QueryOver<Task>().Where(val=> val.Name == "First task")
                                      .WhereRestrictionOn(val=> val.Id)
                                      .IsIn(new []{3,4})
                                      .List();

var result = session.QueryOver<Task>().Where(val=> val.Name == "First task")

.WhereRestrictionOn(val=> val.Id)

.IsIn(new []{3,4})

.List();

Of course this query returns wrong data because of the fact, that NHiberante by default “joins” queries using AND operator. So above query will result in generating something like that

SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' AND Id IN (3,4 /*more id's to come*/)

1	SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' AND Id IN (3,4 /more id's to come/)

After a little digging I managed to write appropriate query

var orRestriction = Restrictions.Or(
                         Restrictions.Where<Task>(val => val.Name == "First task"),
                         Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }));

var result = session.QueryOver<Task>()
                    .Where(orRestriction)
                    .List();

var orRestriction = Restrictions.Or(

Restrictions.Where<Task>(val => val.Name == "First task"),

Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }));

var result = session.QueryOver<Task>()

.Where(orRestriction)

.List();

Although this code generates correct SQL, it is hard to use it in case of many OR statements. For example, adding one more OR force us to write

var orRestriction = Restrictions.Or(
      Restrictions.Or(
            Restrictions.Where<Task>(val => val.Name == "First task"),
            Restrictions.On<Task>(val => val.Id).IsIn(new[] {3, 4}) 
       ),
       Restrictions.On<Task>(val => val.Name).IsIn(new[] {"FirstTask", "SecondTask"})
);

var result = session.QueryOver<Task>()
                    .Where(orRestriction)
                    .List();

var orRestriction = Restrictions.Or(

Restrictions.Or(

Restrictions.Where<Task>(val => val.Name == "First task"),

Restrictions.On<Task>(val => val.Id).IsIn(new[] {3, 4})

Restrictions.On<Task>(val => val.Name).IsIn(new[] {"FirstTask", "SecondTask"})

);

var result = session.QueryOver<Task>()

.Where(orRestriction)

.List();

In my opinion this is a little bit hard to read, so I continued my searching to find more elegant solution. My third attempt was based on Restrictions.Disjunction

var disjunctinos = Restrictions.Disjunction()
                           .Add(Restrictions.Where<Task>(val => val.Name == "First task"))
                           .Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }));
                           
var result = session.QueryOver<Task>()                    
                    .Where(disjunctinos)                    
                    .List();

var disjunctinos = Restrictions.Disjunction()

.Add(Restrictions.Where<Task>(val => val.Name == "First task"))

.Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }));

var result = session.QueryOver<Task>()

.Where(disjunctinos)

.List();

This was almost perfect solution. Adding new OR statements do not require us to do function nesting

var disjunctinos = Restrictions.Disjunction()
                          .Add(Restrictions.Where<Task>(val => val.Name == "First task"))
                          .Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }))
                          .Add(Restrictions.On<Task>(val => val.Name).IsIn(new[] { "FirstTask", "SecondTask" }));

var result = session.QueryOver<Task>()
                    .Where(disjunctinos)
                    .List();

var disjunctinos = Restrictions.Disjunction()

.Add(Restrictions.Where<Task>(val => val.Name == "First task"))

.Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }))

.Add(Restrictions.On<Task>(val => val.Name).IsIn(new[] { "FirstTask", "SecondTask" }));

var result = session.QueryOver<Task>()

.Where(disjunctinos)

.List();

However the ultimate solution is to just add NHibernate.Criterion namespace and use IsIn extension method. This operation simplify our query into single lambda expression

var result = session.QueryOver<Task>()
                    .Where(val => val.Name == "First task" || val.Id.IsIn(new[] { 3,4 }))
                    .List();

var result = session.QueryOver<Task>()

.Where(val => val.Name == "First task" || val.Id.IsIn(new[] { 3,4 }))

.List();

Source code for this post can be found here

NHhibernate – multiple queries in one roundtrip to server

2014-04-272016-01-30 TPodolakc#, Nhibernate

Usually when we need to retrieve data from database server, we write code which looks like that

using (var session = DataAccesLayer.Instance.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        var projects = session.QueryOver<Project>().Where(val => val.Name == "Project").List();
        var tasks = session.QueryOver<Task>().Where(val => val.Name == "First task").List();
        //more queries           
        //more queries             
        //exception handling etc.
    }
}

using (var session = DataAccesLayer.Instance.OpenSession())

{

using (var transaction = session.BeginTransaction())

{

var projects = session.QueryOver<Project>().Where(val => val.Name == "Project").List();

var tasks = session.QueryOver<Task>().Where(val => val.Name == "First task").List();

//more queries

//exception handling etc.

}

This code will execute two queries,each of them in separate roundtrip to the server.

This is the most popular way of getting data for multiple tables etc. However it is possible to enhance the code above and execute several queries in only one roundtrip to the database server. NHibernate provides us with three different mechanisms to accomplish this goal:

MultiQuery

First way to execute several queries at once is MultiQuery. In my opinion this is not very handy mechanism because of the fact, that we must use HQL. Here is an example of usage

var projectQuery = session.CreateQuery("FROM Project as prj where prj.Name= :projectName")
                          .SetParameter("projectName", "Project");

var taskQuery = session.CreateQuery( "FROM Task as tsk where tsk.Name=:taskName" )
                       .SetParameter("taskName","First task");
 
var multiQueryByIndex = session.CreateMultiQuery( )
                               .Add<Project>( projectQuery )
                               .Add<Task>( taskQuery );

var projectQuery = session.CreateQuery("FROM Project as prj where prj.Name= :projectName")

.SetParameter("projectName", "Project");

var taskQuery = session.CreateQuery( "FROM Task as tsk where tsk.Name=:taskName" )

.SetParameter("taskName","First task");

var multiQueryByIndex = session.CreateMultiQuery( )

.Add<Project>( projectQuery )

.Add<Task>( taskQuery );

As You can see thanks to CreateMultiQuery method we are able to aggregate multiple queries. In order to get access to results, we have to execute code below

var result = multiQueryByIndex.List();

1	var result = multiQueryByIndex.List();

Now we can get result of specific query using indexer

var projects = result[0] as List<Project>;
var tasks = result[1] as List<Task>;

1 2	var projects = result[0] as List<Project>; var tasks = result[1] as List<Task>;

If we don’t like this approach, the CreateMultiQuery method has an overload which allows to give “names” to queries. Thanks to this we can get result of query by its name.

var multiQueryByKey = session.CreateMultiQuery( )
                             .Add<Project>( "project", projectQuery )
                             .Add<Task>( "task", taskQuery );
 
var projects = multiQueryByKey.GetResult( "project" ) as List<Project>;
var tasks = multiQueryByKey.GetResult( "task" ) as List<Task>;

var multiQueryByKey = session.CreateMultiQuery( )

.Add<Project>( "project", projectQuery )

.Add<Task>( "task", taskQuery );

var projects = multiQueryByKey.GetResult( "project" ) as List<Project>;

var tasks = multiQueryByKey.GetResult( "task" ) as List<Task>;

MultiCriteria

Much better way of creating multiple queries is MultiCriteria. In this case we can use QueryOver API, so we don’t have to rely on HQL query strings.

var projectQuery = session.QueryOver<Project>().Where(val => val.Name == "Project");
var taskQuery = session.QueryOver<Task>( ).Where(val => val.Name == "First task");
var multiCriteriaByIndex = session.CreateMultiCriteria( )
                                  .Add( projectQuery )
                                  .Add( taskQuery );

var multiCriteriaByKey = session.CreateMultiCriteria( )
                                .Add( "project", projectQuery )
                                .Add( "task", taskQuery );

IList result = multiCriteriaByIndex.List( );
var projects = result[0] as List<Project>;
var tasks = result[1] as List<Task>;

projects = multiCriteriaByKey.GetResult( "project" ) as List<Project>;
tasks = multiCriteriaByKey.GetResult( "task" ) as List<Task>;

var projectQuery = session.QueryOver<Project>().Where(val => val.Name == "Project");

var taskQuery = session.QueryOver<Task>( ).Where(val => val.Name == "First task");

var multiCriteriaByIndex = session.CreateMultiCriteria( )

.Add( projectQuery )

.Add( taskQuery );

var multiCriteriaByKey = session.CreateMultiCriteria( )

.Add( "project", projectQuery )

.Add( "task", taskQuery );

IList result = multiCriteriaByIndex.List( );

var projects = result[0] as List<Project>;

var tasks = result[1] as List<Task>;

projects = multiCriteriaByKey.GetResult( "project" ) as List<Project>;

tasks = multiCriteriaByKey.GetResult( "task" ) as List<Task>;

As You can see the usage of CreateMultiCriteria method is pretty consistent with CreateMultiQuery. Unfortunately we still have to retrieve data from specific query by index or by query name.

Future

My personal favorite for creating multi queries is Future. The usage is pretty straightforward

var projects = session.QueryOver<Project>().Where(val => val.Name == "Project").Future<Project>();
var tasks = session.QueryOver<Task>().Where(val => val.Name == "First task").Future<Task>();

1 2	var projects = session.QueryOver<Project>().Where(val => val.Name == "Project").Future<Project>(); var tasks = session.QueryOver<Task>().Where(val => val.Name == "First task").Future<Task>();

As You can see, we can leverage QueryOver API and instead of calling List we call Future. NHibernate will aggregate our queries and will execute them once we start to iterate over one of them.

Despite the fact that all three mechanism has completely different syntax ,SQL created by all of them is the same and looks like that

NHibarnate – using event listeners to set entity modification date

2013-08-242016-01-30 TPodolakc#, EventListeners, FluentNhibernate, Nhibernate

I think, that it is a common practice to store information about creation and modification date of entities. However keeping appropriate columns up to date manually is error prone. We must remember that every time we modify entity, we also have to change its modification date. Fortunately thanks to event listeners, NHibernate can do all of this boring stuff for us. Let’s start from creating class which implements two interfaces:

IPreInsertEventListener
IPreUpdateEventListener

public class NHListener : IPreInsertEventListener, IPreUpdateEventListener
{
    public bool OnPreInsert(PreInsertEvent @event)
    {
        throw new NotImplementedException();
    }

    public bool OnPreUpdate(PreUpdateEvent @event)
    {
        throw new NotImplementedException();
    }
}

public class NHListener : IPreInsertEventListener, IPreUpdateEventListener

{

public bool OnPreInsert(PreInsertEvent @event)

{

throw new NotImplementedException();

}

public bool OnPreUpdate(PreUpdateEvent @event)

{

throw new NotImplementedException();

}

As You probably guessed OnPreInsert method is invoked before insert, and OnPreUpdate is called before update of entity. Having prepared the basic skeleton of class, it is time to configure NHibernate to use our event listeners. The basic configuration is taken from my previous post about NHibernate automappings, so I will only extend it by appending event listeners

Fluently.Configure().Database(
                    MsSqlConfiguration.MsSql2008.ConnectionString(
                        @"YourConnectionString"))
                    .Mappings(val => val.AutoMappings.Add(CreateAutomappings))
                    .ExposeConfiguration(val =>
                    {
                        val.AppendListeners(ListenerType.PreUpdate, new IPreUpdateEventListener[] { new NHListener() });
                        val.AppendListeners(ListenerType.PreInsert, new IPreInsertEventListener[] { new NHListener() });
                    })
                    .BuildSessionFactory();

Fluently.Configure().Database(

MsSqlConfiguration.MsSql2008.ConnectionString(

@"YourConnectionString"))

.Mappings(val => val.AutoMappings.Add(CreateAutomappings))

.ExposeConfiguration(val =>

{

val.AppendListeners(ListenerType.PreUpdate, new IPreUpdateEventListener[] { new NHListener() });

val.AppendListeners(ListenerType.PreInsert, new IPreInsertEventListener[] { new NHListener() });

})

.BuildSessionFactory();

Now we can put some logic into OnPreInsert and OnPreUpdate functions. First of all we have to somehow identify entities which hold information about creation and modification time. In order to do that I created simple interface IDateInfo

public interface IDateInfo
{
    DateTime ModificationDate { get; set; }
    DateTime CreationDate { get; set; }
}

public interface IDateInfo

{

DateTime ModificationDate { get; set; }

DateTime CreationDate { get; set; }

}

In the next step we have to modify OnPreInsert and OnPreUpdate methods, so that they can set creation and modification date of entities which implement IDateInfo interface.

public class NHListener : IPreInsertEventListener, IPreUpdateEventListener
{
    public bool OnPreInsert(PreInsertEvent @event)
    {
        IDateInfo dateInfo;
        if ((dateInfo = @event.Entity as IDateInfo) != null)
        {
            var currentDate = DateTime.Now;
            dateInfo.CreationDate = dateInfo.ModificationDate = currentDate;
        }
        return false;
    }

    public bool OnPreUpdate(PreUpdateEvent @event)
    {
        IDateInfo dateInfo;
        if ((dateInfo = @event.Entity as IDateInfo) != null)
        {
            var currentDate = DateTime.Now;
            dateInfo.ModificationDate = currentDate;
        }
        return false;
    }
}

public class NHListener : IPreInsertEventListener, IPreUpdateEventListener

{

public bool OnPreInsert(PreInsertEvent @event)

{

IDateInfo dateInfo;

if ((dateInfo = @event.Entity as IDateInfo) != null)

{

var currentDate = DateTime.Now;

dateInfo.CreationDate = dateInfo.ModificationDate = currentDate;

}

return false;

}

public bool OnPreUpdate(PreUpdateEvent @event)

{

IDateInfo dateInfo;

if ((dateInfo = @event.Entity as IDateInfo) != null)

{

var currentDate = DateTime.Now;

dateInfo.ModificationDate = currentDate;

}

return false;

}

Unfortunately at this moment saving entity (which implements IDateInfo interface) will throw exception

Despite the fact that we set values of ModificationDate and CreationDate properties , NHiberante seems not to notice that. That is why we have to manually update values of State object from PreInsertEvent and PreUpdateEvent. According to the documentation State object from class PreInsertEvent holds values which should be inserted and State object from class PreUpdateEvent holds values which should be updated. Function which update values of state object might look like that

private void SetState(IEntityPersister persister, object[] state, string propertyName, object value)
{
    var index = Array.IndexOf(persister.PropertyNames, propertyName);
    if (index == -1)
        return;
    state[index] = value;
}

private void SetState(IEntityPersister persister, object[] state, string propertyName, object value)

{

var index = Array.IndexOf(persister.PropertyNames, propertyName);

if (index == -1)

return;

state[index] = value;

}

Updated NHListener class now looks like that

using System;
using System.Linq.Expressions;
using NHibernate.Event;
using NHibernate.Impl;
using NHibernate.Persister.Entity;

namespace DataAccessLayer
{
    public class NHListener : IPreInsertEventListener, IPreUpdateEventListener
    {
        private static readonly string ModificationDatePropertyName = GetPropertyName<IDateInfo>(val => val.ModificationDate),
                                       CreationDatePropertyName = GetPropertyName<IDateInfo>(val => val.CreationDate);

        public bool OnPreInsert(PreInsertEvent @event)
        {
            IDateInfo dateInfo;
            if ((dateInfo = @event.Entity as IDateInfo) != null)
            {
                var currentDate = DateTime.Now;
                dateInfo.CreationDate = dateInfo.ModificationDate = currentDate;
                SetState(@event.Persister, @event.State, ModificationDatePropertyName, currentDate);
                SetState(@event.Persister, @event.State, CreationDatePropertyName , currentDate);
            }
    
            return false;
        }
    
        public bool OnPreUpdate(PreUpdateEvent @event)
        {
            IDateInfo dateInfo;
            if ((dateInfo = @event.Entity as IDateInfo) != null)
            {
                var currentDate = DateTime.Now;
                dateInfo.ModificationDate = currentDate;
                SetState(@event.Persister, @event.State, ModificationDatePropertyName, currentDate);
            }
            return false;
        }
        
        private void SetState(IEntityPersister persister, object[] state, string propertyName, object value)
        {
            var index = Array.IndexOf(persister.PropertyNames, propertyName);
            if (index == -1)
                return;
            state[index] = value;
        }
    
        private static string GetPropertyName<TType>(Expression<Func<TType, object>> expression)
        {
            return ExpressionProcessor.FindPropertyExpression(expression.Body);
        }
    }
}

using System;

using System.Linq.Expressions;

using NHibernate.Event;

using NHibernate.Impl;

using NHibernate.Persister.Entity;

namespace DataAccessLayer

{

public class NHListener : IPreInsertEventListener, IPreUpdateEventListener

{

private static readonly string ModificationDatePropertyName = GetPropertyName<IDateInfo>(val => val.ModificationDate),

CreationDatePropertyName = GetPropertyName<IDateInfo>(val => val.CreationDate);

public bool OnPreInsert(PreInsertEvent @event)

{

IDateInfo dateInfo;

if ((dateInfo = @event.Entity as IDateInfo) != null)

{

var currentDate = DateTime.Now;

dateInfo.CreationDate = dateInfo.ModificationDate = currentDate;

SetState(@event.Persister, @event.State, ModificationDatePropertyName, currentDate);

SetState(@event.Persister, @event.State, CreationDatePropertyName , currentDate);

}

return false;

}

public bool OnPreUpdate(PreUpdateEvent @event)

{

IDateInfo dateInfo;

if ((dateInfo = @event.Entity as IDateInfo) != null)

{

var currentDate = DateTime.Now;

dateInfo.ModificationDate = currentDate;

SetState(@event.Persister, @event.State, ModificationDatePropertyName, currentDate);

}

return false;

}

private void SetState(IEntityPersister persister, object[] state, string propertyName, object value)

{

var index = Array.IndexOf(persister.PropertyNames, propertyName);

if (index == -1)

return;

state[index] = value;

}

private static string GetPropertyName<TType>(Expression<Func<TType, object>> expression)

{

return ExpressionProcessor.FindPropertyExpression(expression.Body);

}

As You can see, after setting values of IDateInfo I also call function SetState and update state of appropriate properties. From now everything works fine and our entities can be saved without problems. Source code for this post can be found here

FluentNhibernate – mapowanie kolumn typu time

2012-03-132016-01-31 TPodolakFluentNhibernate, MsSql, Nhibernate

Witam
Ostatnio napotkałem na dość ciekawy wyjątek podczas wykonywania NHibernatowego inserta. W bazie danych mam prostą tabelę

Do takiej tabeli został stworzony model

namespace Model
{
    public class Appointment
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual DateTime StartDate { get; set; }
        public virtual TimeSpan Duration { get; set; }
    }
}

namespace Model

{

public class Appointment

{

public virtual int Id { get; set; }

public virtual string Name { get; set; }

public virtual DateTime StartDate { get; set; }

public virtual TimeSpan Duration { get; set; }

}

oraz mapping

namespace Model.Mappings
{
    public class AppointmentMapping : ClassMap<Appointment>
    {
        public AppointmentMapping()
        {
            Id(val => val.Id).GeneratedBy.Native();
            Map(val => val.Name).Not.Nullable();
            Map(val => val.StartDate).Not.Nullable();
            Map(val => val.Duration).Not.Nullable();
        }
    }
}

namespace Model.Mappings

{

public class AppointmentMapping : ClassMap<Appointment>

{

public AppointmentMapping()

{

Id(val => val.Id).GeneratedBy.Native();

Map(val => val.Name).Not.Nullable();

Map(val => val.StartDate).Not.Nullable();

Map(val => val.Duration).Not.Nullable();

}

Niestety ku mojemu zaskoczeniu próbując zapisać do bazy obiekt typu Appointment dostałem następujący wyjątek
SqlException: Operand type clash: bigint is incompatible with time

Mówiąc szczerze wyjątek ten niewiele mi powiedział, zwłaszcza, że zapytanie wygenerowane przez NHibernata (przechwycone w NHibernateProfilerze) wyglądało jak najbardziej poprawnie. Jednakże zapytanie przechwycone przez SqlProfilera wyglądało w następujący sposób

exec sp_executesql N'INSERT INTO [Appointment] (Name,StartDate,Duration) VALUES (@p0,@p1,@p2); 
select SCOPE_IDENTITY()',
N'@p0 nvarchar(4000),
@p1 datetime,
@p2 bigint',
@p0=N'appoitnemt',
@p1='2012-03-13 00:00:00',
@p2=439320000000

exec sp_executesql N'INSERT INTO [Appointment] (Name,StartDate,Duration) VALUES (@p0,@p1,@p2);

select SCOPE_IDENTITY()',

N'@p0 nvarchar(4000),

@p1 datetime,

@p2 bigint',

@p0=N'appoitnemt',

@p1='2012-03-13 00:00:00',

@p2=439320000000

Widać tutaj, że drugi parametr (@p2) został oznaczony jako bigint, a nie time (tak jak jest to oznaczone w bazie). Przyczyną takiego stanu rzeczy jest zły mapping. Z tego co wyczytałem, jeżeli chcemy aby poprawnie zmapować TimeSpana na kolumnę typu time, musimy zmodyfikować interesujący nas mapping na następujący.

Map(val => val.Duration).Not.Nullable().CustomType("TimeAsTimeSpan");

1	Map(val => val.Duration).Not.Nullable().CustomType("TimeAsTimeSpan");

Mając taki mapping nasze zapytanie zostanie wygenerowane poprawnie i operacja insertu zakończy się powodzeniem

.NET BLOG

.NET, C# blog

Nhibernate

NHibernate – generating WHERE IN … OR … queries

NHhibernate – multiple queries in one roundtrip to server

NHibarnate – using event listeners to set entity modification date

FluentNhibernate – mapowanie kolumn typu time