Linq Query - Multiple Joins to one table

Question

Account:
Id|Contact1|Contact2

Contact:
Id|status

I need a LINQ query that returns all of the accounts where contact1 and contact2 do not have a certain status.

var query = from a in accounts
            join c1 in contact on a.contact1 equals c1.id
            join c2 in contact on a.contact2 equals c2.id
            where c1.status != 1 && c2.status != 1
            select new {a.id}

I'm struggling with the logic. Clearly this isn't going to work, and doesn't. Just not sure how to join two tables on one in linq.

EDIT: I discovered my issue is in most cases contact2 is null. I need some sort of conditional join that only occurs if a.contact2 is not null.

To clarify, the logic I'm attempting to achieve is: Retrieve all accounts where contact1 status does not equal 1. If contact1 == 1, check if contact2 == 1. If contact2 != 1 retrieve the account.

Running into issues because contact2 on the account is not always populated. When it's null, nothing is retrieved by my original query.

Possible duplicate of [How to perform Join between multiple tables in LINQ lambda](https://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda) — RH6, Oct 18 '17 at 14:13
@RH6 Difference is this is multiple of the same table. Not different ones. — ZaphodBeeblbrox, Oct 18 '17 at 14:20
@arslanaybars There is no error, the query is just not correct. Returns no results, when there definitely should be. But it does build with it. — ZaphodBeeblbrox, Oct 18 '17 at 14:21
@arslanaybars Difficult because the objects are very large. But thanks to your comment I realize now the query is not the problem. It works correctly, but in some cases contact2 is null. Which then returns nothing. So I need a conditional join. Thank you. — ZaphodBeeblbrox, Oct 18 '17 at 14:38
What do you mean conditional join this dose a left join? It should work fine what error are you getting are you doing this in memory or with EF or Nhibrenate? — Filip Cordas, Oct 18 '17 at 14:48
@ZaphodBeeblbrox Ok there are four type of joins this is true in [SQL](https://www.w3schools.com/sql/sql_join.asp) and LINQ. If you mean a right join you can just google who to do a right join in linq. You might still run into some problems regarding IQueriable but that is another issue. — Filip Cordas, Oct 18 '17 at 15:00

score 0 · Accepted Answer · answered Oct 18 '17 at 14:59

I think I've understood your logic correctly...

As long as either contact1 or contact2 on an account have a status other than 1 then retrieve the account. Only accounts where both contacts have a status of 1 are not returned. In the event that either contact is null it is ignored as you can't check the status.

var query = from a in accounts
            from c in contacts
            where c.Id == a.Contact1 || c.Id == a.Contact2
            where c.Status != 1
            select new { a.Id };

Linq Query - Multiple Joins to one table

1 Answers1