title: SQL to Linq, having trouble in SUM and Multiple column group by with joins
body: <p>I want below query to be converted into linq for paging in MVC.</p>
  <pre><code>SELECT tblInvoiceMaster.InvoiceNo,
           tblCompanyMaster.CompanyId,
           tblCompanyMaster.CompanyName,
           tblInvoiceMaster.InvoiceType,
           tblInvoiceMaster.InvoiceId,
           Sum(tblInvoiceItem.Quantity * tblProductMaster.Rate)    AS TotalPrice,
           CONVERT(VARCHAR(11), tblInvoiceMaster.InvoiceDate, 113) AS InvoiceDate
    FROM   tblCompanyMaster
           INNER JOIN tblInvoiceMaster
                   ON tblCompanyMaster.CompanyId = tblInvoiceMaster.CompanyId
           INNER JOIN tblInvoiceItem
                   ON tblInvoiceMaster.InvoiceId = tblInvoiceItem.InvoiceId
           INNER JOIN tblProductMaster
                   ON tblProductMaster.ProductId = tblInvoiceItem.ProductId
    GROUP  BY tblInvoiceMaster.InvoiceNo,
              tblCompanyMaster.CompanyId,
              tblCompanyMaster.CompanyName,
              tblInvoiceMaster.InvoiceDate,
              tblInvoiceMaster.InvoiceId,
              tblInvoiceMaster.InvoiceType
  </code></pre>
  <p>Equivalent Linq Query:</p>
  <pre><code>                        (from company in DbContext.tblCompanyMasters
                          join invoice in DbContext.tblInvoiceMasters
                              on company.CompanyId equals invoice.CompanyId
                          join item in DbContext.tblInvoiceItems
                              on invoice.InvoiceId equals item.InvoiceId
                          join product in DbContext.tblProductMasters
                              on item.ProductId equals product.ProductId
                          //here I was having issue for group by columns from multiple tables
                          group new { company, invoice, item, product } by new
                          {
                              invoice.InvoiceNo,
                              invoice.InvoiceDate,
                              invoice.InvoiceId,
                              invoice.InvoiceType,
                              company.CompanyId,
                              company.CompanyName,
  
                          } into g
                          select new
                          {
                              g.Key.CompanyId,
                              g.Key.CompanyName,
                              g.Key.InvoiceNo,
                              g.Key.InvoiceType,
                              g.Key.InvoiceId,
                              g.Key.InvoiceDate,
                              Sum = g.Sum(o =&gt; o.item.Quantity * o.product.Rate)
                          }).ToList();
  </code></pre>
  <p>I am stuck in multiple column <code>GROUP BY</code> which are from different tables and doing <code>SUM</code> while performing multiplication, for example:</p>
  <pre><code>SUM(tblInvoiceItem.Quantity * tblProductMaster.Rate)
  </code></pre>
  <p>EDIT: Also suggested in the comment, do not use this kind of join queries in linq, instead use navigation properties for the better end result.</p>