Join customer and payment tables where count of items in payments that year equals 2021 is zero in C-CodePudding

I have two tables which are customers and payments.

Table Customers ==> Columns [CustomerId, ...other customer info].
Table Payments ==> Columns [PaymentId, CustomerId, Year, ...other payment info]

How can I get customers that haven't paid this year. I have no idea what I need.

import System.Linq;
var result = from customers in context.Customers 
join payments from context.Payments 
on customers.CustomerId equals payments.CustomerId
where payments.Year == 2021 into paymentsCount
where paymentsCount.count == 0;

CodePudding user response：

If you have proper navigation properties:

var query = context.Customers
    .Where(c => !c.Payments.Any(p => p.Year == 2021));

If there is no proper navigation property:

var query = context.Customers
    .Where(c => !context.Payments.Any(p => p.CustomerId == c.CustomerId && p.Year == 2021));