CODEWITHSUNDEEP
c#sqllinqwhere-clause
user1017882
user1017882

Reputation:

Linq expression to select rows when no other row in the table matches a condition

I have a table with 3 columns:

Name varchar

Test1 bit

Test2 bit

Group varchar

Objective:

I want to select all rows where only test1 is true per each name per group.

To clarify, take these example records:

Name | Test1 | Test2 | Group
Foo    True    False   Group 1
Bar    True    False   Group 1
Bar    False   True    Group 2
Foo    False   True    Group 1
123    True    False   Group 1

The 2nd row will be selected as it has never tested true for Test2 for that same group with the same 'Name' value.

The 1st row will not be selected as it has, at some point in the table, tested true for Test2 with the same group name.

The final row will be selected as it has never tested true for Test2.

NOTE: I have the group name to explicitly search by.

What I've Tried

What I've tried (and it works) is to select all of the rows where test 1 is true. I've then done another search (iterating through the results from this first query) for each id and tested if it appears again. What I've done here though is a workaround, I understand there's a lot of 'where not exists' capabilities of Linq and I want to utilise them to improve the state of what I've written so far so hopefully I can roll it all up into one statement.

Code Example

var items = itemsTable.Where(o => o.Group.Equals(GroupName) && o.Test1);

Upvotes: 0

Views: 2242

Answers (2)

Petar Ivanov
Petar Ivanov

Reputation: 93010

var items = itemsTable.Where(o => !o.Test2 && 
    !itemsTable.Any(x => x.Name == o.Name && x.Group == o.Group && x.Test2));

Upvotes: 1

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

You can use the following:

var items = itemsTable.GroupBy(e => new { e.Name, e.Group }, e => e)
     .Where(g => !g.Any(e => e.Test2) &&  g.Key.Group.Equals(groupName))
     .SelectMany(e => e).ToList();

Upvotes: 1

Related Questions