Count records not referenced in other table (one-to-many table issue with EF & LINQ)-CodePudding

How can I count all TimeLog records that is not Invoiced (see tables below), but should be invoiced (isBillable), with Entity Framework and Linq?

dbo.TimeLogs

[Table("TimeLogs")]
public class TimeLogEntity
{
  int Id {get; set;}
  int IsBillable {get; set;}
  ... 
  List<InvoiceEntity> Invoices {get; set;}
}

dbo.Invoices

[Table("Invoices")]
public class InvoiceEntity
{
  int Id {get; set;}
  int TimeLogId {get; set;}
  bool IsActive {get; set;}
  ... 
   TimeLogEntity TimeLog {get; set;}     
}

I tried this but without success:

var numRows = await applicationDbContext.TimeLogs
   .Include(t => t.Invoices.Where(i => i.IsActive == true))
   .Where(t => t.IsBillable == true && t.IsActive == true && t.Invoices.Count == 0)
   .CountAsync();

Above query results in this SQL-query (se below), which don't check if the invoice records is active or not. I don't wont to count Invoices that have IsActive = false (they are regarded as deleted).

SELECT COUNT(*)
FROM [TimeLogs] AS [t]
WHERE (([t].[IsBillable] = CAST(1 AS bit)) AND ([t].[IsActive] = CAST(1 AS bit))) AND ((
   SELECT COUNT(*)
   FROM [Invoices] AS [i]
   WHERE [t].[Id] = [i].[TimeLogId]) = 0)

I am using Microsoft.AspNetCore.Identity.EntityFrameworkCore (6.0.1)

CodePudding user response：

This one should work. Note that Include is introduced just for loading related data, not filtering main records.

var numRows = await applicationDbContext.TimeLogs
   .Where(t => t.IsBillable == true && t.IsActive == true 
        && !t.Invoices.Where(i => i.IsActive == true).Any())
   .CountAsync();