自研ORM 子查询&嵌套查询
阅读原文时间:2023年07月08日阅读:2

作者 Mr-zhong

代码改变世界….

一、前言

Fast Framework 基于NET6.0 封装的轻量级 ORM 框架 支持多种数据库 SqlServer Oracle MySql PostgreSql Sqlite

优点: 体积小、可动态切换不同实现类库、原生支持微软特性、流畅API、使用简单、性能高、模型数据绑定采用 委托、强大的表达式解析、支持多种子查询可实现较为复杂查询、源代码可读性强。

缺点:目前仅支持Db Frist Code Frist 后续迭代。

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

更多示例:https://www.cnblogs.com/China-Mr-zhong/p/17514567.html

  • 导航查询

    • 示例代码

                      /// <summary>
                      /// 类别
                      /// </summary>
                      public class Category
                      {
                          /// <summary>
                          /// 类别ID
                          /// </summary>
                          [Key]
                          public int CategoryId { get; set; }
                      /// &lt;summary&gt;
                      /// 类别名称
                      /// &lt;/summary&gt;
                      public string CategoryName { get; set; }
                  /// &amp;lt;summary&amp;gt;
                  /// 产品 Navigate MainName和ChildName 可不显示指定,会自动查找主键匹配或ID为结尾的属性
                  /// &amp;lt;/summary&amp;gt;
                  [Navigate(MainName = nameof(CategoryId), ChildName = nameof(Product.CategoryId))]
                  public IEnumerable&amp;lt;Product&amp;gt; Products { get; set; }
      
              }
      
              var data = db.Query&amp;lt;Category&amp;gt;()
                  .Include(i =&amp;gt; i.Products)
                  .ToList();&lt;/code&gt;&lt;/pre&gt;&lt;/li&gt;</code></pre><li><p>执行后Sql</p>
      SELECT Include_A.`CategoryId`,Include_A.`CategoryName`,Include_B.`ProductId`,Include_B.`ProductCode`,Include_B.`ProductName`,Include_B.`DeleteMark`,Include_B.`CreateTime`,Include_B.`ModifyTime`,Include_B.`Custom1`,Include_B.`Custom2`,Include_B.`Custom3`,Include_B.`Custom4`,Include_B.`Custom5`,Include_B.`Custom6`,Include_B.`Custom7`,Include_B.`Custom8`,Include_B.`Custom9`,Include_B.`Custom10`,Include_B.`Custom11`,Include_B.`Custom12` FROM `Category` `Include_A`
      INNER JOIN `Product` `Include_B` ON `Include_A`.`CategoryId` = `Include_B`.`CategoryId`
  • Join子查询

    • 示例代码

                      var subQuery1 = db.Query<Product>().Select(s => new
                      {
                          s.ProductId,
                          s.CategoryId,
                          s.ProductCode,
                          s.ProductName,
                          s.DeleteMark
                      });
                      var data = db.Query<Category>().InnerJoin(subQuery1, (a, b) => a.CategoryId == b.CategoryId).ToList();
    • 执行后Sql

      SELECT p1.`CategoryId`,p1.`CategoryName`,p2.`ProductId`,p2.`ProductCode`,p2.`ProductName`,p2.`DeleteMark` FROM `Category` `p1`
      INNER JOIN ( SELECT `ProductId` AS `ProductId`,`CategoryId` AS `CategoryId`,`ProductCode` AS `ProductCode`,`ProductName` AS `ProductName`,`DeleteMark` AS `DeleteMark` FROM `Product` ) `p2` ON ( `p1`.`CategoryId` = `p2`.`CategoryId` )
  • From子查询

    • 示例代码

                      var subQuery2 = db.Query<Product>().Select(s=>new
                      {
                          s.ProductId,
                          s.CategoryId,
                          s.ProductCode,
                          s.ProductName,
                          s.DeleteMark
                      });
                      var data = db.Query(subQuery2).ToList();
    • 执行后Sql

      SELECT * FROM ( SELECT `ProductId` AS `ProductId`,`CategoryId` AS `CategoryId`,`ProductCode` AS `ProductCode`,`ProductName` AS `ProductName`,`DeleteMark` AS `DeleteMark` FROM `Product` ) x
  • Select子查询

    • 示例代码

                      var data = db.Query<Product>().Select(s => new
                      {
                          CategoryName = db.Query<Category>().Where(w => w.CategoryId == 1).Select(s => s.CategoryName).First()
                      }).First();
    • 执行后Sql

      SELECT ( SELECT `p2`.`CategoryName` FROM `Category` `p2`
      WHERE ( `p2`.`CategoryId` = 1 ) Limit 1 ) AS `CategoryName` FROM `Product` `p1` Limit 1
  • Select嵌套查询

    • 示例代码

      var data1 = db.Query<Product>().Select(s => new
                      {
                          NestedQuery = db.Query<Category>().Where(w => w.CategoryId == s.CategoryId).ToList()
                      }).First();
                  var data2 = db.Query&lt;Product&gt;().Where(w =&gt; w.ProductId == 1).Select(s =&gt; new
                  {
                      NestedQuery = db.Query&lt;Category&gt;().Where(w =&gt; w.CategoryId == s.CategoryId).ToList()
                  }).First();</code></pre></li>
    • 执行后Sql

      //内部机制主查询有结果才执行嵌套查询,懒加载实现
      
      SELECT 0 AS `fast_args_index_0` FROM `Product` `p1` Limit 1
      
      --------------------------------------------------------------------------
      
      SELECT p2.`CategoryId`,p2.`CategoryName`,p1.`ProductId`,p1.`ProductCode`,p1.`ProductName`,p1.`DeleteMark`,p1.`CreateTime`,p1.`ModifyTime`,p1.`Custom1`,p1.`Custom2`,p1.`Custom3`,p1.`Custom4`,p1.`Custom5`,p1.`Custom6`,p1.`Custom7`,p1.`Custom8`,p1.`Custom9`,p1.`Custom10`,p1.`Custom11`,p1.`Custom12` FROM `Category` `p2`
      RIGHT JOIN `Product` `p1` ON ( `p2`.`CategoryId` = `p1`.`CategoryId` )
      
      SELECT 0 AS `fast_args_index_0` FROM `Product` `p1`
      WHERE ( `p1`.`ProductId` = 1 ) Limit 1
  • Where子查询

    • 示例代码

                      var data = db.Query<Category>().Where(w => w.CategoryId == 1 && db.Query<Product>().Where(w => w.CategoryId == 1).Select(s => 1).Any()).First();//Any支持取反
    • 执行后Sql

      SELECT p1.`CategoryId`,p1.`CategoryName` FROM `Category` `p1`
      WHERE ( ( `p1`.`CategoryId` = 1 ) AND EXISTS ( SELECT 1 FROM `Product` `p2`
      WHERE ( `p2`.`CategoryId` = 1 ) ) ) Limit 1

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章