EF Core中Join可以进行子查询

我们来看看下面的代码，这个代码是一个INNER JOIN的EF Core查询，其中用SubCategory表INNER JOIN了SubCategoryLanguage表，但是我们需要在SubCategoryLanguage表上只查询出其DataStatus等于1的行，所以需要用到子查询：

var count = dbContext.SubCategory.Where(e => e.CategoryCode == "0e3b0d17-516e-489e-9df3-3ecda10d14eb" && e.DataStatus == 1)
.Join(dbContext.SubCategoryLanguage.Where(e => e.DataStatus == 1), sc => sc.SubCategoryCode, sl => sl.SubCategoryCode, (sc, sl) => new { sc.SubCategoryCode, sl.LanguageCode, sl.SubCategoryName })
.Count(s => s.SubCategoryName == "category001" && s.LanguageCode == "CN");

如上面代码黄色高亮所示，我们在SubCategoryLanguage表的查询中加上了DataStatus == 1的查询条件，运行该代码使用EF Core的后台日志我们可以看到生成的SQL如下：

=============================== EF Core log started ===============================
Executed DbCommand (176ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
SELECT COUNT(*)
FROM [MD].[SubCategory] AS [e]
INNER JOIN (
    SELECT [e0].*
    FROM [MD].[SubCategoryLanguage] AS [e0]
    WHERE [e0].[DataStatus] = 1
) AS [t] ON [e].[SubCategoryCode] = [t].[SubCategoryCode]
WHERE (([e].[CategoryCode] = N'0e3b0d17-516e-489e-9df3-3ecda10d14eb') AND ([e].[DataStatus] = 1)) AND (([t].[SubCategoryName] = N'category001') AND ([t].[LanguageCode] = N'CN'))
=============================== EF Core log finished ===============================

我们可以看到在生成的SQL中，INNER JOIN在SubCategoryLanguage表上使用了子查询，在黄色高亮部分我们还可以看到其加上了WHERE条件[DataStatus] = 1。所以在EF Core中使用Join的时候，是可以使用子查询来限制Join表的查询条件的。