Join the Two tables in Entity Framework

In this post we are going to see how to create a join between the two tables in Entity framework, for that first we create a two tables and there related data in Sql .

create table department

(

      id int identity(1,1) primary key,

      name varchar(20),

      description varchar(30),

      head int

)

create table Employee

(

      id int identity(1,1),

      name varchar(30),

      deptid int references department(id),

      contact int

)

insert into department(name,description)values('Finance','Money related')

insert into department(name, description)values('HR','Human Resources')

insert into department(name,description)values('Testing','Testing')

insert into employee(name,deptid,contact)

select 'Suresh',1,3628283

union

select 'Ramesh',1,4352352

union

select 'Ravi', 3, 3254524

union

select 'Jeeva',2,3455424

union

select 'Kalai',2,435423

SELECT * FROM EMPLOYEE

SELECT * FROM DEPARTMENT

Then  we have to implement the Entity Framework in our code refer this post Export Tables from the DB to EF. Now we have access to our tables from our EF.

From our sample we have an employee database which have relation which the Department table, so for now we are going to join this two tables and get the HR department Employees alone.To do this we have to include the table which we are going to Join

Our Database is reference as testingEntities, from that reference we can refer our tables in DB.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace EF_Sample

{

    class Program

    {

        static void Main(string[] args)

        {

            try

            {

                testingEntities context = new testingEntities();

                var datacollection =                                                                            context.Employees.Include("Department").Where(x=>x.department.name=="HR");

                foreach (var d in datacollection)

                {

                    Console.WriteLine(" Name: " + d.name + " ,Department :- " +                                            d.department.name+Environment.NewLine);

                }

                Console.WriteLine("Employees Count :- "+datacollection.Count());

                Console.Read();

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

        }

    }

}

Output:

*************

 Name: Jeeva ,Department :- HR

 Name: Kalai ,Department :- HR

Employees Count :- 2

If  we want to do the same thing in stored procedure then do the following.

Create procedure Emp_dept

AS

BEGIN

SET NOCOUNT ON

SELECT e.id 'employee id',e.name 'Name',d.id 'department id',d.name 'department name'

FROM Employee e

join DEPARTMENT d

on e.deptid = d.id

SET NOCOUNT OFF

END

From this post you can learn how to join the two tables in Entity framework.