c# - 这个关于查找丢失的键的查询可以改进吗？ (SQL 或 LINQ)

Question

我正在开发一个 ASP.NET MVC 网站，并且正在寻找改进此例程的方法。它可以在 LINQ 级别或 SQL Server 级别进行改进。我希望最多我们可以在一次查询调用中完成。

以下是涉及的表格和一些示例数据:

我们没有限制每个 Key 必须有每个 LanguageId 值，事实上业务逻辑不允许这样的限制。但是，在应用程序级别，我们想警告管理员某个键缺少某个/某些语言值。所以我有这个类和查询:

public class LocalizationKeyWithMissingCodes
{
    public string Key { get; set; }
    public IEnumerable<string> MissingCodes { get; set; }
}

此方法获取键列表，以及任何缺失的代码(例如，如果我们有 en + jp + ch 语言代码，并且键只有 en + ch 的值，则列表将包含 jp):

    public IEnumerable<LocalizationKeyWithMissingCodes> GetAllKeysWithMissingCodes()
    {
        var languageList = Utils.ResolveDependency<ILanguageRepository>().GetActive();
        var languageIdList = languageList.Select(q => q.Id);
        var languageIdDictionary = languageList.ToDictionary(q => q.Id);

        var keyList = this.GetActive()
            .Select(q => q.Key)
            .Distinct();

        var result = new List<LocalizationKeyWithMissingCodes>();
        foreach (var key in keyList)
        {
            // Get missing codes
            var existingCodes = this.Get(q => q.Active && q.Key == key)
                .Select(q => q.LanguageId);

            // ToList to make sure it is processed at application
            var missingLangId = languageList.Where(q => !existingCodes.Contains(q.Id))
                .ToList();
            result.Add(new LocalizationKeyWithMissingCodes()
            {
                Key = key,
                MissingCodes = missingLangId
                    .Select(q => languageIdDictionary[q.Id].Code),
            });
        }

        result = result.OrderByDescending(q => q.MissingCodes.Count() > 0)
            .ThenBy(q => q.Key)
            .ToList();

        return result;
    }

我认为我目前的解决方案不好，因为它对每个键进行查询调用。有没有办法通过提高速度或在一次查询调用中打包来改进它？

编辑:这是答案的最终查询:

    public IQueryable<LocalizationKeyWithMissingCodes> GetAllKeysWithMissingCodes()
    {
        var languageList = Utils.ResolveDependency<ILanguageRepository>().GetActive();
        var localizationList = this.GetActive();

        return localizationList
            .GroupBy(q => q.Key, (key, items) => new LocalizationKeyWithMissingCodes()
            {
                Key = key,
                MissingCodes = languageList
                    .GroupJoin(
                        items,
                        lang => lang.Id,
                        loc => loc.LanguageId,
                        (lang, loc) => loc.Any() ? null : lang)
                    .Where(q => q != null)
                    .Select(q => q.Code)
            }).OrderByDescending(q => q.MissingCodes.Count() > 0) // Show the missing keys on the top
            .ThenBy(q => q.Key);
    }

Answer 1

另一种可能性，使用 LINQ:

public IEnumerable<LocalizationKeyWithMissingCodes> GetAllKeysWithMissingCodes(
    List<Language> languages,
    List<Localization> localizations)
{
    return localizations
        .GroupBy(x => x.Key, (key, items) => new LocalizationKeyWithMissingCodes
        {
            Key = key,
            MissingCodes = languages
                .GroupJoin( // check if there is one or more match for each language
                    items,
                    x => x.Id,
                    y => y.LanguageId,
                    (x, ys) => ys.Any() ? null : x)
                .Where(x => x != null) // eliminate all languages with a match
                .Select(x => x.Code) // grab the code
        })
        .Where(x => x.MissingCodes.Any()); // eliminate all complete keys 
}