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??

Answer 1

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.