喜聞樂見最新的ORM查詢BUG，看看有沒你關注的

起因，想測試下查詢語句的生成，按以下邏輯程式碼範例

var query = rep.GetLambdaQuery().Take(100);
            var join = query.Select(b => new { a1 = b.Id, a2 = b.F_String }).Join<TestEntityItem>((a, b) => a.a1 == b.TestEntityId);//第一次關聯
            var join2 = join.Select((a, b) => new { a3 = a.a1, a4 = b.Name })
                .Join<TestEntity>((a, b) => a.a3 == b.Id);//第二次關聯
            join2.Select((a, b) => new
            {
                a.a4,
                b.Id
            });

這裡為多層巢狀關聯,是一個比較常見到場景,根據邏輯意義和語法支援，多數ORM都能實現這個查詢
對應sql為

select 
  t4.[a4], 
  t1.[Id] 
from 
  (
    select 
      t2.[a1] as a3, 
      t3.[Name] as a4 
    from 
      (
        select 
          t1.[Id] as a1, 
          t1.[F_String] as a2 
        from 
          [TestEntity] t1 
        LIMIT 0, 100
      ) t2 
      Inner join [TestEntityItem] t3 on t2.a1 = t3.[TestEntityId]
  ) t4 
  Inner join [TestEntity] t1 on t4.a3 = t1.[Id]

測試了最近看到的幾個ORM

Chloe

var query = getContext().Query<TestEntity>().Take(100);
            var join = query.Select(b => new { a1 = b.Id, a2 = b.F_String }).Join<TestEntityItem>(JoinType.InnerJoin, (a, b) => a.a1 == b.TestEntityId);
            var query3 = join.Select((a, b) => new { a3 = a.a1, a4 = b.Name })
                .Join<TestEntity>(JoinType.InnerJoin, (a, b) => a.a3 == b.Id).Select((a, b) => new
            {
                a.a4,
                b.Id
            });
            var sql = query3.ToString();
            Console.WriteLine($"{GetType().Name}: {sql}");

輸出為

SELECT 
  [TestEntityItem].[Name] AS [a4], 
  [TestEntity0].[Id] 
FROM 
  (
    SELECT 
      [TestEntity].[Id] AS [a1], 
      [TestEntity].[F_String] AS [a2] 
    FROM 
      [TestEntity] AS [TestEntity] 
    LIMIT 
      100 OFFSET 0
  ) AS [T] 
  INNER JOIN [TestEntityItem] AS [TestEntityItem] ON [T].[a1] = [TestEntityItem].[TestEntityId] 
  INNER JOIN [TestEntity] AS [TestEntity0] ON [T].[a1] = [TestEntity0].[Id]

在第一次關聯生成的還正常，第二次關聯就不對了

FastFramework

var query = getDb().Query<TestEntity>().Take(100);
            var join = query.Select(b => new { a1 = b.Id, a2 = b.F_String }).InnerJoin<TestEntityItem>((a, b) => a.a1 == b.TestEntityId);
            var query2 = join.Select((a, b) => new { a3 = a.a1, a4 = b.Name })
                .InnerJoin<TestEntity>((a, b) => a.a3 == b.Id).Select((a, b) => new
                {
                    a.a4,
                    b.Id
                });
            var sql = query2.ToSqlString();
            Console.WriteLine($"{GetType().Name}: {sql}");

輸出

SELECT [p1].[a4] AS [a4],[p2].[Id] AS [Id] FROM [TestEntity] [p1]
INNER JOIN [TestEntityItem] [p2] ON ( [p1].[a1] = [p2].[TestEntityId] )
INNER JOIN [TestEntity] [p2] ON ( [p1].[a3] = [p2].[Id] ) LIMIT 0,100

完全不對

FreeSql

db.Aop.CurdAfter += (s, e) =>
            {
                Console.WriteLine($"{GetType().Name}: {e.Sql}");
            };
            var query = db.Select<TestEntity>().Take(100);
            var query2 = query.WithTempQuery(b => new { a1 = b.Id, a2 = b.F_String });
            var query3 = query2.FromQuery(db.Select<TestEntityItem>()).InnerJoin((a, b) => a.a1 == b.TestEntityId);
            var query4 = query3.WithTempQuery((a, b) => new { a3 = a.a1, a4 = b.Name }).FromQuery(db.Select<TestEntity>()).InnerJoin((a, b) => a.a3 == b.Id); ;
            var result = query4.WithTempQuery((a, b) => new
            {
                a.a4,
                b.Id
            }).ToList();

輸出

SELECT *
FROM (
    SELECT a."a4", b."Id"
    FROM (
        SELECT a."a1" "a3", b."Name" "a4"
        FROM (
            SELECT a."Id" "a1", a."F_String" "a2"
            FROM "TestEntity" a
            limit 0,100 ) a
        INNER JOIN "TestEntityItem" b ON a."a1" = b."TestEntityId" ) a
    INNER JOIN "TestEntity" b ON a."a3" = b."Id" ) a

基本正確,少了as語法，看著有些怪異

SqlSugar

var query = db.Queryable<TestEntity>().Take(100);
            var query2 = query.Select(b => new { a1 = b.Id, a2 = b.F_String });
            var query3 = query2.InnerJoin<TestEntityItem>((a, c) => a.a1 == c.TestEntityId);
            var query4 = query3.Select((a, c) => new { a3 = a.a1, a4 = c.Name })
            //    .InnerJoin<TestEntity>((d, e) => d.a3 == e.Id).Select((d, e) => new
            //{
            //    d.a4,
            //    e.Id
            //})
            ;
            var sql = query4.ToSqlString();
            Console.WriteLine($"{GetType().Name}: {sql}");

注意第二次關聯註釋掉了,如果加上直接異常

System.Exception:「中文提示 : Join TestEntity 錯誤, 請把 (d,e)=> 改成 (a,c,TestEntity )=> 
English Message : Join TestEntity error , Please change (d,e)=> to (a,c,TestEntity )=>.」

第一次關聯輸出

SELECT 
  `a`.`a1` AS `a3`, 
  `b`.`Name` AS `a4` 
FROM 
  (
    SELECT 
      * 
    FROM 
      (
        SELECT 
          `Id` AS `a1`, 
          `F_String` AS `a2` 
        FROM 
          `TestEntity` 
        LIMIT 
          0, 100
      ) MergeTable
  ) `a` 
  Inner JOIN `TestEntityItem` `b` ON (`a`.`a1` = `b`.`TestEntityId`)

革命尚未成功，同志仍須努力