CODEWITHSUNDEEP
c#entity-frameworklinq
jamheadart
jamheadart

Reputation: 5293

LINQ to return distinct value by specific column

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.

Upvotes: 2

Views: 85

Answers (2)

Enigmativity
Enigmativity

Reputation: 117010

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:

result

Upvotes: 1

PiotrWolkowski
PiotrWolkowski

Reputation: 8782

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

Upvotes: 1

Related Questions