Derik Whittaker : HowTo, NHibernate

Hey, NHibernate give me back my milliseconds

Derik Whittaker — Mon, 22 Feb 2010 22:47:00 GMT

Have you ever noticed that out of the box NHibernate’s DateTime type will truncate/ignore your milliseconds for DateTime fields? If you do not believe me check out this post. If you think about why it does this it will become clear, NHibernate runs against MANY databases and each one of them stores DateTime values slightly different. However all of them store basic YYYY-MM-DD : HH:MM:SS.

If you google how to resolve this issue you will find a few a post that suggests you create a custom User Type (IUserType). Although I am sure this will work, this just seems like such overkill for the problem.

What I was able to do was setup my mapping in FluentNHibernate as such

Map( x => x.MyField, "My_Field" ).CustomTypeIs("timestamp");

By adding the .CustomTypeIs attribute (i tried CustomSqlTypeIs but this did not work) NHibernate was able to save my DateTime property into a DateTime (not Timestamp or DateTime2) field with the milliseconds inplace.

Hope this helps,

Till next time,

NHiberante and Join types in your mappings Select vs Join

Derik Whittaker — Mon, 11 Jan 2010 20:34:30 GMT

When you are setting up your NHibernate mappings you must pay close attention to the way you setup your relationships between your entities. Recently I started profiling some of our statements and was shocked to see how some of them had a metric-crap ton of joins when they only needed one or two. After a bit of looking I realized that we had explicitly set FetchType to .Join (via Fluent NHibernate) and this was a big no-no.

When you setup your associations to be join associations each time you use a entity you will get (for free :) ) the associations for that entity. However, as you may guess this can be very, very expensive and not the desired outcome. Below is how changing my FetchType from .Join to .Select (also, .Select is the default if you do not specify any FetchType).

This is the SQL output when everything was setup for .Join

FROM   RES_Basic this_
       inner join Res_Role roles1_
         on this_.RESOURCE_ID = roles1_.RESOURCE_ID
       left outer join O_BRANCH branch4_
         on roles1_.BRANCH_ID = branch4_.BRANCH_ID
       left outer join A_PROVIDER_TAXONOMY providerta5_
         on roles1_.TAXONOMY_ID = providerta5_.TAXONOMY_ID
       left outer join O_TEAM team6_
         on roles1_.TEAM_ID = team6_.TEAM_ID
       left outer join RES_Basic resource7_
         on roles1_.RESOURCE_ID = resource7_.RESOURCE_ID

This is the output after we changed to .Select (and setup my criteria to explicitly reference my Res_Role table)

FROM   RES_Basic this_
       inner join Res_Role roles1_
         on this_.RESOURCE_ID = roles1_.RESOURCE_ID

As you can see, when I do it right I am only joining to the tables I want and this can lead to a huge performance savings.

Till next time,

NHibernate and “The multi-part identifier … could not be bound” Exception

Derik Whittaker — Tue, 05 Jan 2010 12:48:42 GMT

Today as I was creating a new NHibernate Criteria statement everything was working fine UNTIL I added the where clause. At this point I started to get the following exception (which was seen via NHibernate Profier).

WARN:
System.Data.SqlClient.SqlException: The multi-part identifier "doc2_.NAME" could not be bound.

My criteria statement looked something like this

var query = Session.CreateCriteria( typeof(DocumentSection), "docSection" )
    .CreateAlias( "docSection.DocumentSegment", "docSegment", JoinType.None )
    .CreateAlias( "docSegment.DocumentDefinition", "doc", JoinType.InnerJoin )
    .... MORE STUFF HERE

    .Add( Restrictions.Eq( "doc.Name", documentName ) )

    .SetResultTransformer( new DistinctRootEntityResultTransformer() )
    .List();

When I first saw this error it did not make any sense as I KNEW that there was in fact a Name field on the object I was trying to hydrate. But if you look closely at the first .CreateAlias you will notice the issue. For my JoinType I had set it to JoinType.None and not JoinType.Inner so when NHibernate wanted to hydrate my object it could not because as far as it was concerned the object was not meant to be hydrated.

To resolve this issue I changed the JoinType to be Inner and all worked. Below is the working code.

var query = Session.CreateCriteria( typeof(DocumentSection), "docSection" )
    .CreateAlias( "docSection.DocumentSegment", "docSegment", JoinType.InnerJoin )
    .CreateAlias( "docSegment.DocumentDefinition", "doc", JoinType.InnerJoin )
    .... MORE STUFF HERE

    .Add( Restrictions.Eq( "doc.Name", documentName ) )

    .SetResultTransformer( new DistinctRootEntityResultTransformer() )
    .List();

Till next time

NHProf and performance issues while running under VM

Derik Whittaker — Tue, 04 Aug 2009 20:55:00 GMT

Recently I was forced to do full time development on my MacBook because I killed my Windows Laptop (btw, turns out it is a bad idea to spill milk on the keyboard of a laptop… who knew). When doing this I was running Windows7 under VM via Fusion. Everything was working great……until I fired up NHProf.

The second I fired up NHProf my VM basically stopped working. To say the least Ii was a bit perplexed. To confirm it was NHProf and not something else, I killed the app to see if everything would go back to normal.. sure enough it did.

At this point I was pretty sure NHProf was the culprit. In order to figure out the issue or if this was an isolated issue I asked the twitterverse and of course my good buddy Rob Eisenberg (who happens to be one of the authors of the GUI) asked me to try something.

He asked me to turn off hardware acceleration on the gfx card and try it again. I did as he asked and…. bam, the problem went away. Rob is not 100% sure what the cause is, but he is currently having a conversation with the WPF team to see if there is a known issue with this and will report back his findings and I will do so as well.

So, long story short. If running under VM and NHProf is dogging make sure you have hardware acceleration turn off and that should resolve your issue.

Till next time.

Joining NHibernate Transaction with ADO SqlTransaction

Derik Whittaker — Mon, 15 Jun 2009 16:35:54 GMT

Recently (ok, today) I needed to drop down into ADO to call a procedure in our code. I was not too happy with it, but either my lack of knowledge or lack of will to figure it out mandated it. What I needed to do was create a build-insert process and using NHibernate was not the right solution for performance reasons.

When I first created my code, seen below my tests worked fine.

public void AssignNewDestinationToPatients( Destination destination )
{
    var connection = (SqlConnection) Session.Connection;
    
    var sqlCommand = new SqlCommand
                         {
                             CommandType = CommandType.StoredProcedure,
                             CommandText = StoredProcedures.DO_SOMETHING,
                             Connection = connection,                                     
                         };

    sqlCommand.Parameters.Add( new SqlParameter( "DestinationID", destination.DestinationID ) );

    sqlCommand.ExecuteNonQuery()    ;
}

However, once this call was wrapped around an NH transaction i would receive the following error:

{"ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."}

Of course the first I tried to do was cast the NH transaction to a sql transaction and provide that to the command object. However, that is NOT possible. The great news for me was the NH has the capacity to enlist a sql commmand and add it to its transaction. The code below shows how to do this.

public void AssignNewDestinationToPatients( Destination destination )
{
    var connection = (SqlConnection) Session.Connection;
    
    var sqlCommand = new SqlCommand
                         {
                             CommandType = CommandType.StoredProcedure,
                             CommandText = StoredProcedures.DO_SOMETHING,
                             Connection = connection,                                     
                         };

    // this is the magic code
    if ( Session.Transaction != null && Session.Transaction.IsActive )
    {
        Session.Transaction.Enlist( sqlCommand );
    }

    sqlCommand.Parameters.Add( new SqlParameter( "DestinationID", destination.DestinationID ) );

    sqlCommand.ExecuteNonQuery()    ;
}

So, if you need to drop down to ADO from NHibernate and you need to use transactions, this solution should work out well for you.

Till next time,

P.S. If there is a better, simpler way please let me know.

NHibernate & SetProjections/SetProjections w/ List Gotcha

Derik Whittaker — Wed, 27 May 2009 15:21:00 GMT

Today I was needing to create a query which looked a bit like this:

SELECT   max(this_.SEQUENCE) as y0_,
         this_.ADMISSION_ID as y1_
FROM     PT_Status this_
GROUP BY this_.ADMISSION_ID

As you can see, I was trying to do a group by one column and a Max() on another column (legacy DB’s gotta love them).

Since I only wanted to project a few properties I decided to use project and provide a list of columns I wanted, my code looked like:

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()
	.SetProjection( 
		Projections.ProjectionList()                    
        		.Add( Projections.Property( "Sequence" ) ) 
	                .Add( Projections.Property( "AdmissionID" ) )                    
			)                
	.SetProjection( Projections.Max( "Sequence" ) )
	.SetProjection( Projections.GroupProperty( "AdmissionID" ) )
        .SetResultTransformer( Transformers.AliasToBean( typeof( Status ) ) );

When I would run this code it would not give me the sql I wanted, and everything looked ok. Then through trial and error I realized my issue. The code below is the code which works

var detachedCriteria = CriteriaBuilder.CreateCritieraFor()
	.SetProjection( 
		Projections.ProjectionList()                    
        		.Add( Projections.Max( "Sequence" ) ) 
	                .Add( Projections.GroupProperty( "AdmissionID" ) )                    
			)                
        .SetResultTransformer( Transformers.AliasToBean( typeof( Status ) ) );

If you look at the first example i am both setting up my column projections and my group/max projects, but they are on the same column (not needed) and my group/max projections is OUTSIDE my first projection list. Turns out that I only needed my first projection list as it would setup the group/max as well as my return columns.

Hope this helps.

Till next time,

Pay attention to the difference between Expression.Lt & Expression.LtProperty

Derik Whittaker — Thu, 07 May 2009 01:33:20 GMT

Pop quiz, what is the difference between Expression.Lt (.Gt, .Eq, etc) and Expression.LtProperty (.GtProperty, .EqProperty, etc)? If you know the answer off the top of your head you win the prize… What is the prize you may ask? NOTHING….

Anyway, I needed to create a criteria query where I wanted to filter out the data where the date in one table was Less Than the date in another table. I thought this was pretty straight forward. I created an expression part like below

criteria.Add( Expression.Lt( “DateFieldOne”, “DateFieldTwo ) );

However, when I ran my test to see if my criteria was correct I received the following error:

NHibernate.QueryException: Type mismatch in NHibernate.Criterion.SimpleExpress: DateFieldOne expected type System.DateTime, actual System.String

I have to say that I as stumped, everything looked right and I even took a look at the restriction information which was generated. You can see it below.

{(DateFieldONe < DateFieldTwo)}

To the untrained eye (that would be mine) it looked right. After a few minutes of staring at this (and scratching my ass for a bit :) ) it dawned on me. NHibernate was not comparing my fields, it was trying to compare the column on the left (DateFieldOne) to the ‘string’ value of DateFieldTwo. At this point I knew what I needed to do… Use the .LtProperty. When I changed from .Lt to .LtProperty everything worked out just right.

My new criteria logic looks like:

criteria.Add( Expression.LtProperty( “DateFieldOne”, “DateFieldTwo ) );

So the moral of the story is this. When you are trying to create a criteria selection and you are trying to compare 2 columns, not a column against a value make sure you use the *Property command on the Expression object (.LtProperty, .GtProperty, .EqProperty, etc).

Hope this helps,

Till next time,

Debugging NHibernate Exceptions

Derik Whittaker — Tue, 05 May 2009 09:14:00 GMT

When developing your data access layer with NHibernate you are bound to run into some pretty nasty exceptions. Many times the exceptions may be nasty, but at least they explain the problem in enough detail to allow you to solve the problem. But what do we do when the exceptions do not convey enough information to allow us to simply and easily spot our issue? Simply, time to bust out the shovel and pick ax and start digging for the nugget of information which will allow you to find your solution.

The good news is there is at least 2 (could be many more) ways you can get detailed information on what NHibernate is doing and what possible be wrong.

Utilize NHibernate Profiler
If you are using NHibernate and you are NOT using NHProfiler then you are basically writing code with one hand tied behind your back. This tool can and will help you solve so many problems you will be amazed.

But when hunting bugs NHProf is practically useful because it gives you a very clean way to see the exact sql which is being generated and executed in real time (or damn close to it). Implementing NHprof could not be easier and takes a mere 3 steps and 1 line of code.

To Implement all you do is

Add reference to HibernatingRhinos.NHibernate.Profiler.Appender.dll class in your project.
Add this one line of code at the entry point of your application (or unit test)
HibernatingRhinos.NHibernate.Profiler.Appender.NHibernateProfiler.Initialize();
Launch the GUI HibernatingRhinos.NHibernate.Profiler.Client.exe

Utilize the logging which is build into NHibernate via Log4Net
Now sometimes even NHProfiler is not going to solve your problems and when this is the case you are in for some fun. I have found the best way to debug what is going on is to turn on logging for Nhibernate. Now I am not going to go into depth on how to do this as post on Hibernateing Rhinos and Nhiberate.org do a great job of providing this information.

Once you have your app.config or your web.config setup all you need to do is add this line of code at the entry point of your application (or unit tests)
log4net.Config.XmlConfigurator.Configure()

When implementing logging with Log4Net I would suggest 2 things

Turn on DEBUG logging, this will produce a ton of content but is needed
If using a file appender up the max file size to be at least 1mb. Anything lower will simply produce too many files and you will be swimming in a see of log files

If you utilize the above 2 techniques for trouble shooting/debugging your nhibernate issues you should be able to resolve most of them in no time flat.

Till next time,

Creating a nested ‘Or’ statement with NHibernate using the Criteria convention

Derik Whittaker — Tue, 21 Apr 2009 21:26:33 GMT

As I pointed in a prior post (here) when trying to learn to harness the power of NHibernate sometimes the ‘simple’ can be a bit daunting at first. For instance how do you create a query with NHibernate that looks a little like the information below?

Where ....
	AND 
	( 
		( a = x AND b = true )
		or
		( a = y AND b = true )
		or 
		( a = z AND b = true )
	)

This would be actually pretty easy if all you needed to do was have a single Or statement because there is a helper method in NHibernate (Expresssion.Or) which takes in 2 parts. However, what do you do if you need to both have more than 2 parts to your Or statement as well as add in a And clause to each one. Well, you have to break away from the helper methods and go straight to the Restrictions class.

The code below is a snippet which should help to show how you can build a criteria which creates the final sql in the same way as above.

// criteria is an instance of the DetachedCriteria object

var queuedCriterion = new List<AbstractionCriterion>();

// build out each of the 'Or' statements
foreach( var someObject in listOfSomeObjects )
{
	var partOneExpression = Expression.Eq( "A", "x" );
	var partTwoExpression = Expression.Eq( "B", true );
	queuedCriterion.Add( Expression.And( partOneExpression, partTwoExpression ) );
}

// put each of the 'Or' statements into one disjunction.
// this will allow them to be nested on the same level
var orDisjunction = Restrictions.Disjunction();
foreach ( var criterion = queuedCriterion )
{
	orDisjunction.Add( criterion );
}

// finally add the full list 'Or' into the main criteria object
criteria.Add( orDisjunction )

As you can see above there is a bit of code needed to create this sql syntax when using the criteria, but it is not too bad (IMO). The more I learn about NHibernate, the more I really start to appreciate its power and flexibility.

Till next time,

Creating a Select Count(*) with NHibernate using Criteria

Derik Whittaker — Thu, 16 Apr 2009 12:18:19 GMT

As you are learning how to use NHibernate many of the ‘common’ things you are used to doing with standard SQL crop up. One of them for me was to have the ability to perform a Count() query. If I were using straight SQL my query may look something like this:

SELECT COUNT( FieldNameHere )
FROM SomeTableTable 
WHERE Field1Here = 51 
	AND Field2Here = 1 
	AND Field3Here = 1

Like many things in Nhibernate there are many ways to solve the same problem, but one way I found was to create the criteria query as below:

var count = (Int32) Session.CreateCriteria( typeof( SomeTableHere ) ) 
        .Add( Expression.Eq( "Field1Here", 1) ) 
        .Add( Expression.Eq( "Field2Here", (Int32) 2) ) 
        .Add( Expression.Eq( "Field3Here", 3) ) 
	.SetProjection( Projections.Count( "CountFieldHere" ) ).UniqueResult();

And the result from the above syntax would produce the following (the sql below was given to me via NHProfiler – kick ass tool EVERYONE should be using).

SELECT count(this_.OUTBOUND_STAGING_QUEUE_ID) as y0_ 
FROM   SomeTableHere this_ 
WHERE  this_.Field1Here= 51 /* @p0 */ 
       and this_.Field2Here = 1 /* @p1 */ 
       and this_.Field3Here = 1 /* @p2 */

As you can see creating a count query with NHibernate is pretty straight forward and will generate the exact SQL you would expect.

Till next time,

Simple example of using a Subquery in NHibernate when using ICriteria

Derik Whittaker — Mon, 06 Apr 2009 21:41:00 GMT

Of the many things that NHibernate is, simple and ‘straight forward’ it is not (at times). Recently I needed to do a SubQuery, so of course the first thing I did was try to Google it. I was able to find quite a few examples on how to create and execute a subquery, but none of them had ALL the information I was looking for.

What was I looking for, well the ‘dummy’ overview on what I should do. To be exact I wanted the following:

Database layout (most had this, not all)
Raw sql as if you were doing it by hand (a few had this)
Clean/Simple example of how to create the SubQuery (some had this)
- Details on what the query was actually doing (not sure I saw this)
The generated output of the sql which was created by NHibernate (some had this)

Before we get started with creating and looking at the code, maybe it would be best if I gave an overview of the what I was trying to do which required a subquery. I needed to return all header records (from Message_Queue table) who had a child record (from Message_Queue_Status table )which was in a given state.

Database Diagram:
Below you will find a simple database diagram of the 2 tables I was trying to query against. The parent table in this diagram is the Message_Queue table. This table hold the main information for a message which is going to be sent in our system. The child table is the Message_Queue_Status table, this holds a record for the message for each step in the process. With this table we can know at any point in time what status the sending message is in.

Raw SQL of what I needed NHibernate to generate:
What I did prior to trying to setup NHibernate was to manually create the sql for what I needed. I do this many times because I can whip out the sql pretty quickly and easily and it provides me a better picture of what I need NHibernate to do. The raw sql I created is below. (Where you see the values 2 & 4, those would be enums when translated to NHibernate)

SELECT MQ.*
FROM [MESSAGE_QUEUE] MQ
WHERE MQ.[DIRECTION_TYPE_ID] = 2
	AND MQ.[MESSAGE_QUEUE_ID] IN ( 
		SELECT MESSAGE_QUEUE_ID FROM [MESSAGE_QUEUE_STATUS] WHERE [STATUS_TYPE_ID] <= 4
	 )

NHibernate code needed to create the query:
Once I knew what I needed to do, it was only a matter of translating that need over to NHibernate. The thing that stumped me at first was how exactly to create the subquery. The code below is the final working example needed to build the equivalent of the sql above.

var subCriteria = DetachedCriteria.For();
subCriteria.SetProjection( Projections.Property( "MessageQueue.MessageQueueID" ) )
	.Add( Expression.Le( "StatusTypeID", 4 ) );

var criteria = session.CreateCriteria( typeof( MessageQueue ) )
	.Add( Expression.Eq( "DirectionTypeID", (Int32) MessageDirectionType.Outbound ) )
        .Add( Subqueries.PropertyIn( "MessageQueueID", subCriteria ) );

Taking a look at the NHibernate code above, you really need to break down into two parts.

The subquery
The top part is the logic/code needed in order to build:
SELECT MESSAGE_QUEUE_ID FROM [MESSAGE_QUEUE_STATUS] WHERE [STATUS_TYPE_ID] <= 4

Once you have this part built you will need to apply it to the main part of the query
The main query
The main query is where all the magic happens. This is where you need to apply your sub-select and where the data will be filtered.

You will notice that I am using the ‘Subqueries.PropertyIn’ syntax. This will generate the the following SQL
MQ.[MESSAGE_QUEUE_ID] IN ( ….. ) which is what you are looking for.
You should also take note that I am using “MessageQueueID” as my property, but when it gets rendered it is the correct column name in the db as defined by the mappings. I point this out as many first time NHibernate users (myself being one of them) will try to use the db column name, not the property name.

Now if you have done everything correctly you should be able to run your sub query and it will produce the data you are looking for.

I will also point out that if you are NOT using NHProfiler for your NHibernate development, you are simply wasting debug cycles.

Hope this helps someone.

Till next time,

NHibernate and ‘Invalid Index N for this SqlParameterCollection with Count=N error’

Derik Whittaker — Thu, 19 Mar 2009 16:00:08 GMT

If you have ever used NHibernate I am sure you have encounted the error ‘Invalid Index ‘N’ for this SqlParameterCollection with Count=N’ (where N is any number) exception when trying to create your mappings.

If you are completely new to NHibernate, as I am I am sure this one has you scratching your head saying…. WTF.

The good news is that ‘normally’ the solution to this problem is pretty easy, but before I tell you the answer let me explain the issue.

Take a look at the code below

public EpisodeMap()
{
	WithTable( "Episode" );

	Id( x => x.ID ).GeneratedBy.Identity();

        Map( x => x.LevelTypeID );
        Map( x => x.Name );
        Map( x => x.Description );
        Map( x => x.EpisodeNumber );
        Map( x => x.EpisodeDate );
        Map( x => x.CreatedDate );
	Map( x => x.Enabled );

	References( x => x.EpisodeLevel )
        .WithForeignKey( "LevelTypeID" ).TheColumnNameIs( "LevelTypeID" )
        .Access.AsCamelCaseField( Prefix.Underscore )
	.FetchType.Join();

}

Take notice to the mapping above. I have a many-to-one mapping for EpisodeLevel, but I have also created and mapped the FK to EpisodeLevel as LevelTypeID.

The issue (as i have experienced it) is this:

Because I have mapped my FK to the Episode Level table as .LevelTypeID as well as to the EpisodeLevel entity NH is going to try to create multiple associations on that field. However it cannot because that is not correct.

To solve this issue (mostly in my experience) all you need to do is remove the following line
Map( x => x.LevelTypeID );

If you MUST populate the LevelTypeID property at the top level, do so by providing the value as a pass through from Episode Level as such:

public Int32 LevelTypeID
{
	get { return EpisodeLevel.LevelTypeID; }
}

I hope this helps someone.

Till next time,

Verifying that that your data is Eagerly loaded in NHibernate

Derik Whittaker — Fri, 06 Mar 2009 13:27:11 GMT

In my last post I talked about how I needed to eager load my data in order to send it back across the wire. Normally I am not a big fan of creating tests to ensure that I can hit the database, but in this case the act of eager loading needs to be verified and constant.

When I set out to create my tests, I was not exactly sure how I could verify this. The good news is there is a utility class called NHibernateUtil which has a method on it called .IsInitialized() which will return a bool. Below is the unit test I put together to ensure my data was always being eagerly loaded. Yes I know that this test does NOT take into account any new data which may need to be eagerly loaded, but it is at least a step in the right direction.

[Test]
public void FetchDestinations_EnsureDataIsEagerLoaded()
{
    var repository = new DestinationRepository();

    IList destinations = repository.FetchDestinations();

    // want to ensure that the data was eager loaded as we need/want
    var destination = destinations.FirstOrDefault();

    // incase there is no data in the DB
    if ( destination != null )
    {
        Assert.That( NHibernateUtil.IsInitialized( destination.Endpoint ), Is.True );
        Assert.That( NHibernateUtil.IsInitialized( destination.AssignedMessageTypeses ), Is.True );                
    }
}

As you can see from my test, I do need to add in come logic to ensure I am hitting my database and gathering data. I am normally not a fan of this, but I think it is needed here as I do not want to fail my test if there is no data. When this test runs I will always know that my data is being eagerly loaded which fulfills my needs.

Till next time,

Eager loading a One-to-Many collection w/ NHibernate

Derik Whittaker — Thu, 05 Mar 2009 21:14:44 GMT

At work I have a section of code which I need to eager load all my data in order to pass that data back across the wire. I thought I had it all setup correctly, but then I realized that I was not returning back unique results from my query.

Imagine I have the following Schema

My current need was to get all Destinations in the database (we know it is a small number and this is for the edit screen) and have it eager load the endpoint (which you actually get for free) and the Assigned_Message_Types

Originally setup my Criteria selection as below

var result = Session.CreateCriteria( typeof( Destination ) )                                
	.SetFetchMode( "Endpoint", FetchMode.Eager )
        .SetFetchMode( "AssignedMessageTypeses", FetchMode.Eager )        
        .List();

When I ran the query above, I expected to get 2 records back, one for each destination in the database. However, I received 4 of them back. The first 3 were the same, and the 4th one was different. As you could imagine this is NOT what I expected. I thought I would reach out to Kyle Baley (@kbaley) for some help on this, but turns out he was having the same issue recently. Good thing for both of us the Twitterverse was able to lend a helpful hand. James Kovacs (@jamesKovacs) along with someone else (sorry, forgot our handle) pointed both of us to the following 2 posts.

I decided to get the usage of DistinctRootEntityResultTransformer a try, and the code below is what worked for me.

var result = Session.CreateCriteria( typeof( Destination ) )                                
	.SetFetchMode( "Endpoint", FetchMode.Eager )
        .SetFetchMode( "AssignedMessageTypeses", FetchMode.Eager )        
	
	// this is the magic line    
        .SetResultTransformer( new DistinctRootEntityResultTransformer() )
        .List();

By adding the DistinctRootEntityResultTransformer transformer to my query I now get 2 rows of data, which is exactly what I needed/wanted.

Till next time,

Invalid Column name exception with FluentNhibernate and References (many-to-one)

Derik Whittaker — Fri, 13 Feb 2009 08:51:36 GMT

One exception you may run across while using Fluent Nhibernate (or straight up NHibernate) is System.Data.SqlClient.SqlException: Invalid column name ‘……’. This is normally caused because you did not provide the correct column name when doing your mapping.

Lets take a look at the code below which is trying to map a many-to-one relationship to another entity.

References( x => x.BusinessUnit )
        .Access.AsCamelCaseField( Prefix.Underscore )                
        .WithForeignKey( "DATASET_ID" )
	.FetchType.Join

As you can see from the code we are trying to setup the mapping to our BusinessUnit entity, and we are providing the foreign key as ‘DataSet_ID’. However when we generate the .hbm.xml file and run the code it throws the Invalid Column name exception saying that it cannot find BusinesUnit_ID.

Here is the .hml.xml snippet for this mapping

<many-to-one access="field.camelcase-underscore" foreign-key="DATASET_ID" fetch="join" name="BusinessUnit" column="BusinessUnit_id" />

At first glance you may be wondering why it is trying to use BusinessUnit_ID as the column name. The reason is that FluentNHibernate assumes that the column u are mapping against in the patent is the ‘id’ value of the entity (BusinessUnit_ID). However, in my case this is not true. In order to resolve this I simply need to tell FluentNHiberhate what the true column name is. I can do so with the following code

References( x => x.BusinessUnit )
        .Access.AsCamelCaseField( Prefix.Underscore )                
        .WithForeignKey( "DATASET_ID" )
	.TheColumnNameIs( "DATASET_ID" )
	.FetchType.Join

When I run this code, it will generate the following .hbm.xml file

<many-to-one access="field.camelcase-underscore" foreign-key="DATASET_ID" fetch="join" name="BusinessUnit" column="DataSet_ID" />

So, if you ever find you self with the Invalid column name exception make sure you have completely setup your mappings.

Till next time,