Fluent Nhibernate连接三个表

本文关键字：三个 Nhibernate 连接 Fluent | 更新日期: 2023-09-27 17:54:23

我正在使用FluentNhibernate为我的c#应用程序，我想知道如何连接没有外键定义的三个表。假设我有如下的表结构

Student [StudentID, Name1, Name2, ClassID]

类(ClassID、名称、SchoolID)

学校[SchoolID, SchoolName]

我需要像这样连接以上三个表

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

我做了表映射并执行如下查询

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

//执行查询
var studnt = session.CreateCriteria<Student>("st").List<Student>();

但是，它总是返回单个对象(行)，有人知道如何通过连接上面三个表来获得行列表吗?

Fluent Nhibernate连接三个表

将您编写的查询更改如下。

        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();
相关文章：