How to join three tables using FluentNhibernate

Question

I'm using FluentNhibernate for my C# application i would like to know how to join three tables which not having Foreign keys defined. Lets assume i have following table structure,

Student [StudentID, Name1, Name2, ClassID ]

Class [ClassID, Name, SchoolID]

School [SchoolID, SchoolName]

I need to join above three tables like this

SELECT a.Name1,a.Name2,b.Name,c.SchoolName FROM Student a, Class b, School c WHERE a.ClassID = b.ClassID AND b.SchoolID = c.SchoolID

I have following class structure for ORM

public class Student
{
        public virtual int      StudentID { get; set; }
        public virtual string   Name1 { get; set; }
        public virtual string   Name2 { get; set; }
        public virtual int      ClassID  { get; set; }
 }



 public class StudentMap : ClassMap<Student>
    {
        public StudentMap()
        {
            Id(x => x.StudentID ).Column("student_id");
            Map(x => x.Name1 ).Column("name_1");
            Map(x => x.Name1 ).Column("name_2");
            Map(x => x.ClassID).Column("class_Id");
            Table("student");
        }
    }





public class Classt
{
        public virtual int      ClassID { get; set; }
        public virtual string   Name { get; set; }
        public virtual int      SchoolID  { get; set; }
 }



 public class ClassMap : ClassMap<Class>
    {
        public ClassMap ()
        {
            Id(x => x.ClassID ).Column("class_id");
            Map(x => x.Name ).Column("name");
            Map(x => x.SchoolID).Column("school_Id");
            Table("class");
        }
    }




public class School
{
        public virtual int      SchooID { get; set; }
        public virtual string   Name { get; set; }
 }



 public class SchoolMap : ClassMap<School>
    {
        public SchoolMap ()
        {
            Id(x => x.ClassID ).Column("class_id");
            Map(x => x.Name ).Column("name");
            Table("school");
        }
    }

why you have no realation between your entities? whats the point of using ORM if won't use any relation between objects. — Mert, Jun 05 '15 at 14:55

score 0 · Answer 1 · answered Jun 05 '15 at 15:03

0

You should go something like this;

public class Student
{
        public virtual int      StudentID { get; set; }
        public virtual string   Name1 { get; set; }
        public virtual string   Name2 { get; set; }
        public virtual List<Class> ClassList  { get; set; }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(x => x.StudentID ).Column("student_id");
        Map(x => x.Name1 ).Column("name_1");
        Map(x => x.Name1 ).Column("name_2");
        HasMany(x => x.Class).KeyColumn("ClassId")
        Table("student");
    }
}

public class Class
{
        public virtual int      ClassID { get; set; }
        public virtual string   Name { get; set; }
        public virtual IList<Student> StudentList { get; set; }
        public virtual School School  { get; set; }
}

public class ClassMap : ClassMap<Class>
{
    public ClassMap ()
    {
        Id(x => x.ClassID ).Column("class_id");
        Map(x => x.Name ).Column("name");
        HasMany(x => x.Student).KeyColumn("StudentId")
        References(x => x.School)
        Table("class");
    }
}

public class School
{
    public virtual int      SchoolID { get; set; }
    public virtual string   Name { get; set; }
    public virtual IList<Class> ClassList { get; set; }
}

public class SchoolMap : ClassMap<School>
{
    public SchoolMap ()
    {
        Id(x => x.SchoolID ).Column("SchoolId");
        Map(x => x.Name ).Column("name");
        HasMany(x => x.ClassList).KeyColumn("ClassId")
        Table("school");
    }
}

answered Jun 05 '15 at 15:03

Mert

6,432
6
32
68

Thanks for the answer Mert, and how is the query looks like, ? – Praveen Jun 05 '15 at 22:50
what are you trying to achive, nhibernate works object oriented. what is your returning object? – Mert Jun 06 '15 at 02:10
I mean if i want to get specific student object, i get like this `code`var user = session.CreateCriteria("st").Add(Restrictions.Eq("st.StudentId", login)).UniqueResult();`code` Like that how can i execute below join in FluentNhibernate with your suggested file structure `code`SELECT a.Name1,a.Name2,b.Name,c.SchoolName FROM Student a, Class b, School c WHERE a.ClassID = b.ClassID AND b.SchoolID = c.SchoolID `code` – Praveen Jun 06 '15 at 08:39
Hi Mert, I finally came up with following solution but it always returns single object, i need to return object List please let me know if you know the answer [link]https://www.dropbox.com/s/u7asuz47siuqh28/NHibernate.cs?dl=0 – Praveen Jun 09 '15 at 07:03
I believe your join mapping causes this, remove it and try again It should get all students. – Mert Jun 09 '15 at 07:13
I used two "join"s to map three tables. which Join should i need to change? – Praveen Jun 09 '15 at 07:30
I am not expert but I don't think you need to use join there at all. Why don't you use joins in criteria? – Mert Jun 09 '15 at 07:49
Please let me know how to use joins in the criteria – Praveen Jun 09 '15 at 07:56
you can find an example here http://stackoverflow.com/questions/10967695/joining-on-multiple-fields-in-a-nhibernate-criteria-query – Mert Jun 09 '15 at 08:08

How to join three tables using FluentNhibernate

1 Answers1