Select rows where child rows do not exist in LINQ

Question

I have the following tables

Clubs - Id - Name Members - Id - ClubId linked to Clubs.Id - Name 

How can I write a query in LINQ that will give me a list of clubs with no members?

PS: What should be the correct terminology? Participants table - a table from the "Clubs". (not parent and child, but?)

Answer 1

How can I write a query in LINQ that will give me a list of clubs that have no members?

You can do it:

 from m in db.Members where !db.Clubs.Any( c => (c.Id == m.ClubId)) select m; 

For your second question:

Should the correct terminology be here? The table of elements is a * Table for clubs. (not parent and child, but?)

No, it is not. This is not a parent-child relationship, because a member can exist without a club. The connection between Members and Clubs in your case is what they invoke in UML, Aggregation . But the parent and child relationship or what they called Composition , i.e. Related , a child cannot exist without a parent. And that is none of your business.

Answer 2

This gives you a list of memberless clubs:

 from c in db.Clubs where !db.Members.Any( m => m.ClubId == c.Id) select c; 

But I suspect that the right way to do this would be to have many, many relationships with the staging table. (Can your data be an exception?) If you belong to 3 Clubs, will you have your entry in the membership table 3 times? You should if ClubId is in the Members table. Change of address should be done for 3 entries. This is not the right way.

Therefore, removing ClubId from the Members table and adding the Members_Clubs table using only MemberId and ClubId will allow you to associate one member with many clubs.

Then the statement will look like this:

 from c in db.Clubs where !db.Members_Clubs.Any( mc => mc.ClubId == c.Id) select c; 

In this, none of them is “parent” or “child”. This makes them associative objects.