c# - 使用 LINQ 进行 JOIN 和 Sum

Question

我刚刚开始使用 LINQ。如何使用 LINQ 使用 SUM 和 LEFT JOIN。我正在尝试在 LINQ 中构建下面的查询。可能吗？

SELECT t.TenantID, t.TenantFName, t.TenantLName, t.RentalAmount, t.PetRent, t.HousingAmount, t.UtilityCharge, t.TVCharge, t.SecurityDeposit, t.HOAFee,
   t.ParkingCharge, t.StorageCharge, t.ConcessionAmount, t.ConcessionReason, t.TenantEmail, t.TenantPhone, t.CellPhoneProviderID, t.MoveInDate,
   p.PropertyID, p.PropertyName,
   TotalDebit, HousingDebit, TotalCredit, HousingCredit
  FROM Tenants t
  JOIN Properties p ON t.PropertyID = p.PropertyID
  LEFT JOIN (
        Select
          TenantID,
          SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 AND TenantTransactionDate <= Now() THEN TransactionAmount ELSE 0 END) AS TotalDebit,
          SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 AND TenantTransactionDate <= Now() THEN TransactionAmount ELSE 0 END) AS HousingDebit,
          SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 AND TenantTransactionDate <= Now() THEN TransactionAmount ELSE 0 END) AS TotalCredit,
          SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 AND TenantTransactionDate <= Now() THEN TransactionAmount ELSE 0 END) AS HousingCredit
        From TenantTransactions
       Group By TenantID
       ) sums ON sums.TenantID = t.TenantID
 Where t.Prospect = 2
   AND t.PropertyID = 1

谢谢

Answer 1

粗略地得出答案并对您的对象模型做出一些假设，我首先使用类似于此语句的内容单独计算每个总和:

var tenantsTotalDebit = tenantTransactions.Where(tt.TenantId == requestedTenantId && tt.TransactionTypeID == 1 && tt.ChargeTypeID != 6 && tt.TenantTransactionDate <= DateTime.Now).Select(tt => tt.TransactionAmount).Sum();


获得所有总和后，您可以创建另一个查询来查询 Tenants并且，假设Tenants对象有关联 Properties作为成员，您可以将它们组合成这样:

var tenantQuery = tenants.Where(t.Prospect == 1 && t.PropertyID ==1).Select(t.TenantID, t.TenantFName, ..., tenantsTotalDebit, tenantsHousingDebit, tenantsTotalCredit, tenantsHousingCredit);


您可以在Select()中包含超出您正在查询的对象类型的值。方法，因此您可以在单独确定后将预先计算的总和包含在内。