Linq group +, where for each group
I want to write a linq expression that will return an id that does not contain a specific value. For example, I want to return all individual IDs that have no 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 as they don't have a value with 30.
Can this be done with a single expression?
thank
+2
source to share
2 answers
Of course 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); }
Output:
2 3
Here I 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},
};
+3
source to share
I don't have Linq, but here's SQL Server SQL does 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)
0
source to share