自研ORM 完美支持 Exists查询 非常灵活

0100

示例代码

Case 1 Code

                var data = db.Query<Product>()
                    .Where(w => db.Query<Product>().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1).Any())
                    .Select(s => new
                    {
                        s.ProductId,
                        s.ProductCode
                    }).ToList();
                var data = db.Query<Product>()
                    .Where(w => db.Query<Product>().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1).Any())
                    .Select(s => new
                    {
                        s.ProductId,
                        s.ProductCode
                    }).ToList();
                var data = db.Query<Product>()
                    .Where(w => db.Query<Product>().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 ) )
SELECT `p1`.`ProductId` AS `ProductId`,`p1`.`ProductCode` AS `ProductCode` FROM `Product` `p1`
WHERE  EXISTS ( SELECT 1 FROM `Product` `p2`
WHERE ( `p2`.`ProductId` = 1 ) )
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<Product>().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1);
                var data = db.Query<Product>()
                    .Where(w => existsQuery.Any())
                    .Select(s => new
                    {
                        s.ProductId,
                        s.ProductCode
                    }).ToList();
                var existsQuery = db.Query<Product>().WhereIF(!string.IsNullOrWhiteSpace("测试"), a => a.ProductId == 1).Select(s => 1);

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

                var data = db.Query<Product>()
                    .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 ) )
SELECT `p1`.`ProductId` AS `ProductId`,`p1`.`ProductCode` AS `ProductCode` FROM `Product` `p1`
WHERE  EXISTS ( SELECT 1 FROM `Product` `p2`
WHERE ( `p2`.`ProductId` = 1 ) )
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<Product>()
                    .Where(a => SqlFunc.Operation(a.CreateTime.ToString("yyyy-MM-dd"), ">=", DateTime.Now.ToString("yyyy-MM-dd")))
                    .Where(a => db.Query<Product>().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;
                var queryBuilder = db.Query<Product>()
                    .Where(a => SqlFunc.Operation(a.CreateTime.ToString("yyyy-MM-dd"), ">=", DateTime.Now.ToString("yyyy-MM-dd")))
                    .Where(a => db.Query<Product>().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;
                var queryBuilder = db.Query<Product>()
                    .Where(a => SqlFunc.Operation(a.CreateTime.ToString("yyyy-MM-dd"), ">=", DateTime.Now.ToString("yyyy-MM-dd")))
                    .Where(a => db.Query<Product>().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
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
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子查询

                    var data = db.Query<Product>().Select(s => new
                    {
                        XX = db.Query<Product>().Select(s => 1).First() //需要注意的是，这里只能返回单一结果
                    }).First();
                    var data = db.Query<Product>().Select(s => new
                {
                    XX = db.Query<Product>().Select(s => 1).First() //需要注意的是，这里只能返回单一结果
                }).First();
                    var data = db.Query<Product>().Select(s => new
                {
                    XX = db.Query<Product>().Select(s => 1).First() //需要注意的是，这里只能返回单一结果
                }).First();

  • Select查询 (Case When)

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

  • 分组查询

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

  • 分组聚合查询

                var sql = db.Query<Order>().InnerJoin<OrderDetail>((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 sql = db.Query<Order>().InnerJoin<OrderDetail>((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 sql = db.Query<Order>().InnerJoin<OrderDetail>((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<Product>().OrderBy(s => new
                {
                    s.CreateTime
                }).ToList();
                //这是多个字段排序使用方法 还有其它重载方法
                var data = db.Query<Product>().OrderBy(s => new
            {
                s.CreateTime
            }).ToList();
            //这是多个字段排序使用方法 还有其它重载方法
                var data = db.Query<Product>().OrderBy(s => new
            {
                s.CreateTime
            }).ToList();
            //这是多个字段排序使用方法 还有其它重载方法

  • Having查询

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

© 版权声明
文章版权归作者所有，未经允许请勿转载，侵权请联系 admin@trc20.tw 删除。
THE END
博客文章
喜欢就支持一下吧
点赞0

Warning: mysqli_query(): (HY000/3): Error writing file '/tmp/MYouEwDH' (Errcode: 28 - No space left on device) in /www/wwwroot/583.cn/wp-includes/class-wpdb.php on line 2345
admin的头像-五八三
相关推荐
评论 抢沙发
头像
欢迎您留下宝贵的见解！
提交
头像

昵称

图形验证码
取消
昵称

请填写用户信息：

  • 昵称(必填)
  • 邮箱(必填)
代码

请输入代码：

确认
图片
热门推荐