CODEWITHSUNDEEP
c#linqgroup-by
user320587
user320587

Reputation: 1367

Need help on Linq with group by and distinct

I am trying to perform a group by followed by a distinct to verify if a set of columns maps to only other column. For example, in the dataset below

Brand   Product      Location      Customer      Demand
 Box       A         Chicago       Chicago        10
 Box       B         Chicago       Milwaukee      20
 Cart      C         Madison       Milwaukee      10
 Cart      D         Chicago       Milwaukee      15

Product A, B, C are valid. But D is not valid since there exists a Product B with Chicago as Location & Milwaukee as Customer. I am trying to build a LINQ query to get the exception record and having some trouble. I am very sure I have overcomplicated my query.

var vDuplicateSupplierLitho = from p in vRecords
                              group p by new
                              {
                                  Location = p["Location"].Cast<string>(),
                                  Customer = p["Customer"].Cast<string>()
                              } into grp
                              select new
                              {
                                  Location = grp.Key.Location,
                                  Customer = grp.Key.Customer,
                                  Products = from a in grp
                                             group a by new { Product = a["Product"].Cast<string>() } into apngrp
                                             where apngrp.Count() > 1 && apngrp.Select(a => a["Product"]).Distinct().Count() > 1
                                                  from NonUniqueRecord in apngrp
                                                  select new
                                                  {
                                                      Product = apngrp.key.Product,
                                                      Location = g.key.Location,
                                                      Customer = g.key.Customer
                                                      Demand = NonUniqueRecord["Demand"]
                                                  }
                                              };

Thanks.

Upvotes: 0

Views: 3556

Answers (1)

Av Pinzur
Av Pinzur

Reputation: 2228

new[] {
        new { Brand = "Box", Product = "A", Location = "Chicago", Customer = "Chicago", Demand = 10 },
        new { Brand = "Box", Product = "B", Location = "Chicago", Customer = "Milwaukee", Demand = 20 },
        new { Brand = "Cart", Product = "C", Location = "Madison", Customer = "Milwaukee", Demand = 10 },
        new { Brand = "Cart", Product = "D", Location = "Chicago", Customer = "Milwaukee", Demand = 15 },
        new { Brand = "Cart", Product = "C", Location = "Madison", Customer = "Milwaukee", Demand = 10 }
        }
    .GroupBy(o => new { o.Location, o.Customer })
    .Where(g => g.Select(o => o.Product).Distinct().Count() > 1)
    .SelectMany(g => g)

... or, in query syntax ...

from record in new[] {
        new { Brand = "Box", Product = "A", Location = "Chicago", Customer = "Chicago", Demand = 10 },
        new { Brand = "Box", Product = "B", Location = "Chicago", Customer = "Milwaukee", Demand = 20 },
        new { Brand = "Cart", Product = "C", Location = "Madison", Customer = "Milwaukee", Demand = 10 },
        new { Brand = "Cart", Product = "D", Location = "Chicago", Customer = "Milwaukee", Demand = 15 },
        new { Brand = "Cart", Product = "C", Location = "Madison", Customer = "Milwaukee", Demand = 10 }
        }
group record by new { record.Location, record.Customer } into grouping
where (from o in grouping select o.Product).Distinct().Count() > 1
from duplicate in grouping
select duplicate

Upvotes: 5

Related Questions