c# - LINQ 按特定列返回不同的值

Question

我有一张Colour这样的桌子：

Id  Name    VendorId
--------------------
1   Purple  NULL
2   Blue    NULL
3   Black   NULL
4   Purple  1
5   Orange  1
6   Mauve   2

而且我想将所有颜色都VendorId设为NULL，除非该颜色Name有一个VendorId附加的条目，所以例如VendorId = 1我想要

Id  Name    VendorId
--------------------
2   Blue    NULL
3   Black   NULL
4   Purple  1
5   Orange  1

注意带有的Purple行 ID不在列表中。因为我会得到行和1NULL VendorIdId = 21,2,36

我最初想.Select .Distinct在 Name 上，但我需要整个对象或至少是 Id

        var result = _context.Colours
            .Where(x => x.Vendor == null || x.Vendor.Id == vendorId)
            .Select(x => x.Name)
            .Distinct()
            .ToList();

但是如果我使用.Select(x => x.Id).Distinct()on 那么我会得到两个实例Purple

如何在 LINQ 中实现这一点？

编辑：

我刚刚尝试使用

        var result = _context.Colours
            .Where(x => x.Vendor == null || x.Vendor.Id == vendorId)
            .OrderByDescending(x => x.Id)
            .GroupBy(x => x.Name)
            .Distinct()
            .ToList()
            .Select(x => x.First())
            .ToList();

试图得到所有nulland id = 1，然后通过降序 Id 排序并尝试.GroupBy但我得到了Client side GroupBy is not supported.

标签： c#entity-frameworklinq

Answer 1

试一试：

var vendorId = 1;
var result =
    _context
        .Colours
        .Where(x => x.VendorId == null || x.VendorId == vendorId)
        .OrderByDescending(x => x.VendorId)
        .GroupBy(x => x.Name)
        .SelectMany(xs => xs.Take(1))
        .OrderBy(x => x.ID)
        .ToList();

使用此示例数据：

var colours = new []
{
    new { ID = 1, Name = "Purple", VendorId = (int?)null },
    new { ID = 2, Name = "Blue", VendorId = (int?)null },
    new { ID = 3, Name = "Black", VendorId = (int?)null },
    new { ID = 4, Name = "Purple", VendorId = (int?)1 },
    new { ID = 5, Name = "Orange", VendorId = (int?)1 },
    new { ID = 6, Name = "Mauve", VendorId = (int?)2 },
};

我得到这个结果：