Fluent NHibernate Many to one mapping

Question

I am new to Hibernate world. It may be a silly question, but I am not able to solve it. I am testing many to One relationship of tables and trying to insert record. I have a Department table and Employee table. Employee and Dept has many to One relationship here. I am using Fluent NHibernate to add records. All codes below. Pls help

SQL Code

create table Dept (
    Id int primary key identity,
    DeptName varchar(20),
    DeptLocation varchar(20)
);

create table Employee (
    Id int primary key identity,
    EmpName varchar(20),
    EmpAge int,
    DeptId int references Dept(Id)
);

Class Files

public partial class Dept
{
    public virtual System.String DeptLocation { get; set; }
    public virtual System.String DeptName { get; set; }
    public virtual System.Int32 Id { get; private  set; }
    public virtual IList<Employee> Employees { get; set; }
}

public partial class Employee
{
    public virtual System.Int32 DeptId { get; set; }
    public virtual System.Int32 EmpAge { get; set; }
    public virtual System.String EmpName { get; set; }
    public virtual System.Int32 Id { get; private set; }
    public virtual Project.Model.Dept Dept { get; set; }
}

Mapping Files

public class DeptMapping : ClassMap<Dept>   
{
    public DeptMapping()
    {
        Id(x => x.Id);
        Map(x => x.DeptName);
        Map(x => x.DeptLocation);
        HasMany(x => x.Employees).Inverse().Cascade.All();
    }
}

public class EmployeeMapping : ClassMap<Employee>
{
    public EmployeeMapping()
    {
        Id(x => x.Id);
        Map(x => x.EmpName);
        Map(x => x.EmpAge);
        Map(x => x.DeptId);
        References(x => x.Dept).Cascade.None();
    }
}

My Code to add

        try
        {
            Dept dept = new Dept();
            dept.DeptLocation = "Austin";
            dept.DeptName = "Store";

            Employee emp = new Employee();
            emp.EmpName = "Ron";
            emp.EmpAge = 30;

            IList<Employee> empList = new List<Employee>();
            empList.Add(emp);
            dept.Employees = empList;
            emp.Dept = dept;

            IRepository<Dept> rDept = new Repository<Dept>();
            rDept.SaveOrUpdate(dept);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

Here i am getting error as

InnerException = {"Invalid column name 'Dept_id'."} Message = "could not insert: [Project.Model.Employee][SQL: INSERT INTO [Employee] (EmpName, EmpAge, DeptId, Dept_id) VALUES (?, ?, ?, ?); select SCOPE_IDENTITY()]"

Charlacharlady

Please check this link. Its very important mapping set up. thanigai.wordpress.com/2011/06/23/… – Cornea

Answer 1

Mattias' answer is almost right, but ForeignKey is used for schema generation. Try the below mapping instead. Also, you have the Employees collection mapped with CascadeAll. This will delete employee records if you delete a department, which is probably not desirable.

public class DeptMapping : ClassMap<Dept>
    { 
        public DeptMapping() 
        {
            Id(x => x.Id); 
            Map(x => x.DeptName); 
            Map(x => x.DeptLocation); 
            HasMany(x => x.Employees).KeyColumn("DeptId").Inverse().Cascade.All(); 
        } 
    }

public class EmployeeMapping : ClassMap<Employee>
{ 
    public EmployeeMapping() 
    { 
        Id(x => x.Id); 
        Map(x => x.EmpName); 
        Map(x => x.EmpAge); 
        Map(x => x.DeptId); 
        References(x => x.Dept, "DeptId").Cascade.None(); 
   } 
}

Phlebotomize

Jamie, Thanks for the correction, I am able to insert record to Dept table. But it is not inserting records to Employee table. Looks like my coding to call SaveOrUpdate part has some mistake.I am like almost there, but not finally. – Charlacharlady

The line dept.Employees = empList;, changes the collection reference. You need to add the employee to the collection through dept.Employees.Add(emp);. – Phlebotomize

I replaced dept.Employees= empList with dept.Employees.Add(emp) , it is giving Object reference not set instance of an object. This is correct as the EmployeeColleciton is null. The employee is a IList<Employee> collection, it needs to be set first before adding items to it.So it will not work. Second thing, how collection reference is changed with it ? – Charlacharlady

You should initialize the collection in Dept's constructor: Employees = new List<Employee>(). In response to the second question, NHibernate provides its own collection implementation for IList, so when you assigned deptEmployees = empList the reference was changed from NHibernate's list to yours. – Phlebotomize

Thanks for guiding me. I implemented as you said. created a constructor to Employees. Also added another method to Dept class as AddEmployees. I am sending the code part again. Still I am not able to add record to Employee table. It is adding perfectly to Dept table. – Charlacharlady

Now I am able to insert the record to employee. I need to add Employee after adding the Dept. It maintained the same foreign key values.I feel it is bit weird. The department should take the whole path to insert. I don't know if it s by design or need to change some property. The code is below. Pls give your suggesiton. IRepository<Dept> rDept = new Repository<Dept>(); rDept.SaveOrUpdate(dept); IRepository<Employee> rEmp = new Repository<Employee>(); rEmp.SaveOrUpdate(emp ); – Charlacharlady

Boss, I got the answer. I have made one mistake by making cascade.None() for dept.I changed it to Casecade.SaveUpdate(). It worked fine foe me. Thanks a lot for guiding me. – Charlacharlady

No problem, I'm glad you figured it out. – Phlebotomize

Oh god! Why is ManyToOne suddenly called "References"? I want to slap Fluent Nhibernate authors for wasting hours of my time. – Bourque

Why is Inverse() still needed? this just seems like internal implementation detail for the hardcore. Should this really be necessary? – Trailer

Inverse is needed, here's a good explanation: #714137 – Phlebotomize

Answer 2

The problem is (as the error message tells you) that you don't have a column named "Dept_id". Your column name is instead "DeptId". The default conventions of fluent nhibernate is that the name should be with the underscore. To solve this you can ether change the name of the column in your database or you can override the convention in your mapping file to tell it to use your column name instead. That can be done in this way:

//EmployeeMapping
References(x => x.Dept).ForeignKey("DeptId").Cascade.None();

//DeptMapping
HasMany(x => x.Employees).KeyColumn("DeptId").Inverse() .Cascade.All();

Kheda

I Made the changes as you mentioned. I am still getting the same error. public DeptMapping() { Id(x => x.Id); Map(x => x.DeptName); Map(x => x.DeptLocation); HasMany(x => x.Employees) .KeyColumn("DeptId") .Inverse() .Cascade.All(); } public EmployeeMapping() { Id(x => x.Id); Map(x => x.EmpName); Map(x => x.EmpAge); Map(x => x.DeptId); References(x => x.Dept) .ForeignKey("DeptId") .Cascade.None(); } – Charlacharlady

Answer 3

[Serializable] 
public partial class Dept   
{       
    public virtual System.String DeptLocation { get; set; }

    public virtual System.String DeptName { get; set; }

    public virtual System.Int32 Id { get; private  set; }   

    //public virtual Iesi.Collections.Generic.ISet<Employee> Employees { get; set; }

    public virtual IList<Employee> Employees { get; set; }

    public Dept()
    {
        Employees = new List<Employee>();      
    }

    public virtual void AddEmployees(Employee employee)
    {
        employee.Dept = this;
        Employees.Add(employee);       
    } 
}

Employee Class

public partial class Employee
{
    //public virtual System.Int32 DeptId { get; set; }
    public virtual System.Int32 EmpAge { get; set; }
    public virtual System.String EmpName { get; set; }
    public virtual System.Int32 Id { get; private set; }
    public virtual Project.Model.Dept  Dept { get; set; }
}

The Code to call the method

try
{
    Dept dept = new Dept();
    dept.DeptLocation = "Austin";
    dept.DeptName = "Store";

    Employee emp = new Employee();
    emp.EmpName = "Ron";
    emp.EmpAge = 30;


    dept.AddEmployees(emp); 


    IRepository<Dept> rDept = new Repository<Dept>();
    rDept.Add(dept);
}

Charlacharlady