Fluent Nhibernate join three tables

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 did the table mappings and execute the query as follows

public class Student
    {
        public virtual int StudentID     { get; set; }
        public virtual string Name1      { get; set; }
        public virtual string ClassID     { get; set; }
        public virtual string ClsName     { get; set; }
        public virtual string SchoolName  { 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.ClassID).Column("ClassId");
            Join("class", join =>
            {
               join.KeyColumn("class_id");
                Join("school", J =>
                {
                    J.Map(m => m.SchoolName, "school_Name");
                    J.KeyColumn("school_Id");
                });
            });
            Table("student");
        }
    }

//execute query

var studnt = session.CreateCriteria<Student>("st").List<Student>();

But, it always returns a single object (row), Does anyone knows how to get list of rows by joining above three tables ?

Answer 1

change the query you wrote as follows.

        string sqlCommand= @"SELECT 
            a.Id StudentId,
            a.Name1 StudentName,
            a.Name2 StudentName2,
            b.Name ClassName,
            c.SchoolName SchoolName 
                FROM Student a, Class b, School c 
                    WHERE a.ClassID = b.ClassID AND b.SchoolID = c.SchoolID";


public class StudentInfo
{
    int StudentId { get; set; }
    string StudentName { get; set; }
    string StudentName2 { get; set; }
    string ClassName { get; set; }
    string SchoolName { get; set; }
}

        List<StudentInfo> studentInfoList = new List<StudentInfo>();
        studentInfoList = session.CreateSQLQuery(sqlCommand)
            .AddScalar("StudentId", NHibernateUtil.Int32)
            .AddScalar("StudentName", NHibernateUtil.String)
            .AddScalar("StudentName2", NHibernateUtil.String)
            .AddScalar("ClassName", NHibernateUtil.String)
            .AddScalar("SchoolName", NHibernateUtil.String)
            .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<StudentInfo>())
            .List<StudentInfo>().ToList();