linq - 如何将此 SQL 查询转换为 EF Core 中的 LINQ 查询？

Question

我想将以下 SQL 查询转换为 EFCore LINQ 查询

SELECT t.*
FROM Trend t
LEFT JOIN Trend t1 
    on  t1.ArticleId = t.ArticleId
    and t1.Country = t.Country
    and t1.Created > t.Created
WHERE t1.ArticleId is null

---

所以我得到的是这个

from t1 in ctx.Trend
join t2 in ctx.Trend
    on new 
    { 
        x1 = t1.ArticleId, 
        x2 = t1.Country
    }
    equals new 
    { 
        x1 = t2.ArticleId, 
        x2 = t2.Country
    }
where t2.ArticleId == null
   && t1.Created > t2.Created 
select new { t1, t2 };

这转化为

SELECT [t].[Id], [t].[Country], [t].[Created], [t].[ArticleId], [t].[Price], [t0].[Id], [t0].[Country], [t0].[Created], [t0].[ArticleId], [t0].[Price]
FROM [Trend] AS [t]
INNER JOIN [Trend] AS [t0] ON ([t].[ArticleId] = [t0].[ArticleId]) AND ((([t].[Country] = [t0].[Country]) AND ([t].[Country] IS NOT NULL AND [t0].[Country] IS NOT NULL)) OR ([t].[Country] IS NULL AND [t0].[Country] IS NULL))
WHERE CAST(0 AS bit) = CAST(1 AS bit)

我不明白为什么会有一个WHERE true = false条件。

然后我将t2.ArticleId == null条件提升到ctx.Trend

from t1 in ctx.Trend
join t2 in ctx.Trend.Where(trend => trend.ArticleId == null)
    on new 
    { 
        x1 = t1.ArticleId, 
        x2 = t1.Country
    }
    equals new 
    { 
        x1 = t2.ArticleId, 
        x2 = t2.Country,
    }
where t1.Created > t2.Created 
select new { t1, t2 };

这转化为

SELECT [t].[Id], [t].[Country], [t].[Created], [t].[Discount], [t].[ArticleId], [t].[Price], [t1].[Id], [t1].[Country], [t1].[Created], [t1].[Discount], [t1].[ArticleId], [t1].[Price]
FROM [Trend] AS [t]
INNER JOIN (
    SELECT [t0].[Id], [t0].[Country], [t0].[Created], [t0].[Discount], [t0].[ArticleId], [t0].[Price]
    FROM [Trend] AS [t0]
    WHERE CAST(0 AS bit) = CAST(1 AS bit)
) AS [t1] ON ([t].[ArticleId] = [t1].[ArticleId]) AND ((([t].[Country] = [t1].[Country]) AND ([t].[Country] IS NOT NULL AND [t1].[Country] IS NOT NULL)) OR ([t].[Country] IS NULL AND [t1].[Country] IS NULL))
WHERE [t].[Created] > [t1].[Created]

---

该查询基于此问题以获取更多上下文。但这不应该是必要的。

标签： linqtsqlentity-framework-core

Answer 1

这个怎么样？

from t1 in ctx.Trend
from t2 in ctx.Trend.Where(trend => trend.ArticleId == null && trend.ArticleId == t1.ArticleId && trend.Country == t1.Country && trend.Created > t1.Created).DefaultIfEmpty() 
select new { t1, t2 };