c# - Check if all database entries are linked in pairs -

i have database table of accounts.

each account has (amongst other fields) id , linkedid.

the linkedid used store id of account linked.

i want check accounts table pick out invalidly linked accounts.

an account invalid if:

1. its linkedid zero;

2. its linkedid equals own id (i.e. it's linked itself);

3. its linkedid not id of account in accounts table;

4. its linkedid id of account, account's linkedid not id of first account (so if #3 links #564, #564 should link #3).

how go doing without dragging in accounts database?

i using c#, asp.net mvc , entity frameworks.

this got to:

list<account> accounts = db.accounts     .where(x => x.linkedid == 0 || x.linkedid == x.id || ????????)     .tolist() 

use simple linq statement:

var accountswithinvalidlinks =      in db.accounts     let linkedaccount = db.accounts.firstordefault(a2 => a2.id == a.linkedid)     a.linkedid == 0 ||         a.linkedid == a.id ||         linkedaccount == null ||          linkedaccount.linkedid != a.id     select a; 

here's alternative if you're not fan of query expression syntax. avoid repetition, i've combined 3rd , 4th requirements together, make more efficient query:

var accountswithinvalidlinks =      db.accounts.where(a =>          a.linkedid == 0 ||         a.linkedid == a.id ||         !db.accounts.any(a2 => a2.id == a.linkedid && a2.linkedid == a.id));