一、基础查询
//查询集合-//select * from StudentInfo
List<StudentInfo> list = db.Queryable<StudentInfo>().ToList();
//查询总数
int count = db.Queryable<StudentInfo>().Count();
//select count(1) from StudentInfo
List<StudentInfo> studentList;
//按条件查询
{
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Id == 1).ToList();
//select * from StudentInfo where id=1
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Name != null).ToList();
//不是null
//select * from StudentInfo where name is not null
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Name == null).ToList();
//是null
//select * from StudentInfo where name is null
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Name != "名称_11").ToList();
//不是空 ,不为空
//select * from StudentInfo where name <> ''
}
//多条件查询
{
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Id > 10 && it.Name == "名称_11").ToList();
//select * from StudentInfo where id>10 and name='a'
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Id > 10)
.Where(it => it.Name == "名称_11").ToList();
}
//动态OR查询
{
Expressionable<StudentInfo> exp = Expressionable.Create<StudentInfo>();
string name = "名称_11";
//.OrIf 是条件成立才会拼接OR
exp.OrIF(!string.IsNullOrWhiteSpace(name), it => it.Name.Contains(name));
//拼接OR
exp.Or(it => it.Name.Contains("名称_"));
studentList = db.Queryable<StudentInfo>()
.Where(exp.ToExpression()).ToList();
}
//模糊查询
{
studentList = db.Queryable<StudentInfo>()
.Where(it => it.Name.Contains("名称_")).ToList();
//select * from StudentInfo where name like %jack%
}
//根据主键查询
{
/*单主键查询*/
//通过主键查询 SingleById
db.Queryable<StudentInfo>().InSingle(2);
//根据ID查询，可以不是主键
//select * from StudentInfo where id=2
db.Queryable<StudentInfo>().Single(it => it.Id == 2);
/*多主键查询*/
var getAll = db.Queryable<StudentInfo>()
.WhereClassByPrimaryKey(new StudentInfo() { Id = 123 })
.ToList(); //单个实体
getAll = db.Queryable<StudentInfo>()
.WhereClassByPrimaryKey(new List<StudentInfo>()
{
new StudentInfo() { Id = 123 },
new StudentInfo() { Id = 234 },
new StudentInfo() { Id = 345 }
}).ToList(); //支持集合
}
//查询第一条 ，第一行
{
//First() 等同于C#中的 FirstOrDefault , 没有值返回 null
//没有返回Null
StudentInfo student = db.Queryable<StudentInfo>()
.First(it => it.Id == 1);
}
{
//没有返回Null
//select top 1 * from StudentInfo order by id desc where id=1
StudentInfo student = db.Queryable<StudentInfo>()
.OrderBy(it => it.Id, OrderByType.Desc)
.First(it => it.Id == 1);
}
//查前几条
{
List<StudentInfo> studetntlist = db.Queryable<StudentInfo>()
.Take(10)
.ToList();
}
//数据行数
{
int count1 = db.Queryable<StudentInfo>()
.Where(it => it.Id > 11).Count();//同步
Task<int> countTask = db.Queryable<StudentInfo>()
.Where(it => it.Id > 11).CountAsync();
//异步
//select count(*) from StudentInfo where id>11
//你也可以用函数
//SqlFunc.AggregateCount
}
//是否存在记录
{
db.Close();
db.Open();
bool isExists = db.Queryable<StudentInfo>()
.Where(it => it.Id > 11)
.Any();
isExists = db.Queryable<StudentInfo>()
.Any(it => it.Id > 11); //上面语法的简化
}
//In查询,IN的使用
{
int[] allIds = new int[] { 2, 3, 31 };
List<StudentInfo> list1 = db.Queryable<StudentInfo>().Where(it => allIds.Contains(it.Id)).ToList();
//字符串类型 varchar和nvarchar (默认varchar来保证性能)
//NameList.Contains(it.Name, true) //true和false来控制是varchar还是nvarchar
}
//多个字段 条件拼接
{
List<StudentInfo> OrderList = new List<StudentInfo>()
{
new StudentInfo{ Id = 1,Name="名称_1"},
new StudentInfo{ Id = 2,Name="名称_2"},
new StudentInfo{ Id = 3,Name="名称_3"}
};
Expressionable<StudentInfo> exp = new Expressionable<StudentInfo>();
foreach (var item in OrderList)
{
exp.Or(it => it.Id == item.Id && it.Name == item.Name);
}
//使用构造好的表达式
var studentlist = db.Queryable<StudentInfo>().Where(exp.ToExpression()).ToList();
}
//使用 in 的模糊查询
{
var names = new string[] { "名称_", "名称_11" };
Expressionable<StudentInfo> exp = new Expressionable<StudentInfo>();
foreach (var item in names)
{
exp.Or(it => it.Name.Contains(item));
}
var studentlist = db.Queryable<StudentInfo>()
.Where(exp.ToExpression()).ToList();
}
//NOT IN
{
int[] allIds = new int[] { 2, 3, 31 };
var studentlist = db.Queryable<StudentInfo>()
.Where(it => !allIds.Contains(it.Id))
.ToList();
}
//简单排序
{
var studentlist = db.Queryable<StudentInfo>()
.OrderBy(st => st.Id, OrderByType.Desc)
.ToList();
}
//查询一列
{
//单值 查询列 查询单独列
var studentlist = db.Queryable<StudentInfo>()
.Select(it => it.Name)
.ToList();
}
//查询单条
{
StudentInfo student = db.Queryable<StudentInfo>().Single(it => it.Id == 1);
//没有返回Null，如果结果大于1条会抛出错误
//select * from StudentInfo where id=1 // 查询id等于1的单条记录
}
//获取最大值,最小值
{
int maxNum = db.Queryable<StudentInfo>()
.Max(it => it.Id);//同步
Task<int> maxTask = db.Queryable<StudentInfo>()
.MaxAsync(it => it.Id);//异步
db.Close();
db.Open();
int minNum = db.Queryable<StudentInfo>()
.Min(it => it.Id);//同步
Task<int> minTask = db.Queryable<StudentInfo>()
.MinAsync(it => it.Id);//异步
}
//求和
{
db.Close();
db.Open();
int sumNum = db.Queryable<StudentInfo>()
.Sum(it => it.Id);//同步
Task<int> sumTask = db.Queryable<StudentInfo>()
.SumAsync(it => it.Id);//异步
}
//查询过滤排除某一个字段
{
/***单表***/
db.Close();
db.Open();
db.Queryable<StudentInfo>().ToList();
db.Queryable<StudentInfo>().IgnoreColumns(it => it.Name).ToList();//只支持单表查询
}
二、连接查询
//左连接
{
var query1 = db.Queryable<Company>()
.LeftJoin<User>((c, u) => c.Id == u.CompanyId)
.LeftJoin<UserScore>((c, u, us) => u.Id == us.UserId)
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
})
.ToList();
var query2 = db.Queryable<Company, User, UserScore>((c, u, us) =>
new JoinQueryInfos(JoinType.Left, c.Id == u.CompanyId, JoinType.Left, u.Id == us.UserId
))
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
}).ToList();
}
//右链接
{
var query1 = db.Queryable<Company>()
.RightJoin<User>((c, u) => c.Id == u.CompanyId)
.RightJoin<UserScore>((c, u, us) => u.Id == us.UserId)
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
})
.ToList();
var query2 = db.Queryable<Company, User, UserScore>((c, u, us) =>
new JoinQueryInfos(JoinType.Right, c.Id == u.CompanyId, JoinType.Right, u.Id == us.UserId
))
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
}).ToList();
}
//内连接
{
var query1 = db.Queryable<Company>()
.InnerJoin<User>((c, u) => c.Id == u.CompanyId)
.InnerJoin<UserScore>((c, u, us) => u.Id == us.UserId)
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
})
.ToList();
var query2 = db.Queryable<Company, User, UserScore>((c, u, us) =>
new JoinQueryInfos(JoinType.Inner, c.Id == u.CompanyId, JoinType.Inner, u.Id == us.UserId
))
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
}).ToList();
}
//全连接
{
var query1 = db.Queryable<Company>()
.FullJoin<User>((c, u) => c.Id == u.CompanyId)
.FullJoin<UserScore>((c, u, us) => u.Id == us.UserId)
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
})
.ToList();
var query2 = db.Queryable<Company, User, UserScore>((c, u, us) =>
new JoinQueryInfos(JoinType.Full, c.Id == u.CompanyId, JoinType.Full, u.Id == us.UserId
))
.Where(c => c.Id == 1)
.Select((c, u, us) => new
{
CompanyId = c.Id,
CompanyName = c.CompanyName,
UserNae = u.UserName,
Subject = us.Subject,
ScoreCoutn = us.Achievement
}).ToList();
}
//并集
{
var q1 = db.Queryable<Student>()
.Select(it => new User { UserName = it.Name });
var q2 = db.Queryable<User>()
.Select(it => new User { UserName = it.UserName });
var list = db.UnionAll(q1, q2).ToList();
}
三、复杂查询
//分组聚合
{
var list = db.Queryable<Student>()
.GroupBy(it => new { it.Id, it.Name })
.Having(it => SqlFunc.AggregateAvg(it.Id) > 0)
.Select(it => new
{
idAvg = SqlFunc.AggregateAvg(it.Id),
count = SqlFunc.AggregateCount(it.Id),
name = it.Name
})
.ToList();
}
//一般用来指定字段去重复,查询不重复的值,去重字段
{
var list = db.Queryable<Student>()
.Distinct()
.Select(it => new { it.Name }).ToList();
}
//开窗函数
{
var model = db.Queryable<Student>()
.Take(1)
.PartitionBy(it => it.Name)
.ToList();
var model1 = db.Queryable<Student>()
.OrderBy(it => it.Id, OrderByType.Desc)
.Take(1)
.PartitionBy(it => it.Name)
.ToList();
}
//合并结合
{
var test48 = db.Queryable<Student>().Select(it => new
{
index2 = SqlFunc.RowNumber(it.Id, it.Name),
name = it.Name,
date = it.CreateTime
})
.MergeTable()//将结果合并成一个表
.Where(it => it.index2 == 1)
.ToList();
}
//分页查询
{
{
int pagenumber = 2; // pagenumber是从1开始的不是从零开始的
int pageSize = 2;
int totalCount = 0;
//单表分页
var page = db.Queryable<Student>()
.ToPageList(pagenumber, pageSize, ref totalCount);
var page1 = db.Queryable<Student>()
.ToOffsetPage(pagenumber, pageSize, ref totalCount);
}
//如果SqlServer不想用Rownumber可以用 ToOffsetPage 较新版本支持
//多表分页
{
int pagenumber = 1; // pagenumber是从1开始的不是从零开始的
int pageSize = 20;
int totalCount = 0;
var list = db.Queryable<Company>()
.LeftJoin<User>((c, u) => c.Id == u.CompanyId)
.Select((c, u) => new
{
Id = c.Id,
cName = c.CompanyName,
uId = u.Id,
uName = u.UserName
}).ToOffsetPage(pagenumber, pageSize, ref totalCount);
}
//异步分页
{
int pagenumber = 1; // pagenumber是从1开始的不是从零开始的
int pageSize = 20;
//int totalCount = 0;
RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
Task<List<Student>> studentTask =
db.Queryable<Student>()
.ToPageListAsync(pagenumber, pageSize, total);//ToPageAsync
List<Student> list = studentTask.Result;
}
}
//Order排序
{
var list = db.Queryable<Company, User>((c, u) => new JoinQueryInfos(JoinType.Left, c.Id == u.CompanyId))
.OrderBy(c => c.Id)//升序
.OrderBy((c, u) => c.CompanyName, OrderByType.Desc)//倒序
.Select((c, u) => new
{
cId = c.Id,
cName = c.CompanyName,
uId = u.Id,
uName = u.UserName
}).ToList();
}
//多个Order一起
{
var list = db.Queryable<User>()
.OrderBy(it => new
{
it.Id,
name = SqlFunc.Desc(it.UserName)
}).ToList();
}
//动态排序
{
//通过类中属性名获取数据库字段名
{
var orderByFieldName = db.EntityMaintenance.GetDbColumnName<User>("Id");//防注入
var list = db.Queryable<Student>()
.OrderBy(orderByFieldName + " asc ")
.ToList(); ;
}
{
var list = db.Queryable<Company, User>((c, u) => new JoinQueryInfos(JoinType.Left, c.Id == u.CompanyId))
.OrderBy("c.id asc,u.Id desc ") //多表查询有别名(c&u)
.Select((c, u) => new
{
cId = c.Id,
cName = c.CompanyName,
uId = u.Id,
uName = u.UserName
}).ToList();
}
{
var pageJoin = db.Queryable<Company, User>((c, u) => new JoinQueryInfos(JoinType.Left, c.Id == u.CompanyId))
.Select((c, u) => new
{
cId = c.Id,
cName = c.CompanyName,
uId = u.Id,
uName = u.UserName
})
.MergeTable()//将查询结果集变成表MergeTable
.Where(it => it.cId == 1)
.OrderBy("cName asc").ToList();//对表MergeTable进行排序
}
//随机排序取10条
{
db.Queryable<Student>().Take(10).OrderBy(st => SqlFunc.GetRandom()).ToList();
}
}
四、导航查询
1、一对一
//用户和用户信息一对一关系
[SugarTable("User")]
public class User
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//数据库是自增才配自增
public int Id { get; set; }
[SugarColumn(ColumnName = "CompanyId", IsNullable = true)]
public int? CompanyId { get; set; }//数据库有这个字段
[SugarColumn(ColumnName = "UserName", IsNullable = true)]//数据库与实体不一样设置列名
public string? UserName { get; set; }
[SugarColumn(ColumnName = "CreateTime", IsNullable = true)]
public DateTime? CreateTime { get; set; }
public int UserDetailId { get; set; }//数据库有这个字段
[Navigate(NavigateType.OneToOne, nameof(UserDetailId))]
public UserDetail? UserDetailInfo { get; set; } //不能赋值只能是null
}
[SugarTable("UserDetail")]
public class UserDetail
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string? Address { get; set; }
public string? Description { get; set; }
}
#region 一对一关系
{
List<User> users = new List<User>()
{
new User()
{
CompanyId = 1,
UserName = "Richard",
CreateTime = DateTime.Now,
UserDetailInfo = new UserDetail()
{
Address = "湖北武汉",
Description = ".NET金牌讲师"
}
},
new User()
{
CompanyId = 1,
UserName = "cole老师号",
CreateTime = DateTime.Now,
UserDetailInfo = new UserDetail()
{
Address = "湖北黄冈",
Description = "金牌助教"
}
}
};
//导航属性新增
bool bResult = db.InsertNav(users)
.Include(z1 => z1.UserDetailInfo)
.ExecuteCommand();
//导航属性查询
var list1 = db.Queryable<User>()
.Includes(t => t.UserDetailInfo) //多层级
.ToList();
var list2 = db.Queryable<User>()
.Includes(t => t.UserDetailInfo) //多层级
.Where(c => c.UserName == "Richard")
.ToList();
list2[0].UserName = "Richard老师";
list2[0].UserDetailInfo.Address = "湖北武汉汉阳";
bool bResult1 = db.UpdateNav(list2)
.Include(u => u.UserDetailInfo)
.ExecuteCommand();
bool bResult2 = db.DeleteNav<User>(list2)
.Include(c => c.UserDetailInfo)
.ExecuteCommand();
}
#endregion
2、一对多
//company和user一对多，用户表同上
[SugarTable("Company")]
public class Company
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string? CompanyName { get; set; }
public DateTime? CreateTime { get; set; }
[Navigate(NavigateType.OneToMany, nameof(User.CompanyId))]
public List<User> UserList { get; set; }
}
#region 一对多关系
{
List<Company> companies = new List<Company>()
{
new Company()
{
CompanyName="朝夕教育",
CreateTime= DateTime.Now,
UserList=new List<User>(){
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Eleven"
},
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Richard",
UserDetailInfo=new UserDetail
{
Address="湖北武汉汉阳",
Description="金牌讲师"
}
},
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Gerry"
}
}
},
new Company()
{
CompanyName="腾讯课堂",
CreateTime= DateTime.Now,
}
};
//导航新增
db.InsertNav(companies)
.Include(c => c.UserList)
.ExecuteCommand();
//一对多导航查询
var list = db.Queryable<Company>()
.Includes(t => t.UserList)
.ToList();
//多对一导航查询，需要在user实体类的CompanyId下加上
//[Navigate(NavigateType.ManyToOne, nameof(CompanyId))]
//public Company? CompanyInfo { get; set; }
var userlist = db.Queryable<User>()
.Includes(u => u.CompanyInfo)
.ToList();
var list1 = db.Queryable<Company>()
.Includes(x => x.UserList.Where(u => u.UserName == "Richard").ToList())
.Where(x => x.CompanyName.Contains("朝夕教育"))
.ToList();
list1[0].CompanyName = "朝夕教育-金牌机构";
list1[0].UserList[0].UserName = "Richard老师";
//修改
bool bResult2 = db.UpdateNav(list1)
.Include(u => u.UserList)
.ExecuteCommand();
///删除
bool bResult3 = db.DeleteNav(list1)
.Include(u => u.UserList)
.ExecuteCommand();
}
#endregion
3、多对多
//用户和角色多对多关系
[SugarTable("User")]
public class User
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//数据库是自增才配自增
public int Id { get; set; }
[SugarColumn(ColumnName = "CompanyId", IsNullable = true)]
public int? CompanyId { get; set; }//数据库有这个字段
[SugarColumn(ColumnName = "UserName", IsNullable = true)]//数据库与实体不一样设置列名
public string? UserName { get; set; }
[SugarColumn(ColumnName = "CreateTime", IsNullable = true)]
public DateTime? CreateTime { get; set; }
public int UserDetailId { get; set; }//数据库有这个字段
[Navigate(NavigateType.OneToOne, nameof(UserDetailId))]
public UserDetail? UserDetailInfo { get; set; } //不能赋值只能是null
[Navigate(typeof(UserRoleMapping), nameof(UserRoleMapping.UserId), nameof(UserRoleMapping.RoleId))]//注意顺序
public List<Role> RoleList { get; set; } //不能赋值只能是null
}
[SugarTable("Role")]
public class Role
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string? RoleName { get; set; }
public DateTime? CreateTime { get; set; }
[Navigate(typeof(UserRoleMapping), nameof(UserRoleMapping.RoleId), nameof(UserRoleMapping.UserId))]//注意顺序
public List<User> UserList { get; set; }
}
//需要多配置个中间表
public class UserRoleMapping
{
[SugarColumn(IsPrimaryKey = true)]//可以不是主键
public int UserId { get; set; }
[SugarColumn(IsPrimaryKey = true)]//可以不是主键
public int RoleId { get; set; }
}
#region 多对多关系
{
List<User> users = new List<User>()
{
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Richard老师",
RoleList=new List<Role>()
{
new Role()
{
CreateTime= DateTime.Now,
RoleName="管理员"
},
new Role()
{
CreateTime= DateTime.Now,
RoleName="金牌讲师"
}
}
}
};
db.InsertNav(users)
.Include(u => u.RoleList)// 插入第一层
.ExecuteCommand();
var list1 = db.Queryable<User>()
.Includes(x => x.RoleList)
.ToList();
var list2 = db.Queryable<User>()
.Includes(x => x.RoleList.Where(r => r.RoleName == "管理员").ToList())
.Where(c => c.UserName.Equals("Richard老师"))
.ToList();
bool bResult = db.UpdateNav(list2)
.Include(u => u.RoleList)
.ExecuteCommand();
bool bResult1 = db.DeleteNav(list1)
.Include(u => u.RoleList)
.ExecuteCommand();
int iResult1 = db.Deleteable(list1).ExecuteCommand();
var roles = db.Queryable<Role>()
.ToList();
int iResult2 = db.Deleteable(roles).ExecuteCommand();
}
#endregion
4、动态导航
在程序中不用配置关联字段，在查询的过程中，把需要关联的字段，手动匹配完成导航查询
public class StudentA
{
[SugarColumn(IsPrimaryKey = true)]
public int StudentId { get; set; }
public string? Name { get; set; }
public int SchoolId { get; set; }
[SugarColumn(IsIgnore = true)]
public SchoolA SchoolA { get; set; }
[Navigate(NavigateType.Dynamic, null)] //自定义关系映射
public List<BookA> Books { get; set; } //只能是null 不能赋默认值
}
public class BookA
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int BookId { get; set; }
public string? Name { get; set; }
public int studenId { get; set; }
}
#region 手动、指定字段、多字段
{
var list = db.Queryable<StudentA>()
.Includes(it => it.Books
.MappingField(z => z.studenId, () => it.StudentId)
.Where(z => z.BookId == 1)
.ToList()
)
.ToList();
}
#endregion
5、导航查询性能优化
如果数据量偏大，且查询的数据量大，则可以分段查询，每次查询多少条，然后继续往后查询多少条
#region 性能优化，
{
if (db.DbMaintenance.IsAnyTable("User", false))
{
db.DbMaintenance.DropTable<User>();
}
if (db.DbMaintenance.IsAnyTable("Company", false))
{
db.DbMaintenance.DropTable<Company>();
}
db.CodeFirst.InitTables<Company>();
db.CodeFirst.InitTables<User>();
//导航删除
db.DeleteNav<Company>(c => c.Id > 0)
.Include(c => c.UserList)
.ExecuteCommand();
List<Company> companies = new List<Company>();
for (int i = 0; i < 5000; i++)
{
Company company = new Company()
{
CompanyName = $"朝夕教育_{i}",
CreateTime = DateTime.Now
};
List<User> userlist = new List<User>();
for (int j = 0; j < 10; j++)
{
User user = new User()
{
CompanyId = 1,
CreateTime = DateTime.Now,
UserName = $"学员_{j}"
};
userlist.Add(user);
}
company.UserList = userlist;
companies.Add(company);
}
db.InsertNav<Company>(companies)
.Include(c => c.UserList)
.ExecuteCommand();
Console.WriteLine("====================================================================");
Console.WriteLine("====================================================================");
Console.WriteLine("====================================================================");
Console.WriteLine("====================================================================");
Console.WriteLine("====================================================================");
List<Company> resultList = new List<Company>();
db.Queryable<Company>()
.Includes(it => it.UserList)
.ForEach(it => resultList.Add(it), 300); //每次查询300条
}
#endregion
6、查询映射
数据库返回的是一个实体类User，但返回给客户端的是UserDto（比如不想把字段全部返回，只想返回部分属性，所以建了的UserDto），一般都会用AutoMapper，但sqlsugar有一个工具类更简单，nuget包 Mapster
#region 数据映射
{
List<Company> companies = db.Queryable<Company>()
.Includes(it => it.UserList)
.ToList(); //每次查询300条
List<CompanyDto> dtoList = companies.Adapt<List<CompanyDto>>();
}
#endregion
7、导航方法
#region 导航方法
{
List<Company> companies = new List<Company>()
{
new Company()
{
CompanyName="朝夕教育",
CreateTime= DateTime.Now,
UserList=new List<User>(){
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Eleven"
},
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Richard",
UserDetailInfo=new UserDetail
{
Address="湖北武汉汉阳",
Description="金牌讲师"
}
},
new User()
{
CompanyId=1,
CreateTime= DateTime.Now,
UserName="Gerry"
}
}
},
new Company()
{
CompanyName="腾讯课堂",
CreateTime= DateTime.Now,
}
};
//导航新增
db.InsertNav(companies)
.Include(c => c.UserList)
.ThenInclude(u => u.UserDetailInfo)
.ExecuteCommand();
Console.WriteLine("================================");
List<Company> companyList1 = db.Queryable<Company>()
//.Include(c=>c.UserList)
//.Where(c=>c.UserList.Count()>0)
.Where(it => it.UserList.Any()) //这行相当于上面两行
.ToList();
List<Company> companyList2 = db.Queryable<Company>()
.Where(it => it.UserList.Any(c => c.UserName.Equals("Richard")))
.ToList();
if (db.DbMaintenance.IsAnyTable("User", false))
{
db.DbMaintenance.DropTable<User>();
}
if (db.DbMaintenance.IsAnyTable("UserDetail", false))
{
db.DbMaintenance.DropTable<UserDetail>();
}
db.CodeFirst.InitTables<User>();
db.CodeFirst.InitTables<UserDetail>();
db.DeleteNav<User>(c => c.Id > 0)
.Include(c => c.UserDetailInfo)
.ExecuteCommand();
List<User> users = new List<User>()
{
new User()
{
CompanyId = 1,
UserName = "Richard",
CreateTime = DateTime.Now,
UserDetailInfo = new UserDetail()
{
Address = "湖北武汉",
Description = ".NET金牌讲师"
}
},
new User()
{
CompanyId = 1,
UserName = "cole老师号",
CreateTime = DateTime.Now,
UserDetailInfo = new UserDetail()
{
Address = "湖北黄冈",
Description = "金牌助教"
}
}
};
db.InsertNav(users).Include(c => c.CompanyInfo).ExecuteCommand();
List<User> userlist = db.Queryable<User>()
.Where(x => SqlFunc.Exists(x.UserDetailInfo.Id))
.ToList();
}
#endregion
8、ThenMapper
当没有配置导航属性也就是navigate，不能使用Includes方法，可以使用ThenMapper查询
#region 联表导航
{
Console.WriteLine("=================================================");
var list = db.Queryable<StudentA>().ToList();//这儿也可以联表查询
//var list1 = db.Queryable<StudentA>().Includes(c=>c.SchoolA).ToList();//报错，没有配置导航属性Navigate
db.ThenMapper(list, stu =>
{
//如果加Where不能带有stu参数，stu参数写到 SetContext
stu.SchoolA = db.Queryable<SchoolA>().SetContext(scl => scl.SchoolId, () => stu.SchoolId, stu).FirstOrDefault();
});
}
#endregion
9、树级查询
9.1、设置导航属性
public class Tree
{
[SugarColumn(IsPrimaryKey = true)]
public int Id { get; set; }
public string? Name { get; set; }
public int ParentId { get; set; } //在树查询中Parentid 是特殊的；
[Navigate(NavigateType.OneToMany, nameof(ParentId))]//设置导航 一对多
public List<Tree>? Child { get; set; }
}
#region 实现无限层--树
{
if (db.DbMaintenance.IsAnyTable("Tree", false))
{
db.DbMaintenance.DropTable<Tree>();
}
db.CodeFirst.InitTables<Tree>();
Tree tree = new Tree()
{
Id = 1,
Name = "一级树",
ParentId = 0,
Child = new List<Tree>
{
new Tree()
{
ParentId=1,
Id = 5,
Name="二级目录-1",
Child=new List<Tree>
{
new Tree() {
Id = 6,
Name="三级目录-1",
ParentId=2,
Child=new List<Tree>
{
new Tree() {
Id = 7,
ParentId=6,
Name="四级目录-1"
}
}
}
}
}
}
};
db.InsertNav<Tree>(tree)
.Include(it => it.Child)
.ThenInclude(c => c.Child)
.ThenInclude(c1 => c1.Child)
.ThenInclude(c2 => c2.Child)
.ExecuteCommand();
var treeRoot = db.Queryable<Tree>().Where(it => it.Id == 1).ToList();
//第一层
db.ThenMapper(treeRoot, item =>
{
item.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => item.Id, item).ToList();
});
//第二层
db.ThenMapper(treeRoot.SelectMany(it => it.Child), it =>
{
it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//第三层
db.ThenMapper(treeRoot.SelectMany(it => it.Child).SelectMany(it => it.Child), it =>
{
it.Child = db.Queryable<Tree>().SetContext(x => x.ParentId, () => it.Id, it).ToList();
});
//上面的可以用这个一步完成
List<Tree> list = db.Queryable<Tree>().ToTree(it => it.Child, it => it.ParentId, 0);
}
//查询所有下级
//从ParentId 为0 开始查询下级
List<Tree> childlist = db.Queryable<Tree>().ToTree(it => it.Child, it => it.ParentId, 0);
//查询所有上级
List<Tree> parentlist = db.Queryable<Tree>().ToParentList(it => it.ParentId, 6);
//查询所有下级
List<Tree> parentlist = db.Queryable<Tree>().ToChildList(it => it.ParentId, 6);
//条件查询，树形查询
List<Tree> treelist = db.Queryable<Tree>().Where(it => it.ParentId > 5)
.ToTree(it => it.Child, it => it.ParentId, 0);
#endregion
10、动态表达式
//表达式目录树连写
{
Console.WriteLine("========================================================================");
string CompanyName = "";
int? id = 1;
//用例1:连写 不等于空 后面
Expression<Func<Company, bool>> exp = Expressionable.Create<Company>() //创建表达式
.AndIF(string.IsNullOrWhiteSpace(CompanyName) == false, it => it.CompanyName == CompanyName)
.AndIF(id != null, it => it.Id == id)
.ToExpression();//注意 这一句 不能少
var list = db.Queryable<Company>()
.Where(exp)
.ToList();//直接用就行了不需要判段 null和加true
}
//表达式目录树分开写
{
Console.WriteLine("========================================================================");
string CompanyName = "音娱乐行";
int? id = 1;
Expressionable<Company> expable = Expressionable.Create<Company>();
if (string.IsNullOrWhiteSpace(CompanyName) == false)
{
expable.And(it => it.CompanyName == CompanyName);
}
if (id != null)
{
expable.And(it => it.CompanyName == CompanyName);
}
Expression<Func<Company, bool>> exp = expable.ToExpression();//要用变量 var exp=
db.Queryable<Company>().Where(exp).ToList();//直接用就行了不需要判段 null和加true
}
//拓展用例
{
Console.WriteLine("========================================================================");
var names = new string[] { "音娱乐行", "乐善其行" };
Expressionable<Company> exp = Expressionable.Create<Company>();
foreach (var item in names)
{
exp.Or(it => it.CompanyName.Contains(item.ToString()));
}
var list = db.Queryable<Company>().Where(exp.ToExpression()).ToList();
}
// Queyable.Or
{
Console.WriteLine("========================================================================");
var exp = Expressionable.Create<Company>()
.And(it => it.Id == 1)
.Or(it => it.Id == 100)
.ToExpression();//注意 这一句 不能少
var list = db.Queryable<Company>().Where(exp).ToList();
}
11、跨库查询
[Tenant("db2")] //实体标为db2
public class OrderItem
{
[SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int ItemId { get; set; }
public int OrderId { get; set; }
public decimal? Price { get; set; }
[SqlSugar.SugarColumn(IsNullable = true)]
public DateTime? CreateTime { get; set; }
[Navigate(NavigateType.OneToOne,nameof(OrderId))] //设置关系 对应Order表主键
public Order Order { get; set; }
}
[Tenant("db1")] //实体标为db1
public class Order
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
[SugarColumn(IsNullable = true)]
public DateTime CreateTime { get; set; }
[SugarColumn(IsNullable = true)]
public int CustomId { get; set; }
[Navigate(NavigateType.OneToMany, nameof(OrderItem.OrderId))]//
public List<OrderItem> Items { get; set; }
}
//通过ConfigId进行区分是哪个库
var db = new SqlSugarClient(new List<ConnectionConfig>()
{
new ConnectionConfig(){ConfigId="db1",DbType=DbType.Sqlite,
ConnectionString="DataSource=/Db_OrderDb.sqlite",IsAutoCloseConnection=true},
new ConnectionConfig(){ConfigId="db2",DbType=DbType.Sqlite,
ConnectionString="DataSource=/Db_OrderItemDb.sqlite",IsAutoCloseConnection=true }
});
//注意：如果是接口需要
//db.AsTenant().QueryableWithAttr<OrderItem>()
//通过实体类特性Tenant自动映射不同数据库进行查询
var list=db.QueryableWithAttr<OrderItem>()
.Includes(z => z.Order)
.ToList(); //1行代码就搞定了2个库联表查询
//不通过特性实现跨库导航
var list =db.GetConnection("db1").Queryable<OrderItem>()//Orderitem是db1
.CrossQuery(typeof(Order), "db2")//Order是db2
.Includes(z => z.Order)
.ToList();12、原生sql
//无参数
var dt=db.Ado.GetDataTable(sql) //上面列表中 SqlQuery 等方法都可以不一定是GetDataTable
//参数1：简化用法
var dt=db.Ado.GetDataTable("select * from table where id=@id and name like @name",
new{id=1,name="%"+jack+"%"});//参数也可以是数组，比如in的情况下
//插入 更新操作一般用
db.Ado.ExecuteCommand(sql);
//调用存储过程
//简单用法
var dt = db.Ado.UseStoredProcedure().GetDataTable("sp_school",new{name="张三",age=0});
//带有output的存储过程
var nameP= new SugarParameter("@name", "张三");
var ageP= new SugarParameter("@age", null, true);//设置为output，//ageP.Value可以拿到output值
var dt = db.Ado.UseStoredProcedure().GetDataTable("sp_school",nameP,ageP);//返回dt
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
