目录
- [LINQ2Dapper]最完整Dapper To Linq框架(一)---基础查询
- [LINQ2Dapper]最完整Dapper To Linq框架(二)---动态化查询
- [LINQ2Dapper]最完整Dapper To Linq框架(三)---实体类关系映射
- [LINQ2Dapper]最完整Dapper To Linq框架(四)---Linq和SQL并行使用
- [LINQ2Dapper]最完整Dapper To Linq框架(五)---查看Linq实际执行的SQL
- [LINQ2Dapper]最完整Dapper To Linq框架(六)---多表联合与匿名类型返回
1.多表联合查询
默认情况下Where,Get,ToList,PageList等函数只支持单表操作,
如
var comment = conn.QuerySet<Comment>() .Where(x => x.Id.NotIn(new int[] { 1, 2, 3 }) && x.SubTime.AddMinutes(50) < DateTime.Now.AddDays(-1) && x.Type.IsNotNull()) .ToList();
3.1.2后支持双表和三表同时映射操作,需要通过From函数指定映射表,
如
var comment = conn.QuerySet<Comment>() .From<Comment, News, ResourceMapping>() .Where((a, b, c) => a.Id == 1 && b.Headlines.IsNotNull() && c.RSize > 100);
也可以通过GetQuerySet()返回基础的QuerySet对象
var comment = conn.QuerySet<Comment>() .From<Comment, News, ResourceMapping>() .Where((a, b, c) => a.Id == 1 && b.Headlines.IsNotNull() && c.RSize > 100) .GetQuerySet() .ToList();
以上是三表联合查询,如果有更多表联合需求可以通过自己扩展实现,
如
/// <summary> /// 定义一个四表联合扩展类 /// </summary> /// <typeparam name="T">对应QuerySet的泛型</typeparam> /// <typeparam name="T1">扩展泛型1</typeparam> /// <typeparam name="T2">扩展泛型2</typeparam> /// <typeparam name="T3">扩展泛型3</typeparam> /// <typeparam name="T4">扩展泛型4</typeparam> public class testFrom<T, T1, T2, T3,T4> : ISelect<T> { public testFrom(QuerySet<T> querySet) : base(querySet) { } //定义了一个Where public testFrom<T, T1, T2, T3,T4> Where(Expression<Func<T1, T2, T3,T4, bool>> select) { base.Where(select); return this; } //定义了一个ToList public IEnumerable<TReturn> ToList<TReturn>(Expression<Func<T1, T2, T3,T4, TReturn>> select) { return base.ToList<TReturn>(select); } }
然后使用扩展类
//首先声明一个QuerySet var querySet = conn.QuerySet<Comment>(); /*把定义的querySet实例带入到扩展类中 (注意:第一个T类型必须对应querySet的泛型,如Comment)*/ var list = new testFrom<Comment, Comment, News, ResourceMapping, LikeRecord>(querySet) .Where((a, b, c, d) => a.Id == 1 && b.NewsLabel.Contains("t")) .GetQuerySet() .ToList();
以此类推,可以任意扩展联表的数量
以下是支持的函数
public class ISelect<T> { public ISelect(QuerySet<T> querySet); public TReturn Get<TReturn>(LambdaExpression exp); public QuerySet<T> GetQuerySet(); public ISelect<T> OrderBy<TProperty>(Expression<Func<TProperty, object>> field); public ISelect<T> OrderByDescing<TProperty>(Expression<Func<TProperty, object>> field); public PageList<TReturn> PageList<TReturn>(int pageIndex, int pageSize, LambdaExpression exp); public IEnumerable<TReturn> ToList<TReturn>(LambdaExpression exp); public QuerySet<T> Where(LambdaExpression exp); }
2.结果返回匿名类型
3.12版本后支持返回匿名类型,
如
var comment = conn.QuerySet<Comment>() .Where(x => x.Content == "test1" && x.Content.Contains("t")) .Get(x => new { Id = 123, ArticleId = x.ArticleId });
一些复杂的字段返回可以通过sql实现,
例如
var comment = conn.QuerySet<Comment>() .Join<Comment, News>((a, b) => a.ArticleId == b.Id) .Where(x => x.Content == "test1" && x.Content.Contains("t")) .Where<Comment, News>((a, b) => a.SubTime < DateTime.Now.AddDays(-5) && a.Id > a.Id % 1) .Get(x => new { count = Convert.ToInt32("(select count(1) from Comment_4)"), aaa = "6666", });
并且也支持联合查询的返回
var comment = conn.QuerySet<Comment>() .Join<Comment, News>((a, b) => a.ArticleId == b.Id) .Join<Comment, ResourceMapping>((a, b) => a.Id == b.FKId) .Where(x => x.Content == "test") .From<Comment, News, ResourceMapping>() .OrderBy<News>(x => x.Id) .Where((a, b, c) => a.ArticleId == b.Id) .PageList(1, 10, (a, b, c) => new { id = a.Id, name = b.NewsLabel, resource = c.RPath, rownum = Convert.ToInt32("ROW_NUMBER() OVER(ORDER BY Comment.Id)"), NewsLable = "News.NewsLabel" });
还支持比较复杂的子查询
子查询支持的函数有 Count() Sum<T>(字段)
例如
var comment1 = conn.QuerySet<Comment>() .Join<Comment, News>((a, b) => a.ArticleId == b.Id) .Where(x => x.Id.Between(80, 100) && x.SubTime.AddDays(-10) < DateTime.Now && x.Id > 10) .From<Comment, News>() .Get((a, b) => new { //(不查询数据库的方法可以任意使用) test = new List<int>() { 3, 3, 1 }.FirstOrDefault(y => y == 1), aaa = "6666" + "777", Content = a.Content + "‘test‘" + b.Headlines + a.IdentityId, //此字段会拼接成子查询,不用担心循环查询造成的性能问题 bbb = new QuerySet<Comment>(conn, new MySqlProvider()) .Where(y => y.ArticleId == b.Id && y.Content.Contains("test")).Sum<Comment>(x => x.Id), ccc = a.IdentityId, ddd = Convert.ToInt32("(select count(1) from Comment)") });
完整Demo可以去Github上下载:
https://github.com/a935368322/Kogel.Dapper.Test
如有问题也可以加QQ群讨论:
技术群 710217654
框架开源,可以加群下载源码