CODEWITHSUNDEEP
c#mysqllinq
Max Boy
Max Boy

Reputation: 327

Using LINQ for JOINs and Sum

I just started working with LINQ. How can I use SUM and LEFT JOIN using LINQ. I am trying to build the query below in LINQ. Is it possible?

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

Thanks

Upvotes: 0

Views: 87

Answers (2)

jdweng
jdweng

Reputation: 34421

I used classes to model you database. See code below

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

Upvotes: 1

JosephRT
JosephRT

Reputation: 545

Roughing out an answer and making a few assumptions about your object model, I'd start off by calculating each of the sums individually with something akin to this statement:

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

After you've got all the sums, you can create another query that queries the Tenants and, assuming the Tenants object has it's associated Properties as a member, you could combine them in something like this:

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

You can include values beyond the object type that you're querying in a Select() method, so you can include the precalculated sums after determining them separately.

Upvotes: 1

Related Questions