Fluent NHibernate: Foreign Key violation or Null values.

Question

Hey guys, I am having some real issues with mapping using fluent nhibernate. I realise there are MANY posts both on this site and many others focusing on specific types of mapping but as of yet, I have not found a solution that solves my issue.

Here is what I have:

namespace MyProject.Models.Entites
{
    public class Project
    {
       public virtual Guid Id {get; set;}
       // A load of other properties
       public virtual ProjectCatagory Catagory{get;set;}           
    }
}

and then the map:

namespace MyProject.DataAccess.ClassMappings
{
    public class ProjectMap : ClassMap<Project>
    {
        public ProjectMap()
        {
            Id(x => x.Id);
            Map(x => x.Title);
            Map(x => x.Description);
            Map(x => x.LastUpdated);
            Map(x => x.ImageData).CustomSqlType("image");
            HasOne(x => x.Catagory);           
        }
    }
}

So as you can see, I have a project which contains a catagory property. Im not so hot on relational databases but from what I can figure, this is a many-one relationship where many Projects can have one catagory. No, projects cannot fall into more than one category.

So now we have:

namespace MyProject.Models.Entities
{
   public class ProjectCatagory
   {
        public virtual Guid Id { get; set; }
        public virtual String Name { get; set; }
   }
}

and its map:

public ProjectCatagoryMap()
{
    Id(x => x.Id);
    Map(x => x.Name);
}

Issue is, well, it doesn't work ! I will do something similar to the following in a unit test:

Project myproject = new Project("Project Description");
// set the other properties
myProject.Catagory = new ProjectCatagory(Guid.New(), "Test Catagory");
repository.Save(myProject);

Now I have tried a number of mapping and database configurations when trying to get this to work. Currently, the Project database table has a column, "Catagory_id" (which i didnt put there, i assume NH added it as a result of the mapping) and I would LIKE it set to not allow nulls. However, when set as such, I get exceptions explaining that I cannot insert null values into the table (even though during a debug, i have checked all the properties on the Project object and they are NOT null).

Alternatively, I can allow the table to accept nulls into that column and it will simply save the Project object and totally disregard the Category property when saving, therefore, when being retrieved, tests to check if the right category has been associated with the project fails.

If i remember correctly, at one point I had the ProjectMap use:

References(x => x.Catagory).Column("Catagory_id").Cascade.All().Not.Nullable();

this changed the exception from "Cannot insert null values" to a foreign key violation.

I suspect the root of all this hassle comes from my lack of understanding of relational database set up as I have other entities in this project that do not have external dependencies which work absolutely fine with NHibernate, ruling out any coding issues I may of caused when creating the repository.

Any help greatly appreciated. Thank you.

Answer 1

The main issue here is a common misunderstand about the "one-to-one" relation in a relational database and the HasOne mapping in Fluent. The terms in the mapping are relational terms. (Fluent tries to "beautify" them a bit which makes it worse IMO. HasOne actually means: one-to-one.)

Take a look at the Fluent wiki:

HasOne is usually reserved for a special case. Generally, you'd use a References relationship in most situations (see: I think you mean a many-to-one).

The solution is very simple, just exchange HasOne with References (one-to-one to many-to-one in an XML mapping file). You get a foreign key in the database which references the ProjectCatagory.

---

A real one-to-one relation in a relational database is ideally mapped by a primary key synchronization. When two objects share the same primary key, then you don't waste space for additional foreign keys and it is ensured to be one-to-one.

To synchronize primary key, you need to hook up one's key to the others. However this works, it is not what you need here.

Answer 2

After playing around with all the available options for mapping. I found the answer to be similar to that suggested.

As was suspected, HasOne() was clearly wrong and References(x => x.Catagory) was part of the solution. However, I still received foreign key violation exceptions until:

References(x => x.Catagory).Column("Catagory_id").Cascade.SaveUpdate().Not.Nullable().Not.LazyLoad();

Just thought id update the thread in case someone else stumbles across this with a similar issue as just using References() did not work.