Sql – Fluent NHibernate / NHibernate Mappings

fluent-nhibernatenhibernate-mappingsql

I have a parent table and relationship table to find child parent relationship.
I need to find child, parent and its siblings…

EMPLOYEE_RELATION has columns EMPLOYEE_ID and PARENT_ID that saves the relationship.

Here's what SQL looks like. How do I map this to Fluent NHibernate / NHibernate.

select 
   V1.PARENT_ID AS PARENT_ID, 
   V.EMPLOYEE_ID AS EMPLOYEE_ID,
   V2.EMPLOYEE_ID AS SIBLINGS_ID 
 FROM
    EMPLOYEE V
 INNER JOIN EMPLOYEE_RELATION  V1
   ON  V.EMPLOYEE_ID = V1.EMPLOYEE_ID
 INNER JOIN EMPLOYEE_RELATION V2
   ON V2.PARENT_ID = V1.PARENT_ID
 WHERE V.EMPLOYEE_ID = 6357

Best Answer

you should be able to create graph with using many-to-many approach with your cross table EMPLOYEE_RELATION. In other words after you've fetched some entity you should be able to fetch all children attached to it as simple list of the same objects as parent is. And you should be able to traverse it further, but I'm not completely sure.

Here is how it might look in your mapping file:

        HasManyToMany(x => x.Employees)
            .WithTableName("EMPLOYEE_RELATION")
            .WithParentKeyColumn("PARENT_ID")
            .WithChildKeyColumn("EMPLOYEE_ID")
            .Cascade.AllDeleteOrphan()
            .LazyLoad();

Related Solutions

C# – Cascade.SaveOrUpdate (SQLite) fluent-nhibernate

Maybe a little (or a lot) late, but nonetheless,

I simplified your example to look like this:

public class Tournament
{
    public virtual Guid Id { get; private set; }

    private readonly ISet<Player> players;

    public Tournament()
    {
        players = new HashedSet<Player>();
    }

    public virtual ISet<Player> Players
    {
        get { return players; }
    }

    public virtual void AddPlayer(Player player)
    {
        players.Add(player);
    }
}

public class Player 
{
    public virtual Guid Id { get; private set; }

    private readonly ISet<Tournament> tournaments;

    public Player()
    {
        tournaments = new HashedSet<Tournament>();
    }

    public virtual ISet<Tournament> Tournaments
    {
        get { return tournaments; }
    }

    public virtual void AddTournament(Tournament tournament)
    {
        tournaments.Add(tournament);
    }
}

public class TournamentMap : ClassMap<Tournament>
{
    public TournamentMap()
    {
        Id(x => x.Id).GeneratedBy.GuidComb();

        HasManyToMany<Player>(x => x.Players)
            .AsSet().Access.AsLowerCaseField()
            .Cascade.SaveUpdate();
    }
}
public class PlayerMap : ClassMap<Player>
{
    public PlayerMap()
    {
        Id(x => x.Id).GeneratedBy.GuidComb();

        HasManyToMany<Tournament>(x => x.Tournaments)
            .Access.AsLowerCaseField()
            .AsSet()
            .Cascade.SaveUpdate().Inverse();
    }
}

The first test looks like:

   [Test]
    public void CanSavePlayerAttachedToTournament()
    {
        Player player = new Player();
        Tournament tournament = new Tournament();
        player.AddTournament(tournament);
        tournament.AddPlayer(player);
        Session.Save(tournament);
        Session.Flush();
    }

and yields the following sql:

NHibernate: INSERT INTO "Tournament" (Id) VALUES (@p0); @p0 = '65559bba-8603-4874-8a8f-9bf4018596df' NHibernate: INSERT INTO "Player" (Id) VALUES (@p0); @p0 = '549e107c-1339-4fab-8960-9bf4018596e8' NHibernate: INSERT INTO PlayerToTournament (Tournament_id, Player_id) VALUES (@p0, @p1); @p0 = '65559bba-8603-4874-8a8f-9bf4018596df', @p1 = '549e107c-1339-4fab-8960-9bf4018596e8'

The second test looks like:

    [Test]
    public void CanSaveTounamentAttachedToPlayer()
    {
        Player player = new Player();
        Tournament tournament = new Tournament();
        player.AddTournament(tournament);
        tournament.AddPlayer(player);
        Session.Save(player);
        Session.Flush();
    }

and yields the following sql:

NHibernate: INSERT INTO "Player" (Id) VALUES (@p0); @p0 = '35c078c5-1102-4c63-91ca-9bf40185971c' NHibernate: INSERT INTO "Tournament" (Id) VALUES (@p0); @p0 = '367898cf-5835-4e1b-9d7d-9bf40185971c' NHibernate: INSERT INTO PlayerToTournament (Tournament_id, Player_id) VALUES (@p0, @p1); @p0 = '367898cf-5835-4e1b-9d7d-9bf40185971c', @p1 = '35c078c5-1102-4c63-91ca-9bf40185971c'

Fluent NHibernate Many to one mapping

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

Best Answer

Related Solutions

C# – Cascade.SaveOrUpdate (SQLite) fluent-nhibernate

Fluent NHibernate Many to one mapping

Related Topic