使用LINQ选择不同列的唯一值

Question

我有一个具有多列的表（例如ex的订单）。

products    categories  subcategories

--------------------------------------

prod1       cat1        sub1

prod1       cat2        sub2

prod2       cat3        sub6

prod1       cat1        sub1

prod5       cat2        sub8

prod2       cat1        sub1

prod1       cat7        sub3

prod8       cat2        sub2

prod2       cat3        sub1

现在，我可以编写三个不同的查询来获取不同的值

var prod = (from p in _context.orders select p.products).ToList().Distinct();

同样，我可以为他人编写。

现在，我需要在单个查询中获取每一列的不同值，其结果需要像

products    categories  subcategories

--------------------------------------

prod1       cat1        sub1

prod2       cat2        sub2

prod5       cat3        sub6

prod8       cat7        sub8

                        sub3

我的唯一字段的ClassType看起来像这样

public class UniqueProductFields

{

    public IEnumerable<string> Products { get; set; }

    public IEnumerable<string> Categories { get; set; }

    public IEnumerable<string> Subcategories { get; set; }

}   

不知道如何以有效的方式执行此操作，这样我就不必编写三种方法。该表位于数据库中（因此需要进行优化）

Answer 1

使用Linq是绝对不变的要求吗？为什么需要在单个查询中将其返回？建议：使用SQL。它可以在一个单一的查询做，但你不会喜欢的查询。我假设使用SQL Server（可以对其他DBMS进行不同的处理）。WITH V AS (&nbsp; &nbsp;SELECT DISTINCT&nbsp; &nbsp; &nbsp; V.*&nbsp; &nbsp;FROM&nbsp; &nbsp; &nbsp; Orders O&nbsp; &nbsp; &nbsp; CROSS APPLY (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;VALUES (1, O.Products), (2, O.Categories), (3, O.Subcategories)&nbsp; &nbsp; &nbsp; ) V (Which, Value)),Nums AS (&nbsp; &nbsp;SELECT&nbsp; &nbsp; &nbsp; Num = Row_Number() OVER (PARTITION BY V.Which ORDER BY V.Value),&nbsp; &nbsp; &nbsp; V.Which,&nbsp; &nbsp; &nbsp; V.Value&nbsp; &nbsp;FROM&nbsp; &nbsp; &nbsp; V)SELECT&nbsp; &nbsp;Products = P.[1],&nbsp; &nbsp;Categories = P.[2],&nbsp; &nbsp;Subcategories = P.[3]FROM&nbsp; &nbsp;Nums N&nbsp; &nbsp;PIVOT (Max(N.Value) FOR N.Which IN ([1], [2], [3])) P;看到此工作在db <> fiddle输出：Products&nbsp; Categories&nbsp; Subcategories--------&nbsp; ----------&nbsp; -------------prod1&nbsp; &nbsp; &nbsp;cat1&nbsp; &nbsp; &nbsp; &nbsp; sub1prod2&nbsp; &nbsp; &nbsp;cat2&nbsp; &nbsp; &nbsp; &nbsp; sub2prod5&nbsp; &nbsp; &nbsp;cat3&nbsp; &nbsp; &nbsp; &nbsp; sub3prod8&nbsp; &nbsp; &nbsp;cat7&nbsp; &nbsp; &nbsp; &nbsp; sub6null&nbsp; &nbsp; &nbsp; null&nbsp; &nbsp; &nbsp; &nbsp; sub8如果您被束缚并决心使用Linq，那么我将无法为您提供查询样式的语法。我只知道C＃代码样式的语法，但是这是一个刺脚。不幸的是，我认为这不会为您带来任何好处，因为我不得不使用一些非常时髦的东西来使其工作。它使用与上面的SQL查询基本相同的技术，只是，PIVOTLinq中没有等效的技术，除了自定义类之外，没有真正的自然行对象。using System;using System.Collections.Generic;using System.Linq;public class Program {&nbsp; &nbsp; public static void Main() {&nbsp; &nbsp; &nbsp; &nbsp; var data = new List<Order> {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod1", "cat1", "sub1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod1", "cat2", "sub2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod2", "cat3", "sub6"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod1", "cat1", "sub1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod5", "cat2", "sub8"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod2", "cat1", "sub1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod1", "cat7", "sub3"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod8", "cat2", "sub2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new Order("prod2", "cat3", "sub1")&nbsp; &nbsp; &nbsp; &nbsp; };&nbsp; &nbsp; &nbsp; &nbsp; int max = 0;&nbsp; &nbsp; &nbsp; &nbsp; var items = data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .SelectMany(o => new List<KeyValuePair<int, string>> {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new KeyValuePair<int, string>(1, o.Products),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new KeyValuePair<int, string>(2, o.Categories),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new KeyValuePair<int, string>(3, o.Subcategories)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Distinct()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .GroupBy(d => d.Key)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(g => {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var l = g.Select(d => d.Value).ToList();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; max = Math.Max(max, l.Count);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return l;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList();&nbsp; &nbsp; &nbsp; &nbsp; Enumerable&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Range(0, max)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(i => new {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; p = items[0].ItemAtOrDefault(i, null),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; c = items[1].ItemAtOrDefault(i, null),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; s = items[2].ItemAtOrDefault(i, null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ForEach(row => Console.WriteLine($"p: {row.p}, c: {row.c}, s: {row.s}"));&nbsp; &nbsp; }}public static class ListExtensions {&nbsp; &nbsp; public static T ItemAtOrDefault<T>(this List<T> list, int index, T defaultValue)&nbsp; &nbsp; &nbsp; &nbsp; => index >= list.Count ? defaultValue : list[index];}public class Order {&nbsp; &nbsp; public Order(string products, string categories, string subcategories) {&nbsp; &nbsp; &nbsp; &nbsp; Products = products;&nbsp; &nbsp; &nbsp; &nbsp; Categories = categories;&nbsp; &nbsp; &nbsp; &nbsp; Subcategories = subcategories;&nbsp; &nbsp; }&nbsp; &nbsp; public string Products { get; set; }&nbsp; &nbsp; public string Categories { get; set; }&nbsp; &nbsp; public string Subcategories { get; set; }}我想我们可以交换一下.Select(i => new {&nbsp; &nbsp;p = items[0].ItemAtOrDefault(i, null),&nbsp; &nbsp;c = items[1].ItemAtOrDefault(i, null),&nbsp; &nbsp;s = items[2].ItemAtOrDefault(i, null)})为了这：.Select(i => new Order(&nbsp; &nbsp;items[0].ItemAtOrDefault(i, null),&nbsp; &nbsp;items[1].ItemAtOrDefault(i, null),&nbsp; &nbsp;items[2].ItemAtOrDefault(i, null)))然后在输出部分中使用该类的属性。