LINQ: join multiple Table Column using Linq and find aggregated sum from child table values

Question

I'm using Linq (code-first approach) query to retrieve the data from DB and couldn't able to get similar result while using below linq query:

from msi in db.MainSaleInvoiceTbls
join dsi in db.DetialSaleInvoiceTbls on msi.Id equals dsi.MainSaleInvoiceId
join ca in db.CustomerAccounts on msi.CustomerId equals ca.Id
join cg in db.MiscLists on ca.CustomerGroupId equals cg.Id 
where msi.IsActive == true && msi.CompanyId == UniversalInfo.UserCompany.Id && msi.MainSaleInvoiceDataType == MainSaleInvoiceType.SOInvoice
group msi by new { dsi,msi.Date,msi.FinancialVoucher,msi.SaleOrderPrefix,msi.SaleOrderNumber,msi.SalesId,ca.CustomerName,ca.AccountCode,cg.Name }
into mainSaleInvoice
from dx in mainSaleInvoice.DefaultIfEmpty()
// orderby main.Id
select new
{
     Date = mainSaleInvoice.Key.Date,
     Voucher = mainSaleInvoice.Key.FinancialVoucher,
     InvoiceAccount = mainSaleInvoice.Key.AccountCode,
     CustomerName = mainSaleInvoice.Key.CustomerName,
     CustomerGroup = mainSaleInvoice.Key.Name,
     Invoice = mainSaleInvoice.Key.SaleOrderPrefix + mainSaleInvoice.Key.SaleOrderNumber,
     PurchaseOrder = mainSaleInvoice.Key.SalesId,
     SalesTax = "",
     InvoiceAmount = mainSaleInvoice.Sum(x => (Double)(mainSaleInvoice.Key.Quantity * mainSaleInvoice.Key.UnitPrice))
     }).ToList()

In linq, i need to get shortdatetimeString() and sum() of (unitprice* quantity) from child table DetialSaleInvoiceTbls being new in query writing, I don't know where and what I'm doing wrong. Any suggestions would be much appreciated.

@nick-berardi followed your reference but couldn't achieved desired result. — Osama Rizwan, Jan 10 '20 at 05:56
In the group by you group by dsi. I think this should be at least one or maybe several Property/Properties of dsi. — Nikolaus, Jan 10 '20 at 05:58
I'm following [link](https://stackoverflow.com/questions/530925/linq-using-inner-join-group-and-sum), I've removed dsi from group by but then can't access it in selection sum .... — Osama Rizwan, Jan 10 '20 at 06:03
you have to add all Properties of dsi to Group by, that are Not used in Sum. — Nikolaus, Jan 10 '20 at 06:10
but how to get those property in sum (unitprice*qty) [according to this link](https://stackoverflow.com/questions/530925/linq-using-inner-join-group-and-sum) but [getting this highlighted output which is wrong](https://pasteboard.co/IPhgdXG.png) — Osama Rizwan, Jan 10 '20 at 06:14
Both are called Linq query, they are just different in how you express the query. The former uses method-syntax whereas the latter uses sql-like syntax. — Hopeless, Jan 10 '20 at 06:15
I know @Hopeless but the reason for using Linq is, i've no reference mainsaleinvoicetbl from detailinvoice table to mainsaleinvoicetbl in entity relations that's why must have to use Linq rather than Lambda.... — Osama Rizwan, Jan 10 '20 at 06:22
actually you can do anything with LINQ using the method syntax, you don't have to use sql-like syntax to do things that method syntax cannot do. In some cases you have to use method syntax because sql-like syntax cannot help. It's just more convenient, more friendly and shorter in some cases, especially easier for some SQL developers who not good at c# to follow. — Hopeless, Jan 10 '20 at 06:42
@Hopeless Bro, Now what's the exact solution of my Question. I'm using sum() function but it isn't giving the required output... — Osama Rizwan, Jan 10 '20 at 06:46
sorry that I did not intend to dig deeper in your actual issue. It looks fairly complicated at least from what you posted. If the first (method syntax) works, you just need to convert it to the equivalent one using sql-like syntax. It's not very hard to do if you understand all the relationships and models. I guess it would take about 15 minutes to complete on average, someone may do that faster. — Hopeless, Jan 10 '20 at 07:25

score 0 · Accepted Answer · answered Jan 17 '20 at 05:00

 var list=from msi in db.MainSaleInvoiceTbls
          join dsi in db.DetialSaleInvoiceTbls on msi.Id equals dsi.MainSaleInvoiceId
          join ca in db.CustomerAccounts on msi.CustomerId equals ca.Id
          join cg in db.MiscLists on ca.CustomerGroupId equals cg.Id into a
          where msi.IsActive == true && msi.CompanyId == UniversalInfo.UserCompany.Id 
          && msi.MainSaleInvoiceDataType == MainSaleInvoiceType.SOInvoice
          from cg in a.DefaultIfEmpty()
          select new
          {mainSaleInvoice.Date, 
           mainSaleInvoice.FinancialVoucher,mainSaleInvoice.AccountCode,
           mainSaleInvoice.CustomerName,mainSaleInvoice.Name,
           mainSaleInvoice.SaleOrderPrefix, mainSaleInvoice.SaleOrderNumber,
           mainSaleInvoice.SalesId, }).sum(x=>x. (mainSaleInvoice.Quantity * 
           mainSaleInvoice.UnitPrice)).groupby msi new 
          {msi.Date,msi.FinancialVoucher,msi.SaleOrderPrefix,msi.SaleOrderNumber,
           msi.SalesId};


          Date = mainSaleInvoice.Date;
          Voucher = mainSaleInvoice.FinancialVoucher;
          InvoiceAccount = mainSaleInvoice.AccountCode;
          CustomerName = mainSaleInvoice.CustomerName;
          CustomerGroup = mainSaleInvoice.Name;
          Invoice = mainSaleInvoice.SaleOrderPrefix + 
          mainSaleInvoice.SaleOrderNumber;
          PurchaseOrder = mainSaleInvoice.SalesId;
          SalesTax = "";
          InvoiceAmount =list;

Qty and UnitPrice is coming from DetialSaleInvoiceTbls then sum it.... — Osama Rizwan, Jan 17 '20 at 10:56
written this Query Alternatively....``` select Date,FinancialVoucher,aa.AccountCode,aa.CustomerName,cg.Name,sum(dsi.Quantity*dsi.UnitPrice) as Invoice_Amount from MainSaleInvoiceTbls inner join DetialSaleInvoiceTbls dsi on dsi.MainSaleInvoiceId = MainSaleInvoiceTbls.Id inner join CustomerAccounts aa on aa.Id = MainSaleInvoiceTbls.CustomerId inner join MiscLists cg on cg.Id = aa.CustomerGroupId where MainSaleInvoiceTbls.MainSaleInvoiceDataType = 2 and MainSaleInvoiceTbls.CompanyId = 2 and DATE = '2019/06/30' group by Date,FinancialVoucher,aa.AccountCode,aa.CustomerName,SalesId,cg.Name``` — Osama Rizwan, Jan 17 '20 at 10:58
So can you clarify more what should I add or alternate this query. — Senuri Ruhunuge, Jan 17 '20 at 11:52
is there anyway to see or debug Linq expression equivalent to SQL statement in C# ?? — Osama Rizwan, Sep 08 '20 at 06:05

LINQ: join multiple Table Column using Linq and find aggregated sum from child table values

1 Answers1