Geek asks and answers

Check if the value is in another table

I have two tables: “Person” and “Member”. A member is always a person, but a person is not always a member.

I want to get a list of all non-members.

I tried to do it like this, but no luck:

var NonMembers = PersonsDB.GetAll().Where(x => !Members.Contains(x));

How to solve this?

+3
source share
4 answers

You have a foreign key (e.g. PersonId) in your table Member(you need), and you can compare it with Id Personlike this:

var NonMembers = 
                PersonsDB.GetAll().Where(x => !Members.Any(y=>y.PersonId==x.Id));
+3
source 
var NonMembers = PersonsDb.GetAll().Except(Members);
+4
source

Except()?

var nonMembers = PersonsDB.GetAll().Except(Members);

.

,

  • PersonsDB.GetAll() IEnumerable<Person>
  • Members - IEnumerable<Member>
  • Member extends Person:

:

public class IsPersonMemberComparer : EqualityComparer<Person>
{
    public override bool Equals(Person x, Person y)
    {
        var maybeMember = x;
        var definitelyMember = (Member) y;

        // test whether x and y are the same person
        return maybeMember.SocialSecurityNumber == definitelyMember.SocialSecurityNumber;
    }

    public override int GetHashCode(Person obj)
    {
        return obj.GetHashCode();
    }
}

.

var comparer   = new IsPersonMemberComparer();
var nonMembers = PersonsDB.GetAll().Except(Members, comparer);
+3
source

If there is a navigation property (corresponding to the foreign key of the database), you can check it for null:

var NonMembers = PersonsDB.GetAll().Where(p => p.Member == null);
+1
source

More articles:


All Articles