自研ORM嵌套查询和子查询，强不强大您说了算。

本文主要是介绍自研ORM嵌套查询和子查询，强不强大您说了算。，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

测试代码

                var count = 0;
                var refAsync = new RefAsync<int>();

                //下面示例方法的重载均支持
                var query = db.Query<Product>().Select(s => new
                {
                    WithAttr_First = db.QueryWithAttr<Product>().First(),//支持表达式动态切库 相当叼的用法，切库只能用于嵌套查询
                    WithAttr_FirstAsync = db.QueryWithAttr<Product>().FirstAsync(),
                    WithAttr_ToList = db.QueryWithAttr<Product>().ToList(),
                    WithAttr_ToListAsync = db.QueryWithAttr<Product>().ToListAsync(),
                    First_1 = db.Query<Category>().Select(s => 1).First(),//解析成Sql
                    First = db.Query<Category>().First(),
                    FirstAsync = db.Query<Category>().FirstAsync(),
                    ToArray = db.Query<Category>().ToArray(),
                    ToArrayAsync = db.Query<Category>().ToArrayAsync(),
                    ToList = db.Query<Category>().ToList(),
                    ToListAsync = db.Query<Category>().ToListAsync(),
                    ToPageList = db.Query<Category>().ToPageList(1, 10),
                    ToPageListAsync = db.Query<Category>().ToPageListAsync(1, 10),
                    ToPageList_Count = db.Query<Category>().ToPageList(1, 10, ref count),
                    ToPageListAsync_Count = db.Query<Category>().ToPageListAsync(1, 10, refAsync),
                    ToDictionary = db.Query<Category>().ToDictionary(),
                    ToDictionaryAsync = db.Query<Category>().ToDictionaryAsync(),
                    ToDictionaryList = db.Query<Category>().ToDictionaryList(),
                    ToDictionaryListAsync = db.Query<Category>().ToDictionaryListAsync(),
                    ToDictionaryPageList = db.Query<Category>().ToDictionaryPageList(1, 10),
                    ToDictionaryPageListAsync = db.Query<Category>().ToDictionaryPageListAsync(1, 10),
                    ToDictionaryPageList_Count = db.Query<Category>().ToDictionaryPageList(1, 10, ref count),
                    ToDictionaryPageListAsync_Count = db.Query<Category>().ToDictionaryPageListAsync(1, 10, refAsync),
                    ToDataTable = db.Query<Category>().ToDataTable(),
                    ToDataTableAsync = db.Query<Category>().ToDataTableAsync(),
                    ObjToJson = db.Query<Category>().ObjToJson(),
                    ObjToJsonAsync = db.Query<Category>().ObjToJsonAsync(),
                    ObjListToJson = db.Query<Category>().ObjListToJson(),
                    ObjListToJsonAsync = db.Query<Category>().ObjListToJsonAsync(),
                    Max = db.Query<Category>().Max(a => a.CategoryId),//解析成Sql
                    MaxAsync = db.Query<Category>().MaxAsync(a => a.CategoryId),
                    Min = db.Query<Category>().Min(a => a.CategoryId),//解析成Sql
                    MinAsync = db.Query<Category>().MinAsync(a => a.CategoryId),
                    Count = db.Query<Category>().Count(),//解析成Sql
                    CountAsync = db.Query<Category>().CountAsync(),
                    Sum = db.Query<Product>().Sum(s => s.Number),//解析成Sql
                    SumAsync = db.Query<Product>().SumAsync(s => s.Number),
                    Avg = db.Query<Product>().Avg(s => s.Number),//解析成Sql
                    AvgAsync = db.Query<Product>().AvgAsync(s => s.Number)
                });

                var data = query.First();

                //支持所有重载方法,子查询 一定要调用 返回结果的方法 和普通查询一样

                var json = db.Query<Product>().Select(a => new
                {
                    Max_Query = db.Query<Category>().Where(x => x.CategoryId == a.CategoryId).Max(a => a.CategoryId),
                    Min_Query = db.Query<Category>().OrderBy(o => o.CategoryId, OrderByType.DESC).Min(a => a.CategoryName),
                    Count_Query = db.Query<Category>().OrderBy(o => o.CategoryId, OrderByType.DESC).Count(),
                    Avg_Query = db.Query<Product>().Avg(a => a.Number),
                    Sum_Query = db.Query<Product>().Sum(a => a.Number),
                    Sum_Query_2 = db.Query<Product>().Sum<decimal>("Number"),
                    TestQuery = db.Query<Category>()
                    .InnerJoin<Product>((a, b) => a.CategoryId == b.CategoryId)
                    .Select((a, b) => new
                    {
                        a.CategoryId,
                        a.CategoryName,
                        b.ProductCode,
                        b.ProductName,
                        XX = db.Query<Category>()
                        // 特别注意：同一个Where 只能引用一个上级别名 如果当前别名和上级别名一致则优先解析当前别名
                        .Where(c => c.CategoryId == a.CategoryId)
                        .Where(c => c.CategoryId == b.CategoryId).First()
                    }).First(),

                    //甚至可以在这里使用ado方式查询，虽然不推荐，但还是很厉害的
                    Ado_Test = db.Ado.ExecuteReader(System.Data.CommandType.Text, "select 1", null).ListBuild<int>(),
                }).ObjToJson();
                Console.WriteLine(json);

解析后Sql

SELECT p2.`ProductId`,p2.`CategoryId`,p2.`ProductCode`,p2.`ProductName`,p2.`DeleteMark`,p2.`CreateTime`,p2.`ModifyTime`,p2.`Custom1`,p2.`Custom2`,p2.`Custom3`,p2.`Custom4`,p2.`Custom5`,p2.`Custom6`,p2.`Custom7`,p2.`Custom8`,p2.`Custom9`,p2.`Custom10`,p2.`Custom11`,p2.`Custom12`,p2.`Number` FROM `Product` `p2` Limit 1

SELECT p2.`ProductId`,p2.`CategoryId`,p2.`ProductCode`,p2.`ProductName`,p2.`DeleteMark`,p2.`CreateTime`,p2.`ModifyTime`,p2.`Custom1`,p2.`Custom2`,p2.`Custom3`,p2.`Custom4`,p2.`Custom5`,p2.`Custom6`,p2.`Custom7`,p2.`Custom8`,p2.`Custom9`,p2.`Custom10`,p2.`Custom11`,p2.`Custom12`,p2.`Number` FROM `Product` `p2` Limit 1

SELECT p2.`ProductId`,p2.`CategoryId`,p2.`ProductCode`,p2.`ProductName`,p2.`DeleteMark`,p2.`CreateTime`,p2.`ModifyTime`,p2.`Custom1`,p2.`Custom2`,p2.`Custom3`,p2.`Custom4`,p2.`Custom5`,p2.`Custom6`,p2.`Custom7`,p2.`Custom8`,p2.`Custom9`,p2.`Custom10`,p2.`Custom11`,p2.`Custom12`,p2.`Number` FROM `Product` `p2`

SELECT p2.`ProductId`,p2.`CategoryId`,p2.`ProductCode`,p2.`ProductName`,p2.`DeleteMark`,p2.`CreateTime`,p2.`ModifyTime`,p2.`Custom1`,p2.`Custom2`,p2.`Custom3`,p2.`Custom4`,p2.`Custom5`,p2.`Custom6`,p2.`Custom7`,p2.`Custom8`,p2.`Custom9`,p2.`Custom10`,p2.`Custom11`,p2.`Custom12`,p2.`Number` FROM `Product` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` Limit 1

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` Limit 1

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT COUNT( 1 ) FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT COUNT( 1 ) FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT COUNT( 1 ) FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` LIMIT 0,10

SELECT COUNT( 1 ) FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` Limit 1

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2` Limit 1

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT p2.`CategoryId`,p2.`CategoryName` FROM `Category` `p2`

SELECT MAX( `p2`.`CategoryId` ) AS `CategoryId` FROM `Category` `p2`

SELECT MIN( `p2`.`CategoryId` ) AS `CategoryId` FROM `Category` `p2`

SELECT COUNT( 1 ) FROM `Category` `p2`

SELECT SUM( `p2`.`Number` ) AS `Number` FROM `Product` `p2`

SELECT AVG( `p2`.`Number` ) AS `Number` FROM `Product` `p2`

SELECT 0 AS `fast_args_index_0`,1 AS `fast_args_index_1`,2 AS `fast_args_index_2`,3 AS `fast_args_index_3`,( SELECT 1 FROM `Category` `p2` Limit 1 ) AS `First_1`,5 AS `fast_args_index_5`,6 AS `fast_args_index_6`,7 AS `fast_args_index_7`,8 AS `fast_args_index_8`,9 AS `fast_args_index_9`,10 AS `fast_args_index_10`,11 AS `fast_args_index_11`,12 AS `fast_args_index_12`,13 AS `fast_args_index_13`,14 AS `fast_args_index_14`,15 AS `fast_args_index_15`,16 AS `fast_args_index_16`,17 AS `fast_args_index_17`,18 AS `fast_args_index_18`,19 AS `fast_args_index_19`,20 AS `fast_args_index_20`,21 AS `fast_args_index_21`,22 AS `fast_args_index_22`,23 AS `fast_args_index_23`,24 AS `fast_args_index_24`,25 AS `fast_args_index_25`,26 AS `fast_args_index_26`,27 AS `fast_args_index_27`,28 AS `fast_args_index_28`,( SELECT MAX( `p2`.`CategoryId` ) AS `CategoryId` FROM `Category` `p2` ) AS `Max`,30 AS `fast_args_index_30`,( SELECT MIN( `p2`.`CategoryId` ) AS `CategoryId` FROM `Category` `p2` ) AS `Min`,32 AS `fast_args_index_32`,( SELECT COUNT( 1 ) FROM `Category` `p2` ) AS `Count`,34 AS `fast_args_index_34`,( SELECT SUM( `p2`.`Number` ) AS `Number` FROM `Product` `p2` ) AS `Sum`,36 AS `fast_args_index_36`,( SELECT AVG( `p2`.`Number` ) AS `Number` FROM `Product` `p2` ) AS `Avg`,38 AS `fast_args_index_38` FROM `Product` Limit 1

SELECT p4.`CategoryId`,p4.`CategoryName`,p3.`ProductId`,p3.`ProductCode`,p3.`ProductName`,p3.`DeleteMark`,p3.`CreateTime`,p3.`ModifyTime`,p3.`Custom1`,p3.`Custom2`,p3.`Custom3`,p3.`Custom4`,p3.`Custom5`,p3.`Custom6`,p3.`Custom7`,p3.`Custom8`,p3.`Custom9`,p3.`Custom10`,p3.`Custom11`,p3.`Custom12`,p3.`Number` FROM `Category` `p4`
RIGHT JOIN `Category` `p2` ON ( `p4`.`CategoryId` = `p2`.`CategoryId` )
RIGHT JOIN `Product` `p3` ON ( `p4`.`CategoryId` = `p3`.`CategoryId` ) Limit 1

SELECT `p2`.`CategoryId` AS `CategoryId`,`p2`.`CategoryName` AS `CategoryName`,`p3`.`ProductCode` AS `ProductCode`,`p3`.`ProductName` AS `ProductName`,4 AS `fast_args_index_4` FROM `Category` `p2`
INNER JOIN `Product` `p3` ON ( `p2`.`CategoryId` = `p3`.`CategoryId` ) Limit 1

select 1

SELECT ( SELECT MAX( `p2`.`CategoryId` ) AS `CategoryId` FROM `Category` `p2` ) AS `Max_Query`,( SELECT MIN( `p2`.`CategoryName` ) AS `CategoryName` FROM `Category` `p2`
ORDER BY `p2`.`CategoryId` DESC ) AS `Min_Query`,( SELECT COUNT( 1 ) FROM `Category` `p2`
ORDER BY `p2`.`CategoryId` DESC ) AS `Count_Query`,( SELECT AVG( `p2`.`Number` ) AS `Number` FROM `Product` `p2` ) AS `Avg_Query`,( SELECT SUM( `p2`.`Number` ) AS `Number` FROM `Product` `p2` ) AS `Sum_Query`,( SELECT SUM( Number ) FROM `Product` `p2` ) AS `Sum_Query_2`,6 AS `fast_args_index_6`,7 AS `fast_args_index_7` FROM `Product` Limit 1

{"Max_Query":4,"Min_Query":"测 试 ","Count_Query":4,"Avg_Query":0,"Sum_Query":0,"Sum_Query_2":0,"TestQuery":{"CategoryId":1,"CategoryName":"测 试 ","ProductCode":"测 试 编 号 _1686464371_6","ProductName":"测 试 名 称 _1686464371_6","XX":{"CategoryId":1,"CategoryName":"测 试 "}},"Ado_Test":[1]}

From子查询

                var subQuery = db.Query<Product>();
                var data = db.Query(subQuery).OrderBy(o => o.ProductCode).ToList();

Join子查询

                var subQuery = db.Query<Product>();
                var data = db.Query<Product>().InnerJoin(subQuery, (a, b) => a.ProductId == b.ProductId).ToList();

这篇关于自研ORM嵌套查询和子查询，强不强大您说了算。的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

Java教程

自研ORM嵌套查询和子查询，强不强大您说了算。

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯