Derik Whittaker



Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at
Invalid Column name exception with FluentNhibernate and References (many-to-one)

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" )

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" )

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,

Posted 02-13-2009 2:51 AM by Derik Whittaker



Arjan`s World » LINKBLOG for February 13, 2009 wrote Arjan`s World &raquo; LINKBLOG for February 13, 2009
on 02-13-2009 3:59 PM

Pingback from  Arjan`s World    &raquo; LINKBLOG for February 13, 2009

Thomas G. Mayfield wrote re: Invalid Column name exception with FluentNhibernate and References (many-to-one)
on 02-13-2009 4:18 PM

Just wanted to confirm that you know that the foreign-key="" attribute names the foreign key, and has only a passing relationship with the column name (naming convention, if it exists).  If you were using NH to generate your schema, it'd create a foreign key constraint with that name ("DATASET_ID").  If you had more than one table/class with a foreign key set up exactly like that, you'd get an error.

If you aren't using NHibernate (or a custom tool that parses NHibernate mappings) to generate your schema, .WithForeignKey() is of no use.  And if you are using NH to generate schema, it can name foreign keys itself (though it does use GetHashCode() to generate some very ugly names).

Derik Whittaker wrote re: Invalid Column name exception with FluentNhibernate and References (many-to-one)
on 02-13-2009 4:24 PM


Yes i understand what the foreign-key does.  However, when using FluentNhibernate to create your mappings (with the current build) if you do not provide the .TheColumnNameIs() syntax it will attempt to assume the column name for you based on the 'normal' convention.

Thomas G. Mayfield wrote re: Invalid Column name exception with FluentNhibernate and References (many-to-one)
on 02-13-2009 4:31 PM

I think the point I'm trying to make is that the only way to give it that information (the column name) is to say .TheColumnNameIs().  FluentNHibernate shouldn't default to the name of the foreign key (again, only your person convention means they have the same name, and I would call into question that convention), or any other piece of data--there's nothing at all that can provide it with a column name for a many-to-one mapping short of you giving it one.

Derik Whittaker wrote re: Invalid Column name exception with FluentNhibernate and References (many-to-one)
on 02-13-2009 4:43 PM


You may be right, maybe FNH should not do that.  However, that is not the point of the post.  The point of the post is to help others who may have this issue.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Google Reader or Homepage Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of
Red-Gate Tools For SQL and .NET


SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
NHibernate Profiler
Balsamiq Mockups
JetBrains - ReSharper
Web Sequence Diagrams
Ducksboard<-- NEW Friend!


Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers


Community Server (Commercial Edition)