Working with EF core 2 code first, I've 4 entities:
The customer has a collection of Contacts and Addresses. The customer has one PaymenTerm and PaymentTerm has a collection of Customers (not necessary because I don't want search by PaymentTerm).
I'want to retrieve all customers with Contacts and Addresses and his PaymentTerm:
var customers = await _context.Customers
.Include(c => c.Contacts)
.Include(c => c.Addresses)
.Include(c => c.PaymentTerm)
.ToListAsync();
These are dbcontext configurations: Customer:
x.HasMany(e => e.Contacts)
.WithOne(e => e.Customer)
.OnDelete(DeleteBehavior.Cascade);
x.HasMany(e => e.Addresses)
.WithOne(e => e.Customer)
.OnDelete(DeleteBehavior.Cascade);
PaymentTerm:
x.HasMany(e => e.Customers)
.WithOne(e => e.PaymentTerm)
.OnDelete(DeleteBehavior.Restrict);
PROBLEM: The query does not return any records. This is the SQL query generated:
SELECT [c].[CustomerId], [c].[Cif], [c].[CommercialName], [c].[Created], [c].[CreatedBy], [c].[CustomerName], [c].[Deleted], [c].[DeletedBy], [c].[DeletedDate], [c].[Modified], [c].[ModifiedBy], [c].[PaymentTermId], [c].[TaxFree], [c].[Vat], [t].[PaymentTermId], [t].[Created], [t].[CreatedBy], [t].[Days], [t].[Deleted], [t].[DeletedBy], [t].[DeletedDate], [t].[Modified], [t].[ModifiedBy], [t].[PaymentTermName]
FROM [Customers] AS [c]
INNER JOIN (
SELECT [p].[PaymentTermId], [p].[Created], [p].[CreatedBy], [p].[Days], [p].[Deleted], [p].[DeletedBy], [p].[DeletedDate], [p].[Modified], [p].[ModifiedBy], [p].[PaymentTermName]
FROM [PaymentTerms] AS [p]
WHERE [p].[Deleted] = 0
) AS [t] ON [c].[PaymentTermId] = [t].[PaymentTermId]
WHERE [c].[Deleted] = 0
ORDER BY [c].[CustomerId]
No join added with Contacts and Addresses and not records returned.
If I remove .Include(c => c.PaymentTerm)
, Customer with Contacts and Addresses is returned fine.
Please, could you explain to me what I'm doing wrong?
Your query is doing Inner Join, instead, you have to use Left Join. You can implement this by using DefaultIfEmpty
.