HP, ProLiant, Windows 7, Windows Vista, Usb, Boot, Windows 8, Build, Nhibernate.Envers, Custom, SQL, Scramble, NHibernate, Fetch, Query, Nhibernate, Linq, Equals

Sometimes the basic information provided by the default revision entity we have in NHibernate.Envers are not enough, when we need to extend those information and provide additional data we have the option to use a customized version of the Revision Entity class.

The default revision entity is defined like this:

[Serializable]
public class DefaultRevisionEntity
{
	[RevisionNumber]
	public virtual int Id { get; set; }

	[RevisionTimestamp]
	public virtual DateTime RevisionDate { get; set; }

	public override bool Equals(object obj)
	{
		if (this == obj) return true;
		var revisionEntity = obj as DefaultRevisionEntity;
		if (revisionEntity == null) return false;

		var that = revisionEntity;

		if (Id != that.Id) return false;
		return RevisionDate == that.RevisionDate;
	}

	public override int GetHashCode()
	{
		var result = Id;
		result = 31 * result + (int)(((ulong)RevisionDate.Ticks) ^ (((ulong)RevisionDate.Ticks) >> 32));
		return result;
	}
}

You can notice two properties that defines the information that a Revision Entity class MUST have, they are also marked by two configuration attributes:

• [RevisionNumber] - states which property will represent the revision number of the version
• [RevisionTimestamp] - states the timestamp of the version

Every custom Revision Entity class MUST have these two kind of properties, once again we will use the fluent by code configuration so we will not need to decorate our classes with attributes (I’ll show how to configure NHibernate.Envers using attributes in another post).

Our custom Revision Entity class for this example will be:

//[RevisionEntity]
public class REVINFO
{
	//[RevisionNumber]
	public virtual long Id { get; set; }

	//[RevisionTimestamp]
	public virtual DateTime CustomTimestamp { get; set; }

	public virtual string Data { get; set; }

	public override bool Equals(object obj)
	{
		var casted = obj as REVINFO;
		if (casted == null)
			return false;
		return (Id == casted.Id &&
				CustomTimestamp.Equals(casted.CustomTimestamp) &&
				Data.Equals(casted.Data));
	}

	public override int GetHashCode()
	{
		return Id.GetHashCode() ^ CustomTimestamp.GetHashCode() ^ Data.GetHashCode();
	}
}

As you can see we have the two basic properties plus an additional Data field that will represent our custom data added to each revision tracked.

To be able to fill these data we need to implement a particular type of interface: an IRevisionListener. This interface expose only a single member function - void NewRevision(object) - and it’s called every time a new revision object is generated. The demo code is:

public class RevInfoListener : IRevisionListener
{
	public static string Data = "test data";

	public void NewRevision(object revisionEntity)
	{
		((REVINFO)revisionEntity).Data = Data;
	}
}

The last step is to change the configuration to ‘inject’ our custom Revision Entity class and Listener; in my test project I do it with the following code:

public class NHinitCustomRevInfo : NHibernateInitializer
{
	protected override System.Collections.Generic.IEnumerable<System.Type> GetDomainEntities()
	{
		// I am using ConfORM to emit the mappings, so we add it to the mapped classes.
		return base.GetDomainEntities().Union(new[] { typeof(REVINFO) });
	}
	
	protected override void ConfOrmMapping(ConfOrm.ObjectRelationalMapper orm, ConfOrm.NH.Mapper mapper)
	{
		// I have to provide a mapping for a custom RevInfo class...NHibernate must know how to handle these objects
		orm.TablePerClass<REVINFO>();
		
		orm.TablePerClass<Person>();
		orm.TablePerClass<Game>();
	}

	public void InitializeAudit()
	{
		// initialize the NHibernate.Envers fluent configuration object
		var enversConf = new NHibernate.Envers.Configuration.Fluent.FluentConfiguration();

		// I prefer to not use attributes to configure the custom revision entity
		enversConf.SetRevisionEntity<REVINFO>(e => e.Id, e => e.CustomTimestamp, typeof(RevInfoListener));
		
		// the RevInfo class must not be in the auditing list
		enversConf.Audit(GetDomainEntities().Where(e => !typeof(REVINFO).IsAssignableFrom(e)));

		// to inspect the metadata
		//var mets = enversConf.CreateMetaData(Configure);

		// Configure.Properties.Add("nhibernate.envers.audit_table_prefix", string.Empty); // default
		Configure.Properties.Add("nhibernate.envers.audit_table_suffix", "_REV"); // default _AUD
		// Configure.Properties.Add("nhibernate.envers.revision_field_name", "REV"); // default
		// Configure.Properties.Add("nhibernate.envers.revision_type_field_name", "REVTYPE"); // default

		Configure.IntegrateWithEnvers(enversConf);
	}
}

I am using ConfORM and my mapping engine, so part of the configuration is related to how it’s used to generate the mappings, but the concepts are valid for any other mapping tools you use; Let’s go through it:

• Line 6: we add the REVINFO class to my mapped domain, ConfORM need to be aware of it.
• Line 12: we tell ConfORM how to map the class.
• Line 21: we initialize the NHibernate.Envers fluent configuration engine.
• Line 24: this is the crucial point, we specify our custom Revision Entity class type, we tell Envers which functions to use to fill in the Revision incremental number and the Revision Timestamp; plus we pass in the type of the IRevisionListener implementation if we want to specify additional data in the custom revision entity class.
• Line 27: we tell to NHibernate.Envers which classes we want to track (be careful to exclude the REVINFO class from the tracked ones here).
• Line 37: we add NHibernate.Envers to the standard NHibernate configuration.

If we create a simple test and execute it we can inspect the schema of the generated database to see the custom revision entity in place:

Figure 1 - Database Schema with Custom Revision Entity

That’s all for this post, next time we’ll see how to query for the extended revision entity properties we just added.

This entry was posted on Friday, 05 August, 2011 and is filed under .Net  NHibernate . You can follow any responses to this entry through here . You can leave a response.

I did it again! That’s why I’m writing this blog post...I do it almost every time and the main reason is: trying to optimize NHibernate queries without thinking.

As the title of the posts says the problem here is related to fetching the data of different collections attached to a single entity. To show you up the problem let’s first see the test domain, it is very simple:

public class Post : Entity<int>
{
	protected Post()
	{
	}

	public Post(string slug)
	{
		Slug = slug;
		Categories = new List<Category>();
		Tags = new List<Tag>();
	}

	public string Slug { get; set; }

	public IList<Category> Categories { get; set; }

	public IList<Tag> Tags { get; set; }	
}

public class Category : Entity<int>
{
	public string Name { get; set; }
}

public class Tag : Entity<int>
{
	public string Name { get; set; }
}

I generated some test data consisting of a single post which has 2 categories and 2 tags associated to it.

We want to query the Post object together with all it’s data, the first thing we can do is this:

[Test]
public void NoFetch_Success()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// look for a non existing revision
		using (var tx = s.BeginTransaction())
		{
			var p = s.CreateQuery("from Post p where p.Slug = :slug")
				.SetParameter("slug", "Slug")
				.UniqueResult<Post>();

			tx.Commit();

			Assert.IsNotNull(p);
			Assert.AreEqual(2, p.Categories.Count);
			Assert.AreEqual(2, p.Tags.Count);
		}
	}
}

Straightforward and simple; this thing produces 3 different queries, one for each kind of object:

select post0_.Id as Id0_, post0_.Slug as Slug0_ from Post post0_ where post0_.Slug=@p0;@p0 = 'Slug' [Type: String (4000)]

SELECT categories0_.PostId as PostId1_, categories0_.Id as Id1_, categories0_.Id as Id1_0_, categories0_.Name as Name1_0_ FROM Category categories0_ WHERE categories0_.PostId=@p0;@p0 = 1 [Type: Int32 (0)]

SELECT tags0_.PostId as PostId1_, tags0_.Id as Id1_, tags0_.Id as Id2_0_, tags0_.Name as Name2_0_ FROM Tag tags0_ WHERE tags0_.PostId=@p0;@p0 = 1 [Type: Int32 (0)]
		

The returned data are all correct.

But we really have too many queries here and we want to optimize it a bit (in order to limit/prevent the impact of the n+1 selects problem), so in my optimization berserker fury I started using fetch joins to retrieve more data in a single pass and I wrote this query:

from Post p left join fetch p.Categories left join fetch p.Tags where p.Slug = :slug

which is an absolute mistake, because it generates duplicated data; let’s see some test code:

[Test]
public void FetchALL_Failure()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// look for a non existing revision
		using (var tx = s.BeginTransaction())
		{
			// try adding a: select distinct p 
			var p = s.CreateQuery("from Post p left join fetch p.Categories left join fetch p.Tags where p.Slug = :slug")
				.SetParameter("slug", "Slug")
				.UniqueResult<Post>();

			tx.Commit();

			Assert.IsNotNull(p);
			Assert.AreEqual(2, p.Categories.Count); // <- big exception... we have 4 rows
			Assert.AreEqual(2, p.Tags.Count);
		}
	}
}

which produces 1 single query:

select post0_.Id as Id0_0_, categories1_.Id as Id1_1_, tags2_.Id as Id2_2_, post0_.Slug as Slug0_0_, categories1_.Name as Name1_1_, categories1_.PostId as PostId0__, categories1_.Id as Id0__, tags2_.Name as Name2_2_, tags2_.PostId as PostId1__, tags2_.Id as Id1__ from Post post0_ left outer join Category categories1_ on post0_.Id=categories1_.PostId left outer join Tag tags2_ on post0_.Id=tags2_.PostId where post0_.Slug=@p0;@p0 = 'Slug' [Type: String (4000)]

But the data are all wrong! In fact the Assert that should check for the correct numbers of categories are telling me that I have 4 rows inside that collection. I should have reminded earlier what the NHibernate documentation says about this situation:

It is possible to create a cartesian product by join fetching more than one collection in a query, so take care in this case. Join fetching multiple collection roles is also disabled for bag mappings. Note also that the fetch construct may not be used in queries called using Enumerable(). Finally, note that full join fetch and right join fetch are not meaningful. (Reference documentation, 13.3 Associations and joins)

Note that adding a ‘select distinct p’ will not help in this case.

To fix this we need to have just one fetch join at each level of the hierarchy; we can write something like this:

var p = s.CreateQuery("from Post p left join fetch p.Categories where p.Slug = :slug")
						.SetParameter("slug", "Slug")
						.UniqueResult<Post>();

This code will generate 2 queries (one to get the Post and the Categories and one to get the Tags) and the test will pass:

select post0_.Id as Id12_0_, categories1_.Id as Id13_1_, post0_.Slug as Slug12_0_, categories1_.Name as Name13_1_, categories1_.PostId as PostId0__, categories1_.Id as Id0__ from Post post0_ left outer join Category categories1_ on post0_.Id=categories1_.PostId where post0_.Slug=@p0;@p0 = 'Slug' [Type: String (4000)]
		
SELECT tags0_.PostId as PostId1_, tags0_.Id as Id1_, tags0_.Id as Id14_0_, tags0_.Name as Name14_0_ FROM Tag tags0_ WHERE tags0_.PostId=@p0;@p0 = 1 [Type: Int32 (0)]

But...wait! .UniqueResult<T>() here is smart enough to hide us a fetch join side effect; if we use .List<T>() instead we will see it clearly: the fetch introduces ‘ghosts’ in our returned resultset; the data will be a combination of Posts and Categories fields, so for any single Post object we will have ‘n’ records (one for each Category bound to the Post) and NHibernate will output ‘n’ Post entities, all of them containing the same data.

The solution here is pretty simple: just add the ‘distinct’ keyword to the select statement (or use the DistinctRootEntityTransformer), this way we are telling NHibernate to give us back all the unique instances of Post objects together with their Categories, here is the code:

[Test]
public void FetchSingleCollection_Distinct_Success()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// look for a non existing revision
		using (var tx = s.BeginTransaction())
		{
			var p = s.CreateQuery("select distinct p from Post p left join fetch p.Categories where p.Slug = :slug")
				.SetParameter("slug", "Slug")
				.UniqueResult<Post>();
			tx.Commit();

			Assert.IsNotNull(p);
			Assert.AreEqual(2, p.Categories.Count);
			Assert.AreEqual(2, p.Tags.Count);
		}
	}
}

If I remind it well, there’s no need to use the distinct on the queries generated using Linq to NHibernate, because the provider does it for you.

As usual here’s the test project for those who want to play with it:

I am hoping, now that I’ve wrote it down, to not make the same mistake again ...

This entry was posted on Tuesday, 19 July, 2011 and is filed under .Net  NHibernate . You can follow any responses to this entry through here . You can leave a response.

In the previous articles of this series we saw how to initialize the environment to use NHibernate.Envers and how to query it for getting information about the revisions it creates.

It’s now time to focus on the methods that allows us to actually retrieve the different revisions details (such as the state of the whole entity at a given revision and similar) and the history of the modifications an object has been subject to.

Once again our operation entry point is the IAuditReader object, but this time we will use its CreateQuery() method.

CreateQuery() returns an AuditQueryCreator object that exposes all the functions we need to perform search queries against the database; AuditQueryCreator provides a series of normal methods that specifies the entity we want to look for using a typeof(T) argument as their first parameter. It also exposes the generic version of those methods and we’ll focus our attention on those (because I hate to write down typeof() everywhere, if I can avoid it), the non generic functions are however very similar and their usage should be clear anyway.

Each of these generic functions will return an IEntityAuditQuery<T> object, these kind of objects work exactly like the usual NHibernate ICriteria API: we can add search criteria to filter out the results, order the results, perform pagination, add projections and so on.

To get the results from an IEntityAuditQuery object we can use two methods:

• .Single() - it will return a single instance of the class representing the result, or an exception if more than one result is available.
• .Results() - it will return an enumeration of the class representing the result.

Actually we have tree different methods we can use to build up queries against our revision database tables, let’s see them in detail:

ForEntitiesAtRevision<T>

Method signature: IEntityAuditQuery<T> ForEntitiesAtRevision<T>(long revision)

The query built using this method will ultimately return to us an IEnumerable<T> (or a single instance of T, or the result of a projection), the enumeration will contains all the instances of the specified type of entity at the requested revision; one or more filtering conditions can be added:

[Test]
public void ForEntitiesAtRevision()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// look for a non existing revision
		using (var tx = s.BeginTransaction())
		{
			IAuditReader auditReader = s.Auditer();
			var rev = auditReader.CreateQuery().ForEntitiesAtRevision<Person>(1).Results();
			Assert.IsNotNull(rev);
			Assert.That(rev.Count() == 2);
			// print the data on the console
			Utils.PrintEntityList(rev);
			tx.Commit();
		}
	}
}

in line 10 I am asking to see all the entities of type Person at their revision number 1, given my test domain this is the result:

***** NHibernate.Envers.Tests.Querying.Test2.ForEntitiesAtRevision
System.Linq.Enumerable+WhereSelectListIterator`2[System.Collections.IDictionary,NHibernate.Envers.Tests.Domain.Person] , count: 2
Id: 1, Name: Jhon, Surname: Doe, Note: 
Games:
Id: 1, Name: g0, Type: , Rating: 0, Note: note0
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
---

Id: 2, Name: Jane, Surname: Doe, Note: 
Games:
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
Id: 3, Name: g2, Type: , Rating: 2, Note: note2
---

As you can see I have two entity at that revision number; if I wanted to filter out the results I could have written something similar in line 10:

var rev = auditReader.CreateQuery().ForEntitiesAtRevision<Person>(1)
.Add(AuditEntity.Property("Name").Eq("Jhon")).Results();

ForRevisionsOf<T>

Method signature: IEntityAuditQuery<T> ForRevisionsOf<T>(bool includesDeleted)

The query built using this method will ultimately return to us an IEnumerable<T> (or a single instance of T, or the result of a projection), the enumeration will contains all the instances of the specified type of entity; one or more filtering conditions can be added.

If includesDeleted is set to true the revisions where the entities were deleted will be returned (the default value is ‘false’).

The results of the query will be sorted in ascending order by the revision number, unless an order or projection is added.

Here’s an example:

[Test]
public void ForRevisionsOf()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// look for a non existing revision
		using (var tx = s.BeginTransaction())
		{
			IAuditReader auditReader = s.Auditer();
			var rev = auditReader.CreateQuery().ForRevisionsOf<Person>(true).Results();
			Assert.IsNotNull(rev);
			Utils.PrintEntityList(rev);
			tx.Commit();
		}
	}
}

This is the ‘messy’ result:

***** NHibernate.Envers.Tests.Querying.Test2.ForRevisionsOf
System.Linq.Enumerable+WhereSelectListIterator`2[System.Collections.IDictionary,NHibernate.Envers.Tests.Domain.Person] , count: 4
Id: 1, Name: Jhon, Surname: Doe, Note: 
Games:
Id: 1, Name: g0, Type: , Rating: 0, Note: note0
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
---

Id: 2, Name: Jane, Surname: Doe, Note: 
Games:
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
Id: 3, Name: g2, Type: , Rating: 2, Note: note2
---

Id: 2, Name: Jane, Surname: Doe, Note: Modified
Games:
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
Id: 3, Name: g2, Type: , Rating: 2, Note: note2
---

Id: 1, Name: Jhon, Surname: Doe, Note: Modified
Games:
Id: 1, Name: g0, Type: , Rating: 0, Note: note0
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
---

To be honest this function is not so useful without adding some filters.

ForHistoryOf<TEntity>

Method signature: IEntityAuditQuery<IRevisionEntityInfo<TEntity, DefaultRevisionEntity>> ForHistoryOf<TEntity>(bool includesDeleted)

The query built using this method will ultimately return to us an IEnumerable<IRevisionEntityInfo<TEntity, DefaultRevisionEntity>> (or a single instance of IRevisionEntityInfo<TEntity, DefaultRevisionEntity>, or the result of a projection), the enumeration will contains all the revision information of the instances of the specified type of entity; one or more filtering conditions can be added.

If includesDeleted is set to true the revisions where the entities were deleted will be returned (the default value is ‘false’).

The results of the query will be sorted in ascending order by the revision number, unless an order or projection is added.

This function is extremely useful because you can obtain detailed information about the revision number, the timestamp and the kind of operation (added, modified, deleted) alongside with the snapshot of the entity itself; all these information are available through an implementation of the IRevisionEntityInfo<TEntity, TRevisionEntity> interface.

Here’s an example:

[Test]
public void ForHistoryOf()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// look for a non existing revision
		using (var tx = s.BeginTransaction())
		{
			IAuditReader auditReader = s.Auditer();
			var rev = auditReader.CreateQuery().ForHistoryOf<Person>(true).Results();
			Assert.IsNotNull(rev);
			Utils.PrintRevisionInfoList(rev);
			tx.Commit();
		}
	}
}

And here’s the results I get with my domain:

***** NHibernate.Envers.Tests.Querying.Test2.ForHistoryOf
System.Linq.Enumerable+<CastIterator>d__aa`1[NHibernate.Envers.Query.IRevisionEntityInfo`2[NHibernate.Envers.Tests.Domain.Person,NHibernate.Envers.DefaultRevisionEntity]] , count: 4
Rev=1 Timestamp=2011-07-13T14:19:32.1470000 Op=Added
Id: 1, Name: Jhon, Surname: Doe, Note: 
Games:
Id: 1, Name: g0, Type: , Rating: 0, Note: note0
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
---

Rev=1 Timestamp=2011-07-13T14:19:32.1470000 Op=Added
Id: 2, Name: Jane, Surname: Doe, Note: 
Games:
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
Id: 3, Name: g2, Type: , Rating: 2, Note: note2
---

Rev=2 Timestamp=2011-07-13T14:19:32.1630000 Op=Modified
Id: 2, Name: Jane, Surname: Doe, Note: Modified
Games:
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
Id: 3, Name: g2, Type: , Rating: 2, Note: note2
---

Rev=2 Timestamp=2011-07-13T14:19:32.1630000 Op=Modified
Id: 1, Name: Jhon, Surname: Doe, Note: Modified
Games:
Id: 1, Name: g0, Type: , Rating: 0, Note: note0
Id: 2, Name: g1, Type: , Rating: 1, Note: note1
---

With this post we have covered pretty much all the basic functionalities that NHibernate.Envers offers us to query the revision database tables, take a look at the new examples in the test solution if you want to see it in action live:

Cya next.

This entry was posted on Wednesday, 13 July, 2011 and is filed under .Net  NHibernate . You can follow any responses to this entry through here . You can leave a response.

A couple of days ago Matteo Migliore asked me a question about why String.Equals(string, StringComparison) was not supported in Linq to NHibernate while it was in Entity Framework.

My first answer was that the Linq to NHibernate implementation might have some missing and, if it was not supported, we can extend the provider to add support for missing functions if we know how to translate them in SQL (I even wrote a couple of blog posts in the past about how to extend the provider to add functions to it: NHibernate 3 - Extending the Linq Provider to fix some System.NotSupportedException and NHibernate - Customize the Linq provider to call your user defined SQL functions).

With those things in mind we have all the tools to cover the gaps and implement what we need; basically we want to ‘force’ a case insensitive string comparison at database level using something like this:

var res = Session.Query().Where(t => t.T1.Equals("test", StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();

I’m not a true SQL expert, but when it comes to string comparison a lot of database stuff kicks in (like collations and such) and every database can have a different default behavior to compare strings (by default it should be case insensitive, but I’m not really sure of this...so DBA experts can say their own here); aside of that, I think that the default behavior ORMs have is to rely on what the database does.

Matteo confirmed this last statement looking at the SQL expression that Entity Framework 4 generated for his test domain when calling Equals passing in an xxxIgnoreCase StringComparison, in both case (with and without the parameter) EF4 generated this SQL statement:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Customers] AS [Extent1]
WHERE N'temp' = [Extent1].[FirstName]

As you can see this is totally dependent on the database and it can ignore our will to perform a case insensitive equals (due to the database collation for example)...so it’s not a ‘perfect’ translation of what the user wanted to do and it may introduce subtle bugs in your code.

Linq to NHibernate raises us a ‘warning’ saying that this kind of operation is not supported (intentionally or not...we’ll never know...), so the first step is to extend the provider to support this kinds of equals:

public class EqualsStringGenerator : BaseHqlGeneratorForMethod
{
	public EqualsStringGenerator()
	{
		SupportedMethods = new[]
		                   	{
		                   		ReflectionHelper.GetMethodDefinition(x => x.Equals(null, StringComparison.CurrentCulture))
		                   	};
	}

	public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
	{
		return treeBuilder.Equality(
			visitor.Visit(targetObject).AsExpression(),
			visitor.Visit(arguments[0]).AsExpression());
	}
}

public class ExtendedLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
	public ExtendedLinqtoHqlGeneratorsRegistry()
	{
		this.Merge(new EqualsStringGenerator());
	}
}

You can read my previous articles for the details, however the important thing here is that we are adding a new supported method (the overloaded version of String.Equals) and how we translate it to SQL in the BuildHql() function: we are basically ignoring the second parameter - the StringComparison - and we are constructing a normal Equality between two operands.

This extension makes Linq to NHibernate operate exactly as EF4 does and will generate a similar kind of SQL query (it completely relies on the database for the case sensitivity or not of the operation).

But we can do the things a little bit better and add support for forcing a case insensitive equals even if case sensitive collations are used for your database, making Linq-To-NHibenrate a little bit smarter. The trick is simple: add a SQL LOWER() call to both the sides of the equality operator:

public class EqualsStringGenerator : BaseHqlGeneratorForMethod
{
	public EqualsStringGenerator()
	{
		SupportedMethods = new[]
		                   	{
		                   		ReflectionHelper.GetMethodDefinition(x => x.Equals(null, StringComparison.CurrentCulture))
		                   	};
	}

	public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
	{
		StringComparison comparison = (StringComparison)(arguments[1].As().Value);
		if (comparison == StringComparison.CurrentCultureIgnoreCase ||
		    comparison == StringComparison.InvariantCultureIgnoreCase ||
		    comparison == StringComparison.OrdinalIgnoreCase)
		{
			return treeBuilder.Equality(
				treeBuilder.MethodCall("lower", new[] { visitor.Visit(targetObject).AsExpression() }),
				treeBuilder.MethodCall("lower", new[] { visitor.Visit(arguments[0]).AsExpression() }));
		}
		return treeBuilder.Equality(
			visitor.Visit(targetObject).AsExpression(),
			visitor.Visit(arguments[0]).AsExpression());
	}
}

Using the treeBuilder.MethodCall(“lower”, ...) we are adding the desired call, the good thing is that this will be database independent (as long as your database support this function, otherwise you can extend the NHibernate dialect for your database and add support for it); this is what NHibernate now generates for my test domain:

NHibernate: select TOP (@p0)  table1x0_.ID as ID7_, table1x0_.T1 as T2_7_, table1x0_.T2 as T3_7_, table1x0_.GuidTest as GuidTest7_, table1x0_.Tb2 as Tb5_7_, table1x0_.Tb3 as Tb6_7_ from TABLE1 table1x0_ where lower(table1x0_.T1)=lower(@p1);@p0 = 1 [Type: Int32 (0)], @p1 = 'test' [Type: String (4000)]

This is far from being an optimized solution and we are not translating correctly all the behaviors of this overloaded version of the string comparison, nonetheless is a good starting point for it.

How to force then a case sensitive string comparison ? One thing that comes into my mind is to extend the provider once again and to cast both the operands to a binary type, in this way the comparison will be over array of bytes and it will be ‘sensitive’ by default...but I’ll leave this to you .

This entry was posted on Tuesday, 14 June, 2011 and is filed under .Net  Linq  NHibernate . You can follow any responses to this entry through here . You can leave a response.

We’ve already saw how to setup NHibernate.Envers, how to configure and how to create the schema for it (NHibernate.Envers - a quick introduction).

To query for our revision entities we need to use the IAuditReader interface, we have two ways to obtain it:

• Using the AuditReaderFactory.Get(ISession) method.
• Using the ISession.Auditer() extension method.

Once we have the object in place we can start querying for data. There isn’t many documentation around (here’s the only link I was able to find and it’s from the original Java project: Hibernate Envers - Easy Entity Auditing); so your best bet is to build the documentation file from the source project or to download the source code itself and look at the comments there.

As you saw in the previous article NHibernate.Envers approaches the entity version problem defining a unique master table to hold a global revision number to which attach the history of the entities that are part of a single revision.

It creates a REVINFO table and maps it over a DefaultRevisionEntity class.

In this article we’ll focus on getting information about the Revision object, this is the class that actually identifies the current revision number and revision timestamp (it can be customized as we’ll see in a next article of this series).

Let’s see each function in detail (I’ll just look at the generic version, because I hate to write typeof() everywhere):

T GetCurrentRevision<T>(bool persist)

“Gets an instance of the current revision entity, to which any entries in the audit tables will be bound. Please note the if persist is false, and no audited entities are modified in this session, then the obtained revision entity instance won't be persisted. If persist is true, the revision entity instance will always be persisted, regardless of whether audited entities are changed or not”.

This very simple test (assuming we have an empty database) will give you an idea of how it work:

// assuming an empty database
using (ISession s = _nh.SessionFactory.OpenSession())
{
	using (var tx = s.BeginTransaction())
	{
		IAuditReader auditReader = s.Auditer();
		DefaultRevisionEntity o = auditReader.GetCurrentRevision<DefaultRevisionEntity>(false);
		tx.Commit();
		Assert.IsNotNull(o);
		// it's transient
		Assert.AreEqual(0, o.Id);
	}
	using (var tx = s.BeginTransaction())
	{
		IAuditReader auditReader = s.Auditer();
		DefaultRevisionEntity o = auditReader.GetCurrentRevision<DefaultRevisionEntity>(true);
		tx.Commit();
		Assert.IsNotNull(o);
		// it's persisted
		Assert.AreEqual(1, o.Id);
	}
}

Looking at the returned class you can have information about the Revision to which your (current or next) changes will be attached to.

T FindRevision<T>(long revision)

Gets the RevisionEntity at the given revision number, if the revision doesn’t exist a ‘RevisionDoesNotExistException’ will be raised.

The following tests will shot its usage:

[Test]
public void FindRevision_DoesNotExists()
{
	// assuming an empty database
	Assert.Throws<RevisionDoesNotExistException>(() =>
	{
		using (ISession s = _nh.SessionFactory.OpenSession())
		{
			// look for a non existing revision
			using (var tx = s.BeginTransaction())
			{
				IAuditReader auditReader = s.Auditer();
				var rev = auditReader.FindRevision<DefaultRevisionEntity>(1);
				Assert.IsNotNull(rev);
				tx.Commit();
			}
		}
	});
}

[Test]
public void FindRevision_Success()
{
	// assuming an empty database
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// create a revision
		using (var tx = s.BeginTransaction())
		{
			// create a person
			Person p = new Person("Jhon", "Doe");
			s.SaveOrUpdate(p);
			tx.Commit();
		}
		using (var tx = s.BeginTransaction())
		{
			IAuditReader auditReader = s.Auditer();
			var rev = auditReader.FindRevision<DefaultRevisionEntity>(1);
			Assert.IsNotNull(rev);
			tx.Commit();
		}
	}
}

In the first one you will get an exception when asking for a revision that does not exists, in the second you will properly get the information you requested in the form of the default revision entity class.

IDictionary<long, T> FindRevisions<T>(IEnumerable<long> revisions)

“Find a map of revisions using the revision numbers specified”. If the revisions do not exist an empty dictionary will be returned. If you ask for a revision number that does not exist, it will just not be returned in the resulting dictionary (no exception will be thrown).

The following test cover the case of requesting a non existent revision:

[Test]
public void FindRevisions_NonExistentRevision()
{
	using (ISession s = _nh.SessionFactory.OpenSession())
	{
		// create a revision
		using (var tx = s.BeginTransaction())
		{
			// create a person
			Person p = new Person("Jhon", "Doe");
			s.SaveOrUpdate(p);
			tx.Commit();
		}
		// look for a non existent revision
		using (var tx = s.BeginTransaction())
		{
			IAuditReader auditReader = s.Auditer();
			var rev = auditReader.FindRevisions<DefaultRevisionEntity>(new long[] { 1, 2 });
			Assert.IsNotNull(rev);
			Assert.AreEqual(1, rev.Count);
			Assert.AreEqual(1, rev[1].Id);
			tx.Commit();
		}
	}
}

DateTime GetRevisionDate(long revision)

“Get the date, at which a revision was created”. The precision is database and mapping specific; the default mapping uses ‘timestamp’ as its NHibernate type (this will include the milliseconds in the date and we all know the problems with the milliseconds rounding in some database engines). If you want another behavior you will need to customize the default revision entity class (more in a next post).

If the revision doesn’t exist a ‘RevisionDoesNotExistException’ will be raised.

long GetRevisionNumberForDate(DateTime date)

Gets the revision number of the highest revision which was created on or before the given date. As you can imagine this function is pretty useless if you do not know the exact date (up to the milliseconds).

If the revision doesn’t exist a ‘RevisionDoesNotExistException’ will be raised.

Enough for today, the next time we’ll take a look at more specific queries that will involve our tracked entities too.

You can get the actual same source code here, it will contain one or more tests for each of the functions reported before:

This entry was posted on Friday, 10 June, 2011 and is filed under .Net  NHibernate . You can follow any responses to this entry through here . You can leave a response.

Recently I had the need to introduce a sort of ‘Entity Versioning’ in a portion of my applications, the whole data access strategy was realized using NHibernate + XML / ConfORM mappings.

I started the thing introducing an poor auditing system using a listener and the pre-insert and pre-update events. This will allow me to track who created a record and the last one that modified it (and when) in an easy way (you can find some examples on this technique in a lot of blog posts around, just look for NHibernate + auditing in any web search engine).

The afore mentioned strategy is enough for the great part of my domain, but then I had to move a step forward and not only log who make the action, but also I needed to store the state of the entity at the time it was modified (being it updated or deleted), in short I need the whole history of the changes on that entity. My first thought was to extend my original approach and save the entity in another version of the previous listener...huge work to do!

Then I reminded Fabio Maulo talking about the release of NHibernate.Envers at one of the last UgiAlt workshops I attended to...Gotcha! Let’s take a look at it and see if it fits my needs.

Download the whole source code at https://bitbucket.org/RogerKratz/nhibernate.envers, you need to if you use the latest bits of NHibernate 3.2 because you will need to update the referenced libraries and recompile the project.

Let’s start considering this very simple domain:

public class Person : Entity<int>
{
	protected Person()
	{
	}

	public Person(string name, string surname)
	{
		Games = new List<Game>();
		Name = name;
		Surname = surname;
	}

	public string Name { get; set; }

	public string Surname { get; set; }

	public string Note { get; set; }
	
	public IList<Game> Games { get; set; }

	public override string ToString()
	{
		StringBuilder sb = new StringBuilder();
		sb.AppendFormat("Id: {2}, Name: {0}, Surname: {1}, Note: {3}\n", Name, Surname, Id, Note);
		sb.AppendLine("Games:");
		foreach (var g in Games)
			sb.AppendLine(g.ToString());
		sb.AppendLine("---");
		return sb.ToString();
	}
}

public class Game : Entity<int>
{
	public string Name { get; set; }

	public string Type { get; set; }

	public int Rating { get; set; }

	public string Note { get; set; }

	public override string ToString()
	{
		return string.Format("Id: {3}, Name: {0}, Type: {1}, Rating: {2}, Note: {4}", Name, Type, Rating, Id, Note);
	}
}

here we have a person and the games he plays; both the classes are mapped as individual entities using ConfORM.

To keep the post short I’ll skip the mapping initialization, so we can move to the interesting bits: how to setup NHibernate.Envers. You have several ways to set it up, the easiest (and more flexible) one is configuring it by code:

• Declare FluentConfiguration object (which is nothing more than an IMetaDataProvider).
• Call its Audit() or Audit<>() methods to decide which entities you want to track.
• Attach it to the NHibernate Configuration object calling its IntegrateWithEnvers extension method.

Here’s how it looks like:

var enversConf = new NHibernate.Envers.Configuration.Fluent.FluentConfiguration();

// audit the whole domain
// enversConf.Audit(GetDomainEntities());

enversConf.Audit<Person>();
enversConf.Audit<Game>();

Configure.IntegrateWithEnvers(enversConf);

If we ask NHibernate to generate the schema for us this is a picture of what we get:

Figure 1 - domain model + auditing tables database schema

As you can see the one-to-many relation between Person and Games has been correctly transformed in a many-to-many when it comes to track objects history.

By default we’ll have a main table named REVINFO that will assign a unique revision number to every version of the object we track, we’ll have an history table for every entity and for every relation between objects (these tables use the suffix _AUD). all those tables maintain a relation with the main REVINFO that provide the timestamp of the operation; moreover another column is added to every history table (named REVTYPE) that states the type of operation performed on the object (update, delete, etc...).

NHibernate.Envers allows you to change the default naming of tables and suffixes with a series of new properties you can specify in the NHibernate configuration section, here are some:

• nhibernate.envers.audit_table_prefix - defaults to String.Empty
• nhibernate.envers.audit_table_suffix - defaults to “_AUD”
• nhibernate.envers.revision_field_name - defaults to “REV”
• nhibernate.envers.revision_type_field_name - default to “REVTYPE”

Other interesting options are:

• nhibernate.envers.default_catalog - defaults to String.Empty, allows you to set a specific catalog for the auditing tables.
• nhibernate.envers.default_schema - defaults to String.Empty, allows you to set a specific schema for the auditing tables.
• nhibernate.envers.store_data_at_delete - defaults to false, if set to true all the data of the removed entity will be saved when you remove it (instead of having just a marker with all null values).

Enough for today, the next time we’ll see some methods to retrieve the history of the changes related to the tracked entities.

You can grab the actual sample code here:

I’m always amazed to see that every time I have a problem related to the data access strategy and what’s around it, NHibernate always seems to something that, if not fully solve your problems, can point you in right direction to customize it and have your job done.

This entry was posted on Tuesday, 07 June, 2011 and is filed under .Net  NHibernate . You can follow any responses to this entry through here . You can leave a response.