[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

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

posted @   Kogel  阅读(4313)  评论(0编辑  收藏  举报
编辑推荐:
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行:
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单,K8s 太复杂?w7panel 让容器管理更轻松!
点击右上角即可分享
微信分享提示