Join Using LINQ to SQL

This blog show how to Joining 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. 

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default: System.Web.UI.Page {
    static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RealConnectionString"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e) {
        DataClasses2DataContext db = new DataClasses2DataContext();
        db.Connection.ConnectionString = con.ConnectionString;
        //from invoice main
        var invoicemain = from c in db.Customerinvoices
        where c.status == "closed"
        select c;
        //from Received
        var receivedmain = from c in db.invoicereceiveds
        where c.status == "posted"
        group c by c.invoiceid into g
        select new {
            receivedid = g.Key,
                receivedamount = g.Sum(x => x.recevedamount)
        };
        //total received amount group by invoiceid
        //sum of credited amount group by invoice id
        var sumcredited = from x in db.creditnotes
        group x by x.invoiceid
        into g
        select new {
            invoiceid = g.Key,
                creditedamount = g.Sum(x => x.creditamount)
        };
        //***** ********* List of totalinvoiceamount**** ***** ************
        var totinvoiceamnt = from x1 in invoicemain
        join x2 in sumcredited
        on x1.invoiceid equals x2.invoiceid
        into g
        from c in g.DefaultIfEmpty()
        join x3 in receivedmain
        on x1.invoiceid equals x3.receivedid
        into g1
        from c1 in g1.DefaultIfEmpty()
        select new {
            invoiceid = x1.invoiceid,
                TotalInvoiceAmount = x1.invoicamount - c.creditedamount,
                Recevied = c1.receivedamount,
                invoiceno = x1.invoiceno,
                invoicedate = x1.invoicedate
        };
        grid1.Datasourec(totinvoiceamnt);
        grid1.databind();
    }
}