FreeSql学习笔记——4.联表

前言

  上一章节是查询，记录了简单的查询，比较看好的是分块、Dto映射和分页，除了简单的单表查询，更多的时候要用到联表查询，毕竟设计数据库是按照范式设计，FreeSql的联表操作有导航属性、Join联表和Sql联表等。

 

Init

  使用到联表，目前的Student表不够，新增一个Class表，同时新增Student表的ClassId字段和Class字段，数据库新增几条记录

查看代码

[Table(Name = "Student")]
public class Student
{
    [Column(IsIdentity = true, IsPrimary = true)]
    public long Id { get; set; }

    [Column(IsNullable = false, DbType = "varchar(20)")]
    public string Name { get; set; }

    public int Age { get; set; }

    public StatusEnum Status { get; set; }

    public DateTime AddTime { get; set; }

    [Column(IsNullable =true,DbType = "varchar(100)")]
    public string Remark { get; set; }

    public long ClassId { get; set; }

    public Class Class { get; set; }
}

 public enum StatusEnum
 {
     Normal = 1,

     Freeze = 2
 }
 
[Table(Name = "Class")]
public class Class
{
    [Column(IsIdentity = true, IsPrimary = true)]
    public long Id { get; set; }

    [Column(IsNullable = false, DbType = "varchar(20)")]
    public string Name { get; set; }

    public List<Student> StudentList { get; set; }
}

 

连表查询

  利用导航属性字段进行连表查询，写法和sql很相似

_freeSql.Select<Student>()
            .InnerJoin(x => x.Class.Id == x.ClassId)
            .Where(x => x.Id > 10)
            .ToList();
//SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId], a__Class.[Id] as9, a__Class.[Name] as10
//FROM[Student] a
//INNER JOIN[Class] a__Class ON a__Class.[Id] = a.[ClassId]
//WHERE(a.[Id] > 10)

  

  联表查询后也能指定返回的字段，与单表使用方式一样

_freeSql.Select<Student, Class>()
             .InnerJoin(x => x.t1.ClassId == x.t2.Id)
             .Where(x => x.t1.Id > 10)
             .ToList(x => new { Name=x.t1.Name, ClassName=x.t2.Name });
 //SELECT a.[Name] as1, b.[Name] as2
 //FROM[Student] a
 //INNER JOIN[Class] b ON a.[ClassId] = b.[Id]
 //WHERE(a.[Id] > 10)

 

自定义查询

  自定义Sql查询，使用Linq+Sql进行联表查询，可以做到使用字符串去拼接sql语句，包括表、条件、查询的字段，非常灵活

_freeSql.Select<Student>()
     .InnerJoin("Class b on b.Id = a.ClassId")
     .Where(x => x.Id > 10)
     .Where("b.Id>0")
     .ToList(x => new { Name = x.Name, ClassName = "b.Name" });
//SELECT a.[Name] as1, b.Name as2 
//FROM[Student] a
//INNER JOIN Class b on b.Id = a.ClassId
//WHERE(a.[Id] > 10) AND(b.Id > 0)

 

子查询

_freeSql.Select<Student>()
    .Where(a => _freeSql.Select<Class>().As("b").ToList(b => b.Id).Contains(a.ClassId))
    .ToList();
//SELECT value from fn_listextendedproperty('MS_Description', 'schema', N'dbo', 'table', N'Class', NULL, NULL)
//SELECT a.[Id], a.[Name], a.[Age], a.[Status], a.[AddTime], a.[Remark], a.[Version], a.[ClassId]
//FROM[Student] a
//WHERE(((a.[ClassId]) in (SELECT b.[Id]
//FROM[Class] b)))

 

  对于连表操作FreeSql支持非常强大，使用方式也有多种，选择自己喜欢的即可，

 

  传送门：http://freesql.net/guide/select-multi-table.html