.net-corejoinentity-framework-corerelationship.net-core-2.0

Cannot understand this EF Core 2 relationships behaviour


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?


Solution

  • Your query is doing Inner Join, instead, you have to use Left Join. You can implement this by using DefaultIfEmpty.