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()]"

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(); 
   } 
}

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();

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);
}