Linq group by + where for each group

Question

Hi,

I want to write a linq expression that will return the ID that does not contain a particular value. For example, I want to return all distinct IDs that do not have Value = 30.

ID, Value  
1, 10  
1, 20  
1, 30  
2, 10  
2, 20  
3, 10  
3, 20

The result should be 2 and 3 since non of these have a Value with 30.

Is this possible to do with a single expression?

Thanks

Answer 1

Sure, this will do it:

var query = from i in list
            group i by i.GroupId into g
            where g.Any(p => p.ItemId == 30) == false
            select g.Key;

foreach(var result in query) { Console.WriteLine(result); }

This outputs:

2
3

Here I have used, as an example:

class Product {
    public int GroupId { get; set; }
    public int ItemId { get; set; }
}

and

var list = new List<Product>() {
    new Product() {GroupId = 1, ItemId = 10},
    new Product() {GroupId = 1, ItemId = 20},
    new Product() {GroupId = 1, ItemId = 30},
    new Product() {GroupId = 2, ItemId = 10},
    new Product() {GroupId = 2, ItemId = 20},
    new Product() {GroupId = 3, ItemId = 10},
    new Product() {GroupId = 3, ItemId = 20},
};

Answer 2

I don't have Linq, but here is the SQL Server SQL to do what you want:

DECLARE @YourTable table (ID int, value int)

insert into @YourTable VALUES (1, 10)
insert into @YourTable VALUES (1, 20)
insert into @YourTable VALUES (1, 30)
insert into @YourTable VALUES (2, 10)
insert into @YourTable VALUES (2, 20)
insert into @YourTable VALUES (3, 10)
insert into @YourTable VALUES (3, 20)


SELECT DISTINCT ID
    FROM @YourTable y1
    WHERE NOT EXISTS (SELECT Value 
                      FROM @YourTable y2
                      WHERE y1.ID=y2.id and y2.value=30)

OUTPUT:

ID
-----------
2
3

(2 row(s) affected)