barjed
barjed

Reputation: 387

How to select and exclude records from the same subset in LINQ

I have a sample connection table PolicyToX with fields Id, PolicyId, PersonId, SchoolId. Records are always saved with one of the FKs being NULL, for example 1, 1, 5, NULL.

I want to write a query in LINQ that, when given two parameters: PersonId and SchoolId will filter all Policies of the given School but without those that are already bound to a given Person.

So, if I have a dataset of:

[Id][PolicyId][PersonId][SchoolId]
 1     1         5        NULL
 2     1        NULL       1
 3     2        NULL       1

and pass paremeters PersonId = 5 and SchoolId = 1 the result should be one Policy of ID = 2.

Thanks!

Upvotes: 1

Views: 2107

Answers (2)

StevieB
StevieB

Reputation: 1000

Assuming PolicyToX contains the data then is this what you're looking for?

        var ids = from e in PolicyToX where e.PersonId == personId select e.PolicyId;
        var result = from d in PolicyToX where d.SchoolId == schoolId && !ids.Contains(d.PolicyId) select d;

Upvotes: 1

Dmytro Rudenko
Dmytro Rudenko

Reputation: 2524

var data = list.Where(x => x.SchoolId == schoolId && x.PersonId != personId);

Are you talking about this?

Upvotes: 0

Related Questions