Derik Whittaker

Syndication

News


Duplicated columns in generated sql from NHibernate

I was trying to profile some NHibernate sql to see if we could make some improvements.   One thing I noticed very shortly after starting this task was that one of my columns was being duplicated.  Now this is NOT going to make a huge performance difference but was something I wanted to remove this as it felt ‘wrong’.  Below is the original sql segment

 

resrole1_.PART_B_PROVIDER_NO             as PART16_29_0_,
resrole1_.ADMIN_SET_ID                   as ADMIN17_29_0_,
resrole1_.NATIONAL_PROVIDER_ID           as NATIONAL18_29_0_,
resrole1_.Admin_Set_ID                   as Admin17_29_0_,
resrole1_.Resource_Type                  as Resource19_29_0_,

The first thing I looked at when I started to look for the cause was the mapping for the entity (Role entity) to make sure the ‘Admin_Set_ID’ is not mapped multiple times.  And of course it was not.  After a few minutes of staring at the screen (everyone knows this is how all the hard problems are solved) it dawned on me that Admin_Set_ID was a FK from one of my associations.  Below is my association that was using it.

References( x => x.ResourceType )
	.Access.AsCamelCaseField( Prefix.Underscore )
        .WithColumns( "Admin_Set_ID", "Resource_Type" )
        .FetchType.Select()
        .LazyLoad();

Because Admin_Set_Id is a FK I should NOT have been mapping as part of my entity.  When I removed this column from my entity so that I could get it as part of my association.  When I did this my newly outputted sql looked like below:

resrole1_.PART_B_PROVIDER_NO             as PART16_29_0_,
resrole1_.ADMIN_SET_ID                   as ADMIN17_29_0_,
resrole1_.NATIONAL_PROVIDER_ID           as NATIONAL18_29_0_,
resrole1_.Resource_Type                  as Resource19_29_0_,

Long story short is this.  If you happen to see a column being duplicated in your generated sql make sure that you are not referencing/mapping a FK directly in your mappings. 

Hope this helps.

Till next time,


Posted 01-25-2010 7:57 AM by Derik Whittaker
Filed under:

[Advertisement]

Comments

Andriy Buday wrote re: Duplicated columns in generated sql from NHibernate
on 01-25-2010 6:15 PM

This have something in common to your another article:

<a href>devlicio.us/.../a>

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)