Joining two tables using LINQ


In this article we are going to focus on how to join three database tables using LINQ. As we know in order to join three tables we need a common column in the tables using which we can join the tables and get the required columns. Usually we join two tables using a Foreign key of one table and Primary key of another table. In this article we will join Emp table with EmpCourse table and then join Course table with the EmpCourse table to get all the courses enrolled by the employee.

In this article we are going to focus on how to join two database tables using LINQ. As we know in order to join two table we need a common column in both the tables using which we can join the two tables and get the required columns. Usually we join two tables using a Foreign key of one table and Primary key of another table. In this article we will join Emp table with Course table to get all the courses enrolled by the employee.

Step 1:Launch Visual Studio 2012 -> Create a New Web Application -> Name:DemoJoinEntities -> Ok

Step 2:Open Solution Explorer -> Right Click on the project -> Add -> New Item -> Select -> Data on the left side -> select ADO.NET Entity Data Model -> name it EmpModel.edmx.
model
Step 3:
In Entity Data Model Wizard dialog box select Generate from database.
choose
Step 4:
Click on next -> In the Choose your data connection window select the New Connection -> Type .\sqlexpress for the server name and then select the "EmpDB" database from the "Select or enter a database name" dropdown.
newconnection
Step 5:
sqlexpress
Step 6:
Now Expand tables and then select Emp, Course, and EmpCourse tables and the click on Finish.
ecec
Step 7:
Then you can see all the three tables in the EmpModel.edmx designer window.
relation
Step 8:
In your web page add the following code :


<form id="form1" runat="server">
<div> Please enter Emp Id: <asp:TextBox ID="txtEmpId" runat="server"></asp:TextBox>
<asp:button runat="server" text="Display Course Details" ID="btnDisplayEmpCourse" OnClick="btnDisplayEmpCourse_Click" />

</div>
<asp:GridView ID="gvCourseDetails" runat="server">
</asp:GridView>
</form>


Here we have taken a TextBox, User will enter the EmpId in the textbox for which user want to see all the courses enrolled by the employee.
Step 9:
In the CodeBehind file write the following code:

protected void btnDisplayEmpCourse_Click(object sender, EventArgs e)
{
using (EmpDBEntities db = new EmpDBEntities())
{
int empId = Convert.ToInt32(txtEmpId.Text);
var result = from emp in db.Emps
join empcourse in db.EmpCourses on emp.EmpId equals empcourse.EmpId
join course in db.Courses on empcourse.CourseId equals course.CourseId
where emp.EmpId == empId
select new
{
CourseId = course.CourseId,
CourseName = course.CourseName
};
var courseDetails = result.ToList();
gvCourseDetails.DataSource = courseDetails;
gvCourseDetails.DataBind();
}
}

In the above code, First we are creating a context to access the entities in the EmpDB database using EmpDBEntities db = new EmpDBEntities(). Then we are using two joins to join three tables:Emp,EmpCourse and Course tables to get the CourseID and CourseName of the courses enrolled by a specific Employee with the EmpID entered in the textbox.
First join is used to join Emp table and EmpCourse Table to get the Courses for the Employee with an EmpId. Then we join Course table with EmpCourse table to get the CourseName and CourseId from the Course table of that Employee.
The we are converting the result to a list and binding it as a datasource to the GridView "gvCourseDetails".

The output for the Emp with EmpId = 1 is as shown below:
output


Article by Vaishali Jain
Miss. Jain Microsoft Certified Technology Specialist in .Net(Windows and Web Based application development)

Follow Vaishali Jain or read 127 articles authored by Vaishali Jain

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: