LINQ To Entity Framework Tutorial

As a startup, I choose this topic for those beginners who wants to use Entity Framework. The ADO.NET Entity Framework helps developers to access database very easily. All you need is to create a data model from actual database and programming against generated data model instead of programming directly against the actual database by either creating stored procedure or writing select/insert/update/delete query in data access layer itself. Using this way of programming during the development phase of the project helps to reduce amount of code to access database and also reduce maintenance work in future.
Please follow below steps in order to create data access application using ADO.NET Entity Framework. In this tutorial I am using “Northwind” database in backend.

1. Create new “ASP.Net Empty Web Application” using Visual Studio 2010.
   
2. Create new folder under web application project. Name it “DataAccess”
   
3. Right click on “DataAccess” folder and click on Add –> New Item…
   
4. Select “Data” from right panel in “Add New Item” popup.
   
5. Select “ADO.NET Entity Data Model” and name it to “Northwind.edmx”. Click on “Add” button. This will open “Entity Data Model Wizard”.
   
6. Select “Generate from database” option and click on “Next” button.
   
7. Chose a connection from a list if already exists OR create new by clicking on “New Connection…” button.
   
8. Make sure the “Save entity connection settings in Web.Config as:” option is checked. Click on “Next” button.
   
9. Select Tables, Views AND / OR Stored Procedures which you want to use in your application from this step. As I don’t have any Views / Stored Procedures in my database I am selecting only tables in this step.
   
10. Click on “Finish” button.

Now you have your data model ready to use. You can use Entity Framework’s built in methods to Insert / Update / Delete records from particular table and also write LINQ to Select records from one or more tables. You can write LINQ for Inner Join / Left Join / Grouping / Sorting / Paging etc.
In order to do database operation using ADO.NET Entity Framework, please follow below steps:

1. Add new class file under “DataAccess” folder and name the class file to “DBBase.cs”. Write bellow code in this class file.

      1: using System;

      2: using System.Collections.Generic;

      3: using System.Linq;

      4: using System.Web;

      5:  

      6: namespace LINQToEF.DataAccess

      7: {

      8:     public class DBBase

      9:     {

     10:         private NorthwindEntities _NorthwindEntity = new NorthwindEntities();

     11:  

     12:         public NorthwindEntities NorthwindEntity { get { return _NorthwindEntity; } }

     13:     }

     14: }

   
   
2. Add one more class file under “DataAccess” folder and name the class file to “EmployeeDA.cs”.
   
3. Inherit “BaseDB” class in “EmployeeDA” class. Now you can access property “NorthwindEntity” from “EmployeeDA” class to perform database operation.

Now we are ready to do programming in data access layer to perform different kind of database operations using ADO.NET Entity Framework.
Select From Single Table:
Write LINQ as below to fetch record(s) from “Employee” table.

var allEmployees = from E in NorthwindEntity.Employees
select E;

Use allEmployees.ToArray(); OR allEmployees.ToList(); to fetch multiple records.
Use allEmployees.FirstOrDefault(); to fetch single record.
Select From Multiple Tables Using  Simple JOIN:

var allData = from E in NorthwindEntity.Employees
from O in NorthwindEntity.Orders
where E.EmployeeID == O.EmployeeID
select new { Employees = E, Orders = O };

foreach (var item in allData)
{
// You can access values from Employee and order table using item.Employees / item.Orders properties
// Fetch all required value from both tables, create a DataRow object and insert into DataTable
// Bind this datatable to the grid view

}

Insert / Update / Delete Operations:
Insert new record in employee table:
Create object of “Employee” and set all required properties.

Employee emp = new Employee 
{
FirstName = "First Name",
LastName = "Last Name"
// Set all properties here...
};

Add this object to “NorthwindEntity”

NorthwindEntity.AddToEmployees(emp);

Save changes in entity to insert record into database.

NorthwindEntity.SaveChanges();

Update existing record:

Employee emp = (from E in NorthwindEntity.Employees
where E.EmployeeID == 99
select E).FirstOrDefault();

if (emp != null)
{
NorthwindEntity.AcceptAllChanges();

emp.FirstName = "New Value";

NorthwindEntity.SaveChanges();
}

Delete existing record:

Employee emp = (from E in NorthwindEntity.Employees
where E.EmployeeID == 99
select E).FirstOrDefault();

if (emp != null)
{
NorthwindEntity.AcceptAllChanges();

NorthwindEntity.DeleteObject(emp);        

NorthwindEntity.SaveChanges();
}