CODEWITHSUNDEEP
c#linq
Andrew
Andrew

Reputation: 2335

LINQ to SQL - Distinct records across multiple columns

I've got a query regarding a query I am writing in LINQ which I hope someone can help with. Basically I have the following (example data)

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

So, what I need to do is return records based on criteria provided by the user, including the LanguageID and Description. The tricky part here is that where there are multiple versions of the same product code, language and legislation then I only want to return the latest version.

So...my code as it stands is as follows (pLegislations contains a comman separated list of legislations):

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();

Now this all works fine where there are not multiple versions, so my question is - how can I filter it so only the latest version is returned when there is a match on ProductCode, Language and Legislation, i.e. if I was to pass Language = 'English' and Legislation = 'UK' then I'd expect the following results:

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

Thanks in advance

Andrew

Upvotes: 1

Views: 1480

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

Group results by ProductCode (1) and select from each group item with max Version value (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();

Upvotes: 1

Related Questions