[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等函数只支持单表操作，

如

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)")

                    });