TABLES JOIN CLAUSE in LINQ ENTITY

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Configuration;

using System.IO;

namespace LinqSamples
{
    public partial class WebForm5 : System.Web.UI.Page
    {
        TMSMASTEREntities1 SDC = new TMSMASTEREntities1();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TMSMASTERConnectionString"].ConnectionString.ToString());
                BindGrid();             

            }

        }
        private void BindGrid()
        {

            //var query = from d in SDC.Employees
            //            from l in SDC.Students.Where(loc => loc.StudentId == d.EmpID).DefaultIfEmpty()
            //            where d.Emp_Name.Contains("fg") || l.Name.Contains("fgh")
            //            select new
            //            {
            //                Employees = d,
            //                Students = l,
                          
            //            };

            //var results = (from r in query.AsEnumerable()
            //               select new
            //               {
            //                   Emp_Name = r.Employees.Emp_Name,
            //                   Emp_Address = r.Employees.Emp_Address,                              
            //                   StudentId = r.Students.StudentId,
            //                   Name = r.Students.Name,
            //                   Address = r.Students.Address,
            //                   BirthDate = r.Students.BirthDate,
            //                   Image = r.Students.Image,
            //                   Email = r.Students.Email,
            //                   Mobile = r.Students.Mobile,
            //                   Description = r.Students.Description
                             
            //               }).ToList();


            var results = (from d in SDC.Employees
                           join l in SDC.Students on d.EmpID equals l.StudentId                         
                           select new
                           {
                               Emp_Name =d.Emp_Name,
                               Emp_Address =d.Emp_Address,
                               StudentId = l.StudentId,
                               Name = l.Name,
                               Address = l.Address,
                               BirthDate = l.BirthDate,
                               Image = l.Image,
                               Email = l.Email,
                               Mobile = l.Mobile,
                               Description = l.Description
                           }).ToList();
           

            grid.DataSource = results;
            grid.DataBind();

        }

    }
}

JOIN CLAUSE in LINQ ENTITY

 public class Books
    {
        public string ID { get; set; }
        public string Title { get; set; }
        public decimal Price { get; set; }
        public DateTime DateOfRelease { get; set; }

        public static List<Books> GetBooks()
        {
            List<Books> list = new List<Books>();
            list.Add(new Books  {  ID = "001",Title = "Programming in C#", Price = 634.76m, DateOfRelease = Convert.ToDateTime("2010-02-05")  });

            list.Add(new Books  {  ID = "002",Title = "Learn Jave in 30 days", Price = 250.76m, DateOfRelease = Convert.ToDateTime("2011-08-15")  });

            list.Add(new Books  {  ID = "003",Title = "Programming in ASP.Net 4.0", Price = 700.00m, DateOfRelease = Convert.ToDateTime("2011-02-05") });

            list.Add(new Books  {  ID = "004",Title = "VB.Net Made Easy", Price = 500.99m, DateOfRelease = Convert.ToDateTime("2011-12-31")  });

            list.Add(new Books  {  ID = "005",Title = "Programming in C", Price = 314.76m, DateOfRelease = Convert.ToDateTime("2010-02-05")  });

            list.Add(new Books  {  ID = "006",Title = "Programming in C++", Price = 456.76m, DateOfRelease = Convert.ToDateTime("2010-02-05") });

            list.Add(new Books  {  ID = "007",Title = "Datebase Developement",  Price = 1000.76m, DateOfRelease = Convert.ToDateTime("2010-02-05")  });
            return list;
        }

    }



 public class Salesdetails
    {
        public int sales { get; set; }
        public int pages { get; set; }
        public string ID { get; set; }

        public static IEnumerable<Salesdetails> getsalesdetails()
        {
                    Salesdetails[] sd =
              {
              new Salesdetails { ID = "001", pages=678, sales = 110000},
              new Salesdetails { ID = "002", pages=789, sales = 60000},
              new Salesdetails { ID = "003", pages=456, sales = 40000},
              new Salesdetails { ID = "004", pages=900, sales = 80000},
              new Salesdetails { ID = "005", pages=456, sales = 90000},
              new Salesdetails { ID = "006", pages=870, sales = 50000},
              new Salesdetails { ID = "007", pages=675, sales = 40000},
              };
              return sd.OfType<Salesdetails>();
        }
    }


 List<Books> books = Books.GetBooks();

            //SELECT Title from Books
            var booktitles = from b in books select b.Title;
           

            foreach (var title in booktitles)
                Label1.Text += String.Format("{0} <br />", title);

            //The Join clause:
            //query on both the tables using the join clause

            IEnumerable<Books> books1 = Books.GetBooks();
            IEnumerable<Salesdetails> sales =
                                Salesdetails.getsalesdetails();
            var booktitles1 = from b in books1
                             join s in sales
                             on b.ID equals s.ID
                             select new { Name = b.Title, Pages = s.pages };
            foreach (var title in booktitles1)
                Label2.Text += String.Format("{0} <br />", title);


            //The Where clause:
            //The 'where clause' allows adding some conditional filters to the query.

            var booktitles2 = from b in books
                             join s in sales
                             on b.ID equals s.ID
                             where s.pages > 500
                             select new { Name = b.Title, Pages = s.pages };

            foreach (var title in booktitles2)
                Label3.Text += String.Format("{0} <br />", title);

            //The Orderby and Orderbydescending clauses:
            //These clauses allow sorting the query results.

            var booktitles3 = from b in books
                             join s in sales
                             on b.ID equals s.ID
                             orderby b.Price
                             select new
                             {
                                 Name = b.Title,
                                 Pages = s.pages,
                                 Price = b.Price
                             };

            foreach (var title in booktitles3)
                Label4.Text += String.Format("{0} <br />", title);

            //The Let clause:
            //The let clause allows defining a variable and assigning it a value calculated from the data values.

            var booktitles4 = from b in books
                             join s in sales
                             on b.ID equals s.ID
                             let totalprofit = (b.Price * s.sales)
                             select new { Name = b.Title, TotalSale = totalprofit };
            foreach (var title in booktitles4)
                Label5.Text += String.Format("{0} <br />", title);

CREATE READ UPDATE and DELETE - CRUD operations using Entity Framework 4

<table width="80%" cellspacing="1" cellpadding="5" style="background: #ccc">

    <tr style="background: #fff">

        <td colspan="2">

            <strong>Student Management</strong>

        </td>

    </tr>

    <tr style="background: #fff">

        <td colspan="2">

            <asp:Label ID="statusLabel" runat="server" Text="" ForeColor="Red">

            </asp:Label>

        </td>

    </tr>

    <tr style="background: #fff">

        <td colspan="2">

            <asp:GridView ID="grid"

                          runat="server"

                          DataKeyNames="StudentId"                                           

                          OnSelectedIndexChanging="grid_SelectedIndexChanging"

                          OnRowDeleting="grid_RowDeleting"

                          AutoGenerateColumns="false"

                          Width="100%">

            <Columns>

                <asp:TemplateField>

                    <HeaderTemplate>

                        <table width="100%" cellpadding="5" cellspacing="1">

                            <tr>

                                <td align="center" style="width: 5%;">

                                    nbsp;                                 

                                </td>

                                <td align="center" style="width: 5%;">

                                    &nbsp;

                                </td>

                                <td align="left" style="width: 15%">

                                    Name

                                </td>

                                <td align="left" style="width: 15%">

                                    Address

                                </td>

                                <td align="left" style="width: 15%">

                                    Birthdate

                                </td>

                                <td align="left" style="width: 10%">

                                    Photo

                                </td>

                                <td align="left" style="width: 20%">

                                    Email

                                </td>

                                <td align="left" style="width: 15%">

                                    Mobile

                                </td>

                            </tr>

                        </table>

                    </HeaderTemplate>

                    <ItemTemplate>

                        <table width="100%" cellpadding="0" cellspacing="1">

                            <tr>

                                <td align="center" style="width: 5%;">

                                    <asp:ImageButton ID="ImageButton1"

                                                    runat="server"                                                                                CommandName="Select"

                                                     EnableTheming="false"

                                                     ImageUrl="~/Images/edit.png"
                                                    
                                                     AlternateText="Edit"

                                                     CausesValidation="false" />

                                </td>

                                <td align="center" style="width: 5%;">

                                    <asp:ImageButton ID="LinkButton2"

                                                     runat="server"

                                                      ImageUrl="~/Images/delete.png"

                                                      AlternateText="Delete"

                                                     CommandName="Delete"

                                                       EnableTheming="false"

                                                     CausesValidation="false" />

                                </td>

                                <td align="left" style="width: 15%">

                                    <asp:Label ID="lblName" runat="server"

                                              Text='<%# Eval("Name")%>'></asp:Label>

                                </td>

                                <td align="left" style="width: 15%">

                                    <asp:Label ID="lblAddress" runat="server"

                                              Text='<%# Eval("Address")%>'></asp:Label>

                                </td>

                                <td align="center" style="width: 15%">

                                    <asp:Label ID="lblBirthdate" runat="server"

                                             Text='<%# Eval("Birthdate")%>'></asp:Label>

                                </td>

                                <td align="center" style="width: 10%">

                                    <asp:Image ID="imgPhoto" runat="server" Width="30"                                           Height="30"

                                        ImageUrl='<%# "~/Images/" + Eval("Image")%>' />

                                </td>

                                <td align="left" style="width: 20%">

                                    <asp:Label ID="lblEmail" runat="server"

                                              Text='<%# Eval("Email")%>'></asp:Label>

                                </td>

                                <td align="left" style="width: 15%">

                                    <asp:Label ID="lblMobile" runat="server"

                                              Text='<%# Eval("Mobile")%>'></asp:Label>

                                </td>

                            </tr>

                        </table>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Name<asp:HiddenField ID="hdnId" runat="server" />

    </td>

    <td>

        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Address

    </td>

    <td>

        <asp:TextBox ID="txtAddress" runat="server" TextMode="MultiLine"></asp:TextBox>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Birthdate

    </td>

    <td>

        <asp:TextBox ID="txtBirthdate" runat="server"></asp:TextBox>

        <asp:Calendar ID="calBirthdate" runat="server" 

                      OnSelectionChanged="calBirthdate_SelectionChanged">

        </asp:Calendar>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Image

    </td>

    <td>

        <asp:Image ID="imgThumb" runat="server" Width="100" Height="100" />

        <asp:FileUpload ID="fupImage" runat="server" />

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Email

    </td>

    <td>

        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Mobile

    </td>

    <td>

        <asp:TextBox ID="txtMobile" runat="server"></asp:TextBox>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        Description

    </td>

    <td>

        <asp:TextBox ID="txtDescription" runat="server" TextMode="MultiLine">

        </asp:TextBox>

    </td>

</tr>

<tr style="background: #fff">

    <td>

        &nbsp;

    </td>

    <td>

        <asp:Button ID="btnAdd" ValidationGroup="add" OnClick="btnAdd_Click"                               runat="server" Text="Submit">

        </asp:Button>

        <asp:Button ID="btnReset" OnClick="btnReset_Click" runat="server"                                        CausesValidation="false" Text="Reset">

        </asp:Button>

        <asp:Button ID="btnUpdate" ValidationGroup="add" OnClick="btnUpdate_Click"                            runat="server" Text="Update">

        </asp:Button>

        <asp:Button ID="btnCancel" CausesValidation="false" OnClick="btnCancel_Click"                      runat="server" Text="Cancel">

        </asp:Button>

    </td>

</tr>

</table>


using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Configuration;

using System.IO;

namespace LinqSamples
{
    public partial class WebForm4 : System.Web.UI.Page
    {
        //StudentDbDataContext SDC = new StudentDbDataContext();
        TMSMASTEREntities1 SDC = new TMSMASTEREntities1();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                btnUpdate.Visible = false;

                btnCancel.Visible = false;

                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TMSMASTERConnectionString"].ConnectionString.ToString());



                BindGrid();

                checkMax();

                bindDDL();

            }

        }
        private void BindGrid()
        {
            var students = from student in SDC.Students

                           select new {
                               student.StudentId,
                               student.Name,
                               student.Address,
                               student.BirthDate,
                               student.Image,
                               student.Email,
                               student.Mobile,
                               student.Description
                           };

            grid.DataSource = students;
            grid.DataBind();

        }



        protected void grid_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {

            btnAdd.Visible = false;

            btnReset.Visible = false;

            btnUpdate.Visible = true;

            btnCancel.Visible = true;



            int id =Convert.ToInt32( grid.DataKeys[e.NewSelectedIndex].Value.ToString());

            hdnId.Value = id.ToString();



            //SDC = new StudentDbDataContext();
            SDC = new TMSMASTEREntities1();

            var singleStudent = SDC.Students.Single(student => student.StudentId == id);

         

            txtName.Text = singleStudent.Name;

            txtAddress.Text = singleStudent.Address;

            txtBirthdate.Text = singleStudent.BirthDate.ToString();

            imgThumb.ImageUrl = "~//images//" + singleStudent.Image;

            imgThumb.Visible = true;

            txtEmail.Text = singleStudent.Email;

            txtMobile.Text = singleStudent.Mobile.ToString();

            txtDescription.Text = singleStudent.Description;



        }



        protected void btnAdd_Click(object sender, EventArgs e)
        {

            try
            {

                //SDC = new StudentDbDataContext();
                SDC = new TMSMASTEREntities1();
                Student student = new Student();



                student.Name = txtName.Text;

                student.Address = txtAddress.Text;

                student.BirthDate = Convert.ToDateTime(txtBirthdate.Text);

                if (fupImage.HasFile)
                {

                    student.Image = fupImage.FileName;

                    string path = Server.MapPath(".") + "\\images\\";

                    fupImage.SaveAs(path + fupImage.FileName);

                }

                student.Email = txtEmail.Text;

                student.Mobile = Convert.ToInt64(txtMobile.Text);

                student.Description = txtDescription.Text;



                //SDC.Students.InsertOnSubmit(student);

                //SDC.SubmitChanges();

                SDC.AddToStudents(student);
              
              
                SDC.SaveChanges();
             

                ClearControls();

                statusLabel.Text = "Record Inserted Successfully.";

            }

            catch (Exception Err)
            {

                statusLabel.Text = Err.Message;

            }



            BindGrid();

        }



        protected void btnReset_Click(object sender, EventArgs e)
        {

            ClearControls();

        }



        protected void btnCancel_Click(object sender, EventArgs e)
        {

            ClearControls();



            btnAdd.Visible = true;

            btnReset.Visible = true;

            btnUpdate.Visible = false;

            btnCancel.Visible = false;

        }



        protected void btnUpdate_Click(object sender, EventArgs e)
        {

            try
            {

                //SDC = new StudentDbDataContext();
                int Id=Convert.ToInt32(hdnId.Value);

                SDC = new TMSMASTEREntities1();

                var singleStudent = SDC.Students.Single(student => student.StudentId == Id);

                string ImageName = singleStudent.Image;

                string path = Server.MapPath(".") + "\\images\\";



                singleStudent.Name = txtName.Text;

                singleStudent.Address = txtAddress.Text;

                singleStudent.BirthDate = Convert.ToDateTime(txtBirthdate.Text);



                if (fupImage.HasFile)
                {

                    if (File.Exists(path + ImageName))

                        File.Delete(path + ImageName);



                    ImageName = fupImage.FileName;



                    fupImage.SaveAs(path + ImageName);



                    singleStudent.Image = ImageName;

                }



                singleStudent.Email = txtEmail.Text;

                singleStudent.Mobile = Convert.ToInt64(txtMobile.Text);

                singleStudent.Description = txtDescription.Text;

                //SDC.SubmitChanges();
           
                SDC.SaveChanges();

                ClearControls();

                statusLabel.Text = "Record Inserted Successfully.";



                btnAdd.Visible = true;

                btnReset.Visible = true;

                btnUpdate.Visible = false;

                btnCancel.Visible = false;

            }

            catch (Exception Err)
            {

                statusLabel.Text = Err.Message;

            }



            BindGrid();

        }



        protected void grid_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {

            int id =Convert.ToInt32( grid.DataKeys[e.RowIndex].Value.ToString());



            //StudentDbDataContext SDC = new StudentDbDataContext();
            TMSMASTEREntities1 SDC = new TMSMASTEREntities1();

            var singleStudent = SDC.Students.Single(student => student.StudentId == id);



            string path = Server.MapPath(".") + "\\images\\";

            if (File.Exists(path + singleStudent.Image))
            {

                File.Delete(path + singleStudent.Image);

            }



            //SDC.Students.DeleteOnSubmit(singleStudent);

            //SDC.SubmitChanges();

            SDC.DeleteObject(singleStudent);

            SDC.SaveChanges();

            BindGrid();

        }



        private void ClearControls()
        {

            txtName.Text = string.Empty;

            txtAddress.Text = string.Empty;

            txtEmail.Text = string.Empty;

            txtMobile.Text = string.Empty;

            txtDescription.Text = string.Empty;

            txtBirthdate.Text = string.Empty;

            imgThumb.Visible = false;

        }

        public void checkMax()
        {
            int? maxEmpID = SDC.Students.Max(q => (int?)q.StudentId) ;

            int? ID = (from n in SDC.Students
                         where n.Name == "fgh"
                      select (int?)n.StudentId).Max<int?>();

            if (maxEmpID != null)
            {
                maxEmpID = maxEmpID + 1;
                hdnId.Value = maxEmpID.ToString();
            }
            else
            {
                maxEmpID = 1000;
                hdnId.Value = maxEmpID.ToString();
            }
        }

        public void bindDDL()
        {
            var display = from e in SDC.Students select new { e.StudentId };

            ddlStudentID.DataSource = display.ToList();
            ddlStudentID.DataTextField = "StudentId";
            ddlStudentID.DataValueField = "StudentId";
            ddlStudentID.DataBind();
            ddlStudentID.Items.Insert(0, "--Select--");

        
        }

        protected void calBirthdate_SelectionChanged(object sender, EventArgs e)
        {

            txtBirthdate.Text = calBirthdate.SelectedDate.ToShortDateString();

        }

        protected void ddlStudentID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlStudentID.SelectedItem.Text != "--Select--")
            {

                int id = Convert.ToInt16(ddlStudentID.SelectedValue.ToString());

                var display = from student in SDC.Students

                              where student.StudentId.Equals(id)

                              select new
                              {
                                  student.StudentId,
                                  student.Name,
                                  student.Address,
                                  student.BirthDate,
                                  student.Image,
                                  student.Email,
                                  student.Mobile,
                                  student.Description
                              };



                foreach (var v in display)
                {

                    txtName.Text = v.Name;

                    txtAddress.Text = v.Address;

                    txtBirthdate.Text = v.BirthDate.ToString();

                    imgThumb.ImageUrl = "~//images//" + v.Image;

                    imgThumb.Visible = true;

                    txtEmail.Text = v.Email;

                    txtMobile.Text = v.Mobile.ToString();

                    txtDescription.Text = v.Description;

                }

            }
        }
    }
}