Maurits van Beusekom Maurits van Beusekom - 11 days ago 7
C# Question

EF.Core load all records that don't have a related record in other table

Is it possible in Entity Framework Core (or 7), to load all records from a table that don't have a related record in the other table?

In my case I have a Customers table and an Contracts table. Customers can have 0 to N contracts. In this particular use case I would like to query all Customers that don't have a contract.

**Customer Table:**

CustomerId | Name
---------- | ----------
1 | Apple
2 | Google
3 | Microsoft

**Contracts Table:**

| ContractId | CustomerId | StartDate | EndDate |
| ---------: | ---------: | ---------- | ---------- |
| 1 | 2 | 01-01-2016 | 01-01-2018 |
| 2 | 3 | 01-01-2016 | 01-01-2018 |


In this case I would like the query to return only one object containing the Apple customer.

In SQL I would do something like this:

select cust.CustomerId, cust.Name
from dbo.Customers as cust
left outer join dbo.Contracts as contr
on cust.CustomerId = contr.CustomerId
where contr.ContractId is null;


How can I translate this to a EF query?

Answer

You just need a linq query to achieve what you want like this (independent of the EF version):

var result = yourContext
    .Customers
    .Where(x => !yourContext.Contracts.Any(y => x.Id == y.CustomerId));

Of course I assumed that in your customer entity CustomerId is called Id.