需要帮助Linq与群体和独特 [英] 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

产品A，B，C有效。但是D是无效的，因为存在与芝加哥一样的产品B作为位置&密尔沃基作为客户。
我正尝试构建一个LINQ查询来获取异常记录并遇到一些麻烦。

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.

推荐答案

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

这篇关于需要帮助Linq与群体和独特的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！