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

Question

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;

https://stackoverflow.com/questions/9171063/convert-sql-to-linq-left-join-with-null — Ctznkane525, Sep 02 '21 at 20:55
Does your answer need to be in the SQL-like syntax, or can it use method-based syntax? — Ann L., Sep 02 '21 at 21:18

score 2 · Accepted Answer · answered Sep 03 '21 at 06:41

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));

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

1 Answers1