如何使用 Entity Framework 和 Linq计算所有未开票但应开票 (isBillable) 的 TimeLog 记录？

dbo.TimeLogs

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

dbo.发票

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

我试过这个但没有成功：

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

上面的查询导致这个 SQL 查询（见下文），它不检查发票记录是否处于活动状态。我不会计算 IsActive = false 的发票（它们被视为已洗掉）。

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)

我正在使用 Microsoft.AspNetCore.Identity.EntityFrameworkCore (6.0.1)

uj5u.com热心网友回复：

这个应该可以的。请注意，Include仅用于加载相关资料，而不是过滤主要记录。

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