如何查询Recipe表缺失的指定语言翻译记录对？

表结构 #

主实体表 Recipe #

• Id：唯一标识符

翻译表 RecipeTranslation #

• Id：唯一标识符
• RecipeId：关联主表的唯一标识符
• LanguageCode：语言编码（字符串）
• Name：翻译后的名称（字符串）

需求说明 #

给定一组语言编码数组，查询所有应存在但缺失的翻译记录——即每个Recipe必须对应数组里的每一个LanguageCode都有一条翻译记录，最终输出缺失的RecipeId与LanguageCode配对结果。

当前单语言实现方案 #

目前仅支持单语言查询，通过循环遍历语言编码数组，每次执行以下C#代码获取对应语言的缺失记录：

var result = from ingredient in _dbContext.Ingredients
             join translation in _dbContext.IngredientTranslations
               on new { IngredientId = ingredient.Id, LanguageCode = "uk" } equals new { translation.IngredientId, translation.LanguageCode }
               into ingredientTranslation
             from x in ingredientTranslation.DefaultIfEmpty()
             where x == null
             select new { IngredientId = ingredient.Id, LanguageCode = "uk" };

多语言批量查询方案 #

方案1：LINQ to Entities 实现 #

核心思路是先生成所有Recipe与传入语言编码数组的笛卡尔积，再排除已存在于RecipeTranslation中的配对，剩余结果即为缺失的记录：

// 假设传入的目标语言编码集合为 IEnumerable<string> targetLanguages
var allPossiblePairs = from recipe in _dbContext.Recipes
                       from lang in targetLanguages
                       select new { RecipeId = recipe.Id, LanguageCode = lang };

var existingPairs = from translation in _dbContext.RecipeTranslations
                    where targetLanguages.Contains(translation.LanguageCode)
                    select new { translation.RecipeId, translation.LanguageCode };

// 取两个集合的差集，得到缺失的翻译配对
var missingTranslations = allPossiblePairs.Except(existingPairs);

方案2：原生SQL 实现 #

如果追求更高性能，可直接用原生SQL实现相同逻辑，通过笛卡尔积生成所有可能配对后，左连接翻译表筛选缺失项：

-- 示例：传入的目标语言编码为 'en','fr','de'，实际使用时需参数化传入避免注入
WITH TargetLanguages AS (
    SELECT 'en' AS LanguageCode UNION ALL
    SELECT 'fr' UNION ALL
    SELECT 'de'
),
AllPossiblePairs AS (
    SELECT r.Id AS RecipeId, tl.LanguageCode
    FROM Recipe r
    CROSS JOIN TargetLanguages tl
)
SELECT app.RecipeId, app.LanguageCode
FROM AllPossiblePairs app
LEFT JOIN RecipeTranslation rt 
    ON app.RecipeId = rt.RecipeId 
    AND app.LanguageCode = rt.LanguageCode
WHERE rt.Id IS NULL;

方案说明 #

• 两种方案均避免了循环查询，一次性完成批量检查，性能优于单语言循环方案
• LINQ方案适配EF Core等ORM框架，保持代码风格统一
• 原生SQL方案适合大数据量场景，注意通过参数化方式传入语言编码列表，防止SQL注入

内容的提问来源于stack exchange，提问作者Matster2