LINQ to return distinct value by specific column

Question

I have a Colour table like this:

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

And I want to get all colours with VendorId as NULL, unless that colour Name has an entry with a VendorId attached, so e.g. for VendorId = 1 I'd like

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

Noting that the Purple row Id 1 with the NULL VendorId is not on the list. For Id = 2 I'd get the rows 1,2,3 and 6

I thought initially to .Select .Distinct on Name but I need the entire object or at least the Id's

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

but if I use .Select(x => x.Id).Distinct() on then I get two instances of Purple

How can I achieve this in LINQ?

Edit:

I've just tried using

        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();

Trying to get all null and id = 1, then order by descending Id and trying .GroupBy but I got Client side GroupBy is not supported.

jamheadart · Accepted Answer · 2020-04-22 08:02:59Z

1

Give this a go:

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();

With this sample data:

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

I get this result:

edited Apr 22, 2020 at 8:02

jamheadart

5,3737 gold badges37 silver badges87 bronze badges

answered Apr 22, 2020 at 3:14

Enigmativity

117k12 gold badges101 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jamheadart Over a year ago

Very interesting exception given for this one:

Processing of the LINQ expression 'xs => xs     .Take(1)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433

- I think I can add some ToList() at points to fix it... maybe...

jamheadart Over a year ago

Just for anyone else who suffers the same issue, I added .ToList() in the chain after the .OrderByDescending() otherwise it wouldn't work for me - my client-side couldn't handle the operation.

PiotrWolkowski · Accepted Answer · 2020-04-22 05:37:39Z

This one is working for me and generating expected results:

var idOrNameNotWithId = 
    colours.Where(x => x.VendorId == cid ||
    (!x.VendorId.HasValue && !colours.Where(x => x.VendorId == cid).Select(x => x.Name).ToList().Contains(x.Name)))
    .ToList();

The idea behind it: anything with expect cid (colour ID) or anything that's null but only if, when you take anything with ID the name of the colour is not there.

And the full working method. I borrowed the colour collection definition from @Enigmativity's answer.

static void Main(string[] args)
{
    Console.WriteLine("Hello World!");
    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 },
    };

    var cid = 2;

    var idOrNameNotWithId = 
        colours.Where(x => x.VendorId == cid ||
        (!x.VendorId.HasValue && !colours.Where(x => x.VendorId == cid).Select(x => x.Name).ToList().Contains(x.Name)))
        .ToList();
        }
    }
}

Collectives™ on Stack Overflow

LINQ to return distinct value by specific column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related