Fluent NHibernate generates extra columns

Question

We are using Fluent NHibernate for data object model in the company i work. A couple of days ago, we encountered an issue that Fluent NHibernate generates an extra column which does exist neither in model nor in mapping. Here is the situation:

My Model: FirstClass.cs

public class FirstClass
{
    public virtual int Id { get; private set; }
    public virtual SecondClass MyReference { get; set; }
    public virtual DateTime DecisionDate { get; set; }
}

My Mapping:

public class FirstClassMap : ClassMap<FirstClass>
{
    public FirstClassMap()
    {
        Id(x => x.Id);
        Map(x => x.DecisionDate);

        References(x => x.MyReference);
    }
}

After building the schema with the following code,

Instance._sessionFactory = Fluently.Configure()
                .Database(MySQLConfiguration.Standard
                    .ConnectionString(connectionString)
                    .ShowSql())
                .ExposeConfiguration(c =>
                {
                    c.Properties.Add("current_session_context_class", ConfigurationHelper.getSetting("SessionContext"));
                })
                .ExposeConfiguration(BuildSchema)
                .Mappings( m => m.FluentMappings.AddFromAssemblyOf<Community>())
                .BuildSessionFactory();

An extra column named "SecondClass_id" is produced with index and foreign key to SecondClass table with Id column. Here is the table produced:

CREATE TABLE `FirstClass` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `DecisionDate` datetime DEFAULT NULL,
  `MyReference_id` int(11) DEFAULT NULL,
  `SecondClass_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `MyReference_id` (`MyReference_id`),
  KEY `SecondClass_id` (`SecondClass_id`),
  CONSTRAINT `FK4AFFB59B2540756F` FOREIGN KEY (`MyReference_id`) REFERENCES `SecondClass` (`Id`),
  CONSTRAINT `FK4AFFB59B51EFB484` FOREIGN KEY (`SecondClass_id`) REFERENCES `SecondClass` (`Id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I found that, if I rename "MyReference" to "SecondClass" (same name as the class type), there is no extra column created. But i want to use my property with the name i specified, not with the class name. Why that extra column is created? How do i fix that? I don't want extra foreign key columns hanging around.

Benelux

you mean if you change property name different than object name it creates two field one is your_choosen_name other is object_name? – Selfsatisfaction

Sounds strange. What happens if you specify the column name explicitly. References(x => x.MyReference, "SecondClass_id"); – Couvade

which version of FNH do you use? I can't reproduce it with 1.1.0.685 and the code you posted – Gabel

@Cole W: I haven't tried specifying the column name, i will tell you when i try. – Benelux

@Firo: My version of fluent nhibernate is also 1.1.0.685, and NHibernate version is 2.1.2.4000. – Benelux

@Ottomanlast i have the same Versions. but i get following script create table FirstClass ( Id INTEGER NOT NULL AUTO_INCREMENT, DecisionDate DATETIME, MyReference_id INTEGER, primary key (Id) ) create table SecondClass ( Id INTEGER NOT NULL AUTO_INCREMENT, primary key (Id) ) alter table FirstClass add index (MyReference_id), add constraint FK5921A14627A0DDB3 foreign key (MyReference_id) references SecondClass (Id) sorry i cant help – Gabel

@Firo: thanks for your help. Maybe it is just bad luck :( – Benelux

Answer 1

This often happens when you're using FNH and you have a two-way relationship between entities.

public class FirstClass
{
    public virtual SecondClass MyReference { get; set; }
}

public class SecondClass
{
    public virtual List<FirstClass> ListOfFirstClass { get; set; }
}

public class FirstClassMap : ClassMap<FirstClass>
{
    public FirstClassMap()
    {
        References(x => x.MyReference);
    }
}

public class SecondClassMap : ClassMap<SecondClass>
{
    public SecondClassMap()
    {
        HasMany(x => x.ListOfFirstClass);
    }
}

To fix this you have to override the column name used in either ClassMap, like so:

public class SecondClassMap : ClassMap<SecondClass>
{
    public SecondClasssMap()
    {
        HasMany(x => x.ListOfFirstClass).KeyColumn("MyReference_id");
    }
}

or:

public class FirstClassMap : ClassMap<FirstClass>
{
    public FirstClassMap()
    {
        References(x => x.MyReference).Column("SecondClass_id");
    }
}

The reason for this is that FNH treats each mapping as a separate relationship, hence different columns, keys, and indexes get created.

Anaximenes

HasMany() doesn't have a Column() method, use KeyColumn() instead. – Precedent

@Gweebz thank you for poining out my typo, I've updated my answer. – Anaximenes

@Simon-Bartlett - Thank you for this. I am having issues with columns being duplicated as well. I had to specify the column name on both sides of the relationship for it to go away (KeyColumn and Column). I'm still having issues with the ID column being duplicated on the Child elements of HasMany though. – Effusion