Case 1 Code

var data = db.Query()                    .Where(w => db.Query().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1).Any())                    .Select(s => new                    {                        s.ProductId,                        s.ProductCode                    }).ToList();

Case 1 Sql

(资料图片)

SELECT `p1`.`ProductId` AS `ProductId`,`p1`.`ProductCode` AS `ProductCode` FROM `Product` `p1`WHERE  EXISTS ( SELECT 1 FROM `Product` `p2`WHERE ( `p2`.`ProductId` = 1 ) )

Case 2 Code (如需主表作为条件 需使用Case1 写法)

var existsQuery = db.Query().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1);                var data = db.Query()                    .Where(w => existsQuery.Any())                    .Select(s => new                    {                        s.ProductId,                        s.ProductCode                    }).ToList();

Case 2 Sql

SELECT `p1`.`ProductId` AS `ProductId`,`p1`.`ProductCode` AS `ProductCode` FROM `Product` `p1`WHERE  EXISTS ( SELECT 1 FROM `Product` `p2`WHERE ( `p2`.`ProductId` = 1 ) )

Case 3 Code（真实项目写法的伪代码，可自由搭配，基本上可以媲美手写sql）

var queryBuilder = db.Query()                    .Where(a => SqlFunc.Operation(a.CreateTime.ToString("yyyy-MM-dd"), ">=", DateTime.Now.ToString("yyyy-MM-dd")))                    .Where(a => db.Query().Where(b => a.ProductName.Contains("主表") || b.Custom1.Contains("子表")).WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1).Any())                    .Select(s => new                    {                        s.ProductId,                        s.ProductCode                    }).QueryBuilder;

Case 3 Print Result

SELECT `p1`.`ProductId` AS `ProductId`,`p1`.`ProductCode` AS `ProductCode` FROM `Product` `p1`WHERE DATE_FORMAT( `p1`.`CreateTime`,"%Y-%m-%d" ) >= DATE_FORMAT( @Now_1,"%Y-%m-%d" ) AND  EXISTS ( SELECT 1 FROM `Product` `p2`WHERE ( `p1`.`ProductName` LIKE CONCAT( "%","主表","%" ) OR `p2`.`Custom1` LIKE CONCAT( "%","子表","%" ) ) AND ( `p2`.`ProductId` = 1 ) )参数名称:Now_1 参数值:2023-06-07 18:35:14

3.亮点功能

1.别名自动编排和联表查询一样

2.支持Query 所有非返回结果集方法,结尾需调用Any方法以及重载方法，非常灵活 ！！！

开源项目地址：https://github.com/China-Mr-zhong/Fast-Framework (唯一)

其它查询示例

Select查询 (Case When)

var data = db.Query().Select(s => new                {                    CaseTest1 = SqlFunc.Case(s.Custom1).When("1").Then("xx1").When("2").Then("xx2").Else("xx3").End(),                    CaseTest2 = SqlFunc.CaseWhen(s.Custom1 == "1").Then("xx1").When(s.Custom1 == "2").Then("xx2").Else("xx3").End()                }).ToList();

分组查询

var data = db.Query().GroupBy(s => new            {                s.ProductId,                s.ProductName            }).ToList();

分组聚合查询

var sql = db.Query().InnerJoin((a, b) => a.OrderId == b.OrderId).GroupBy((a, b) => new            {                a.OrderCode            }).Select((a, b) => new            {                a.OrderCode,                Sum_Qty = SqlFunc.Sum(b.Qty)//支持嵌套            }).ToList();

排序查询

var data = db.Query().OrderBy(s => new            {                s.CreateTime            }).ToList();            //这是多个字段排序使用方法 还有其它重载方法

Having查询

var data = db.Query().GroupBy(s => new            {                s.ProductId,                s.ProductName            }).Having(s => SqlFunc.Count(s.ProductId) > 1).ToList();            //必须先使用GroupBy方法 懂得都懂