测试代码
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();
手机扫一扫
移动阅读更方便
你可能感兴趣的文章