使用linq进行连接和求和

gj3fmq9x  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(305)

我刚开始和linq合作。如何使用sum和left join使用linq。我正在尝试用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

谢谢

lymgl2op

lymgl2op1#

我用类来模拟你的数据库。参见下面的代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication53
{
    class Program
    {

        static void Main(string[] args)
        {
            List<Tenants> tenants = new List<Tenants>();
            List<Properties> properties = new List<Properties>();
            List<TenantTransactions> transactions = new List<TenantTransactions>();

            var tenantTransactions = transactions.GroupBy(x => x.TenantID).Select(x => new
            {
                id = x.Key,
                totalDebit = x.Where(y => (y.TransactionTypeID == 1) && (y.ChargeTypeID != 6) && (y.TenantTransactionDate <= DateTime.Now)).Sum(y => y.TransactionAmount),
                housingDebit = x.Where(y => (y.TransactionTypeID == 1) && (y.ChargeTypeID == 6) && (y.TenantTransactionDate <= DateTime.Now)).Sum(y => y.TransactionAmount),
                totalCredit = x.Where(y => (y.TransactionTypeID == 2) && (y.ChargeTypeID != 6) && (y.TenantTransactionDate <= DateTime.Now)).Sum(y => y.TransactionAmount),
                housingCredit = x.Where(y => (y.TransactionTypeID == 2) && (y.ChargeTypeID == 6) && (y.TenantTransactionDate <= DateTime.Now)).Sum(y => y.TransactionAmount)
            }).ToList();

            var results2 = (from t in tenants
                           join p in properties on t.PropertyID equals p.PropertyID
                           join tt in tenantTransactions on t.TenantID equals tt.id into ps
                           from tt in ps.DefaultIfEmpty()
                           select new { t = t, p = p, tt = tt })
              .Where(x => (x.t.PropertyID == 1) && (x.t.Prospect == 1))
              .GroupBy(x => x.t.TenantID)
              .Select(x => new {
                  tenantID = x.Key,
                  tenantFirstName = x.FirstOrDefault().t.TenantFName,
                  tenantLastName = x.FirstOrDefault().t.TenantLName,
                  tenantEmail = x.FirstOrDefault().t.TenantEmail,
                  tenantPhone = x.FirstOrDefault().t.TenantPhone,
                  tenantCellPhoneProvider = x.FirstOrDefault().t.CellPhoneProviderID,
                  properties = x.Select(y => new {
                     propertyID = y.p.PropertyID,
                     propertyName = y.p.PropertyName,
                     rentalAmount = y.t.RentalAmount,
                     petRent = y.t.PetRent,
                     houseingAmount = y.t.HousingAmount,
                     utilityCharge = y.t.UtilityCharge,
                     tvCharge = y.t.TVCharge,
                     sercurityDeposit = y.t.SecurityDeposit,
                     hoaFee = y.t.HousingAmount,
                     parkingCharge = y.t.ParkingCharge,
                     storageCharge = y.t.StorageCharge,
                     concessionAmount = y.t.ConcessionAmount,
                     concessionReason = y.t.ConcessionReason,
                     tenantMoveInDate =  y.t.MoveInDate
                  }).ToList(),
                  totalDebit = x.FirstOrDefault().tt.totalDebit,
                  housingDebit = x.FirstOrDefault().tt.housingDebit,
                  totalCredit = x.FirstOrDefault().tt.totalCredit,
                  housingCredit = x.FirstOrDefault().tt.housingCredit,
              }).ToList();
        }

    }
    public class TenantTransactions
    {
        public int TenantID { get; set; }
        public int TransactionTypeID{ get;set;}
        public int ChargeTypeID { get;set;}
        public DateTime TenantTransactionDate { get;set;}
        public decimal TransactionAmount { get;set;}
    }
    public class Tenants
    {
        public int PropertyID { get; set; }
        public int Prospect { get; set; }
        public int TenantID { get; set; }
        public string TenantFName { get; set; }
        public string TenantLName { get; set; }
        public decimal RentalAmount { get; set; }
        public decimal PetRent { get; set; }
        public decimal HousingAmount { get; set; }
        public decimal UtilityCharge { get; set; }
        public decimal TVCharge { get; set; }
        public decimal SecurityDeposit { get; set; }
        public decimal HOAFee { get; set; }
        public decimal ParkingCharge { get; set; }
        public decimal StorageCharge { get; set; }
        public decimal ConcessionAmount { get; set; }
        public string ConcessionReason { get; set; }
        public string TenantEmail { get; set; }
        public string TenantPhone { get; set; }
        public string CellPhoneProviderID { get; set; }
        public DateTime MoveInDate { get; set; }

    }
    public class Properties
    {
        public int PropertyID { get; set; }
        public string PropertyName { get; set; }
    }
}
vs3odd8k

vs3odd8k2#

粗略地给出一个答案,并对你的对象模型做一些假设,我将首先用类似于以下语句的东西分别计算每一个和: 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() 方法,因此可以在分别确定预计算的和之后包含它们。

相关问题