LINQ to SQL -跨多个列的不同记录

本文关键字：记录 to SQL LINQ | 更新日期: 2023-09-27 18:17:17

我有一个查询关于我在LINQ写的查询，我希望有人能帮助。基本上我有以下(示例数据)

ProductCode  LanguageID   LegislationId   Version
1234         English       UK              1
1234         English       UK              2
5678         English       UK              1
9999         English       UK              1
9999         English       UK              2
9999         Spanish       Spain           1

所以，我需要做的是根据用户提供的条件返回记录，包括LanguageID和Description。这里的棘手之处在于，如果相同的产品代码、语言和法规有多个版本，那么我只想返回最新的版本。

所以…我的代码如下所示(立法包含一个命令分隔的立法列表):

List<string> legList = pLegislations.Split(',').ToList();
IEnumerable<MyClass> results = null;
results = (from a in context.ALLProducts
           let prodCode = a.ProductCode
           let prodDesc = a.ProductDescription
           where legList.Contains(a.LegislationID) &&
                 (a.LanguageID == pLanguage || pLanguage == null) &&
                  a.BrandName == pMarket &&
                 (prodCode.Contains(pSearch) || prodDesc.Contains(pSearch) || 
                                                pSearch == string.Empty) &&
                 prodCode[0] != 'x' &&
                ((pMarket == "testMarket") ? prodCode.StartsWith("0") : true)
                select new MyClass
                {
                   BrandName = a.BrandName,
                   BulkCode = prodCode,
                   BulkDescription = prodDesc,
                   Language = a.LanguageName,
                   LanguageCode = a.LanguageID
                }).ToList();

现在这一切都工作得很好，没有多个版本，所以我的问题是-我怎么能过滤它，所以只有最新的版本返回时，有一个匹配的产品代码，语言和立法，即，如果我是通过语言= '英语'和立法= '英国'那么我期望以下结果:

ProductCode  LanguageID   LegislationId   Version
1234         English       UK              2
5678         English       UK              1
9999         English       UK              2
9999         Spanish       Spain           1

Thanks in advance

安德鲁

LINQ to SQL -跨多个列的不同记录

将结果按ProductCode(1)分组，并从每组中选择Version值最大的项(2):

results = (from a in context.ALLProducts
           let prodCode = a.ProductCode
           let prodDesc = a.ProductDescription
           where legList.Contains(a.LegislationID) &&
                 (a.LanguageID == pLanguage || pLanguage == null) &&
                  a.BrandName == pMarket &&
                 (prodCode.Contains(pSearch) || 
                  prodDesc.Contains(pSearch) || 
                  pSearch == string.Empty) &&
                  prodCode[0] != 'x' &&
                 ((pMarket == "testMarket") ? prodCode.StartsWith("0") : true)
           group a by a.ProductCode into g // 1
           let lastProduct = g.OrderByDescending(x => x.Version).First() // 2
           select new MyClass
                {
                   BrandName = lastProduct.BrandName,
                   BulkCode = prodCode,
                   BulkDescription = prodDesc,
                   Language = lastProduct.LanguageName,
                   LanguageCode = lastProduct.LanguageID
                }).ToList();
相关文章：