Fluent Nhibernate error Foreign Key

Question

I know that this issue has some questions about it, but I can't find the right answer, so please let me ask this question to see if someone could give me the right answer

I have the following scheme for my DB (tables an dsimplified to focus on the problem)

Table Project
idProject INT PK
projectName string UNIQUE
numOfItems INT 

Table Item
serialNumber integer PK
idProject integer PK, FK (references idProject table Project)
fileName string PK

Table Analysis
serialNumber integer PK, FK (references serialNumber table Item)
dateMeasure Date PK
fileName string PK

I have those tables coded in C# as follows

class Analysis{
        public virtual Item serialNum{ get; set; }
        public virtual DateTime dateMeasure { get; set; }
        public virtual string fileName { get; set; }

        public override int GetHashCode(){
             return (fileName.GetHashCode() * serialNum.GetHashCode() * dateMeasure.GetHashCode());
        }

        public override bool Equals(object obj){
            if (obj == null || obj.GetType() != GetType()) return false;

            Analysis a = (Analysis)obj;

            return (a.serialNum == serialNum && a.fileName == fileName && a.dateMeasure == dateMeasure);
        }
}

class Item{
        public virtual int serialNumber { get; set; }
        public virtual Proyecto idProject { get; set; }
        public virtual string fileName { get; set; }
        public virtual DateTime measureDate { get; set; }

        public override int GetHashCode(){
            return (fileName.GetHashCode() * serialNumber.GetHashCode() * idProject.GetHashCode());
        }

        public override bool Equals(object obj)
        {
            if (obj == null || obj.GetType() != GetType()) return false;

            Item i = (Item)obj;

            return (i.serialNumber == serialNumber&& i.fileName== fileName&& d.idProject == idProject);
        }
}

class Project
    {
        public virtual int idProject { get; set; }
        public virtual string projectName{ get; set; }
        public virtual int numItems { get; set; }
    }

And the following mappings of the entites

class AnalysisMap: ClassMap<Analysis>
    {

        public AnalysisMap()
        {
            CompositeId()
                .KeyReference(x=> x.serialNumber)
                .KeyProperty(x => x.dateMeasure)
                .KeyProperty(x => x.fileName);
        }
}

class ItemMap : ClassMap<Item>
    {
        public ItemMap()
        {
            CompositeId()
                .KeyProperty(x => x.fileName)
                .KeyReference(x => x.idProject)
                .KeyProperty(x => x.serialNumber);
            Map (x=>x.measureDate).Column("dateMeasure").Not.Nullable();
            References(x => x.idProject).Column("idProject");
        }
    }

class ProjectMap : ClassMap<Project>
    {
        public ProjectMap()
        {
            Id(x => x.idProject).GeneratedBy.Identity().Column("idProject");
            Map(x => x.projectName).Column("projectName").Unique();
            Map(x => x.NumItems).Column("numOfItems").Not.Nullable().Default("0");
        }
    }

So when I tried to open the session with the following code I get the error "Foreign key (FK9CF1483E7BAABE07:Analysis [serialNum])) must have same number of columns as the referenced primary key (Item [fileName, idProject, serialNumber])"

try{
   ISessionFactory sf = Fluently.Configure()
   .Database(SQLiteConfiguration.Standard.InMemory().ShowSql()
   .ConnectionString("server=local;Data Source= data_source;Integrated Security=SSPI;"))
   .Mappings(m => m.FluentMappings.AddFromAssemblyOf<Project>()
   .AddFromAssemblyOf<Item>()
   .AddFromAssemblyOf<Analysis>()).BuildSessionFactory();

   ISession session = sf.OpenSession();
   lblStatus.Text = "OK";
}
catch (Exception ex){
   lblStatus.Text = ex.Message.ToString();
}

So how should I get the mapping in order to make this work??

Have you tried to simplify your primary keys to 1 column, having multiple columns might have complex mapping, if I were you I would try 1 column first and then add columns to the key to point out the problem — Yaman, Apr 13 '18 at 21:23

score 2 · Accepted Answer · answered Apr 13 '18 at 22:50

First you got to understand the error, you said:

Table Analysis serialNumber integer PK, FK (references serialNumber table Item)

This is wrong, serial number is a FK that references serialNumber, idProject and filename, because the three together forms table Item PK. That's why the error says "...must have same number of columns as the referenced primary key (Item [fileName, idProject, serialNumber])", the primary key of table Item is composed by three fields together and not only by "serialNumber" as you have suggested.

Take a look at this link, it explains how to configure a composite foreign key, this is what you need.

Comment bellow if you need any help.

I have solved it by adding the idProject to the table, but now when I try to populate the database I have an error that there is no way to fix it. I create a Project and add the Item to the list of items that the Project class has. When I execute the SaveOrUpdate(proy) it says that "Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [MockDB.Entities.Item#2]" but there is no other access nor thread and no change to the value, so why that appears? — a.ras2002, Apr 16 '18 at 10:06

Fluent Nhibernate error Foreign Key

1 Answers1