首页 > 其他分享 >自研ORM嵌套查询和子查询，强不强大您说了算。

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

时间：2023-06-11 14:45:04浏览次数：45  
标签：p2 Category 自研 db 查询 ORM Query CategoryId SELECT

测试代码

                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();

标签：p2,Category,自研,db,查询,ORM,Query,CategoryId,SELECT
From： https://www.cnblogs.com/China-Mr-zhong/p/17472939.html

相关文章

  • 在Transformers 中使用约束波束搜索引导文本生成
    引言本文假设读者已经熟悉文本生成领域波束搜索相关的背景知识，具体可参见博文如何生成文本:通过Transformers用不同的解码方法生成文本。与普通的波束搜索不同，约束波束搜索允许我们控制所生成的文本。这很有用，因为有时我们确切地知道输出中需要包含什么。例如，在机器翻译任......
  • [重读经典论文] Swin-Transformer
    参考博客：Swin-Transformer网络结构详解参考视频：12.1Swin-Transformer网络结构详解使用了类似卷积神经网络中的层次化构建方法（Hierarchicalfeaturemaps），比如特征图尺寸中有对图像下采样4倍的，8倍的以及16倍的，这样的backbone有助于在此基础上构建目标检测，实例分割等任务。使用......
  • 【论文阅读】Pyramid Vision Transformer：A Versatile Backbone for Dense Prediction
    ......
  • 飘 查询字母频率并计算 频数
    package文本字母使用频率;importjava.io.*;publicclassLetter1{publicstaticvoidmain(Stringargs[]){try{charshu[]=newchar[1000000];charzimu[]=newchar[52];intj=0;int......
  • 03地铁查询系统
    地铁查询系统2023.6.101、优化返回两个站点之间最短路径功能：成为一个类，进行单元测试。2、生成遍历车站类：要求尽可能快地遍历地铁的所有车站呢（只用经过一次，不用下车，就算经过车站）。  连接数据库：packagecom.example.underground; importjava.sql.Connection;importja......
  • SpringBoot进阶教程(七十六)多维度排序查询
    在项目中经常能遇到，需要对某些数据集合进行多维度排序的需求。对于集合多条件排序解决方案也有很多，今天我们就介绍一种，思路大致是设置一个分值的集合，这个分值是按照需求来设定大小的，再根据分值的大小对集合排序。v需求背景我们来模拟一个需求，现在需要查询一个用户列表，该列表......
  • ALEXA排名代表什么？网站ALEXA排名怎么查询？
    ALEXA排名是根据一个站点的流量数据来列出的排名，排名越靠前，说明该网站的流量越大，被访问的次数也越多。 ......
  • 使用clickhouse和mysql查询时间对比
    业务场景，对于数据量过大的数据统计，跑脚本会很吃力先建立一个clickhouse的mysql引擎表关联本地mysql数据表,以下这个表会自动同步mysql主表数据CREATETABLEtest_table(idUInt32,messageString,contentString,remarkString,order_idString,user_idUInt......
  • Python操作Excel文件中多WorkSheet模拟数据库内连接查询
    严格意义上来讲，是可以把Excel文件看作数据库的，C#通过OLEDB.net就可以使用SQL语句操作Excel文件中的数据。本文代码使用Python扩展库openpyxl操作Excel文件中多个WorkSheet中的数据，模拟了数据库的内连接。假设Excel文件名为data.xlsx，其中第一个WorkSheet数据如下：第二个WorkSheet数据......
  • http://girke.bioinformatics.ucr.edu/
    http://girke.bioinformatics.ucr.edu/ TheGirkelabfocusesonfundamentalresearchquestionsattheintersectionofgenomebiologyandchemicalgenomics.Theseinclude:Whichfactorsingenomes,proteomesandmetabolomesarefunctionallyrelevantandpe......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99