Join Using LINQ to SQL



REQUREMENTS(DataBase Logic) 
  • Table1-Invoice Table
  • Table2-Credit Notes(Crdeitedamount COls)
  • Table3-Recepiept Table (ReceivedAmount COLs)  
 Logic For Grid Calculation:
  1. Total Amount=Sum(Invoice AMount -Tot CreditedNotes  Amount) group by invoice ID Left join Invoice Table.
  2. Received=Sum(total Received)  group by Invoice ID left join Invoice Table.
  3.  Outstanding Amount=Total amount-Received. 
  1. using System;  
  2. using System.Configuration;  
  3. using System.Data;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.Security;  
  7. using System.Web.UI;  
  8. using System.Web.UI.HtmlControls;  
  9. using System.Web.UI.WebControls;  
  10. using System.Web.UI.WebControls.WebParts;  
  11. using System.Xml.Linq;  
  12. using System.Data.SqlClient;  
  13. public partial class _Default: System.Web.UI.Page {  
  14.     static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RealConnectionString"].ConnectionString);  
  15.     protected void Page_Load(object sender, EventArgs e) {  
  16.         DataClasses2DataContext db = new DataClasses2DataContext();  
  17.         db.Connection.ConnectionString = con.ConnectionString;  
  18.         //from invoice main    
  19.         var invoicemain = from c in db.Customerinvoices  
  20.         where c.status == "closed"  
  21.         select c;  
  22.         //from Received    
  23.         var receivedmain = from c in db.invoicereceiveds  
  24.         where c.status == "posted"  
  25.         group c by c.invoiceid into g  
  26.         select new {  
  27.             receivedid = g.Key,  
  28.                 receivedamount = g.Sum(x => x.recevedamount)  
  29.         };  
  30.         //total received amount group by invoiceid    
  31.         //sum of credited amount group by invoice id    
  32.         var sumcredited = from x in db.creditnotes  
  33.         group x by x.invoiceid  
  34.         into g  
  35.         select new {  
  36.             invoiceid = g.Key,  
  37.                 creditedamount = g.Sum(x => x.creditamount)  
  38.         };  
  39.         //***** ********* List of totalinvoiceamount**** ***** ************    
  40.         var totinvoiceamnt = from x1 in invoicemain  
  41.         join x2 in sumcredited  
  42.         on x1.invoiceid equals x2.invoiceid  
  43.         into g  
  44.         from c in g.DefaultIfEmpty()  
  45.         join x3 in receivedmain  
  46.         on x1.invoiceid equals x3.receivedid  
  47.         into g1  
  48.         from c1 in g1.DefaultIfEmpty()  
  49.         select new {  
  50.             invoiceid = x1.invoiceid,  
  51.                 TotalInvoiceAmount = x1.invoicamount - c.creditedamount,  
  52.                 Recevied = c1.receivedamount,  
  53.                 invoiceno = x1.invoiceno,  
  54.                 invoicedate = x1.invoicedate  
  55.         };  
  56.         grid1.Datasourec(totinvoiceamnt);  
  57.         grid1.databind();  
  58.     }  
  59. }