起因,想測試下查詢語句的生成,按以下邏輯程式碼示例
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`)
革命尚未成功,同志仍須努力