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 1与 NULL VendorId不在名单上。对于 Id = 2我会得到行 1,2,3和 6
我最初以为 .Select .Distinct在名称上，但我需要整个对象或至少是 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()然后我得到两个 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();

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

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 },
};

我得到这个结果: