[LINQ2Dapper]最完整Dapper To Linq框架(六)---多表联合与匿名类型返回

目录

• [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框架(六)---多表联合与匿名类型返回
• [LINQ2Dapper]最完整Dapper To Linq框架(七)---仓储模式
• [LINQ2Dapper]最完整Dapper To Linq框架(八)---导航属性

 

1.多表联合查询

默认情况下Where,Get,ToList,PageList等函数只支持单表操作，

如

?

1 2 3 4 5

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函数指定映射表，

如

?

1 2 3 4 5

var comment = conn.QuerySet<Comment>()                    .From<Comment, News, ResourceMapping>()                    .Where((a, b, c) => a.Id == 1                    && b.Headlines.IsNotNull()                    && c.RSize > 100);

也可以通过GetQuerySet()返回基础的QuerySet对象

?

1 2 3 4 5 6 7

var comment = conn.QuerySet<Comment>()                     .From<Comment, News, ResourceMapping>()                     .Where((a, b, c) => a.Id == 1                     && b.Headlines.IsNotNull()                     && c.RSize > 100)                     .GetQuerySet()                     .ToList();

以上是三表联合查询，如果有更多表联合需求可以通过自己扩展实现，

如

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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

　　然后使用扩展类

?

1 2 3 4 5 6 7 8 9

//首先声明一个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();

　　

以此类推，可以任意扩展联表的数量

以下是支持的函数

?

1 2 3 4 5 6 7 8 9 10 11 12

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版本后支持返回匿名类型，

如

?

1 2 3 4 5 6 7

var comment = conn.QuerySet<Comment>()                   .Where(x => x.Content == "test1" && x.Content.Contains("t"))                   .Get(x => new                   {                       Id = 123,                       ArticleId = x.ArticleId                   });

一些复杂的字段返回可以通过sql实现，

例如

?

1 2 3 4 5 6 7 8 9

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

并且也支持联合查询的返回

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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>(字段)

例如

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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

框架开源，可以加群下载源码