score:3

Accepted answer

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

score:0

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)

Related Articles