LINQ to Entities

LINQ to Entities means writing LINQ queries over entity framework objects. Generally, the Entity Framework is an ORM (Object Relational Mapper) that creates database tables as objects, and we will call these objects as entities. Using these entities, we can perform any operation like insert, update delete, etc.

 

Here we will see how to use LINQ with Entities; we will create one new web application and connect with SQL Server using Entity Framework. By using the entity framework, we will display the data in gridview.

LINQ to Entities Example

First, we will create one new “EmployeeDetails” table in the database. For that, execute the following query in your database and insert some dummy data to show it in the application.

 

CREATE TABLE [dbo].[EmployeeDetails](
[EmpId] INT IDENTITY (1, 1) NOT NULL,
[EmpName] VARCHAR (50) NULL,
[Location] VARCHAR (50) NULL,
[Gender] VARCHAR (20) NULL
PRIMARY KEY CLUSTERED ([EmpId] ASC)
);

insert into EmployeeDetails values ('Suresh Dasari','Chennai','Male')
insert into EmployeeDetails values ('Rohini Alavala','Chennai','Female')
insert into EmployeeDetails values ('Praveen Alavala','Guntur','Male')
insert into EmployeeDetails values ('Sateesh Chandra','Vizag','Male')
insert into EmployeeDetails values ('Sushmitha','Vizag','Female')
insert into EmployeeDetails values ('Madhav','Nagpur','Male')

Once we execute above query table will create with data as shown below.

 

EmployeeDetails Table in Database with Data

 

Now, we will create a new web application for that Go to File à Select New à Select Project like as shown below.

 

Create new linq to entities project in visual studio

 

Once we select a new project, a new popup will open in that select Asp.Net Empty Web Application and give name as “LINQtoADONET” and click OK to create a new web application as shown below.

 

Create new linq to entities asp.net web application

 

Once we click OK new popup will open in that select Empty template and click OK like as shown below.

 

Select asp.net empty template to create linq to entities application

 

To use Entities with LINQ, we need to add an ADO.NET Entity Data Model in our application. This ADO.NET Entity Data Model is the step-by-step process of importing SQL tables and converting them into objects using the Entity Framework Database First technique.

 

To add ADO.NET Entity Data Model, right-click on your application à select Add à Select New Item à select ADO.NET Entity Data Model à Give name as “EmployeeEntity” and Click OK like as shown below.

 

Add ado.net entity data model in linq to entities example

 

Once we add ADO.NET Entity Data Model, a new popup will open, as shown below the screen. Here we have already created the database with a table for our application, so we will select “EF Designer from Database” and click Next like as shown below.

 

Select Entity Framework Designer from database in data model wizard

 

Once we click the Next button, the following screen will appear. Now, we need to configure our database for that click on the New Connection button as shown below.

 

Configure database connection in LINQ to Entity Application

 

Once we click on the New Connection button, a new window will open in that choose a data source as “Microsoft SQL Server” and click Continue like as shown below.

 

Choose SQL Server Data Source in LINQ to Entities Application

 

Once we click Continue, the connection properties window will open in that mention all required connection properties to connect the required database, as shown below.

 

Enter all database server details to connect database in linq to entities

 

Once you enter all the details, click “Test Connection” to test whether all the connection properties are correct or not. If all the details are correct, we will get a message like “Test Connection Succeeded” as shown below.

 

Test database connection in linq to entities application

 

Once the connection succeeds, click the OK button; all the connection configurations will be added to our data connection, as shown below.

 

After adding new sql database connections to entity framework designer in linq to entities

 

Now click the Next button; it will show all the database objects available in the respective database from that select required table and click Finish like as shown below.

 

Select required tables from database in entity data model wizard

 

Once we click Finish Button, Entity Data Model will create in our application with our required tables that will be like as shown below.

 

Entity data model with selected tables in linq to entities

 

Once we add the Entity Data Model file, our project will be as shown below.

 

After adding ado.net entity data model in linq to entities project

 

If you observe the above project structure, we have context file “MySampleDBEntities” it contains our database connection settings that will be as shown below.

 

namespace LINQtoEntities
{
  using System;
  using System.Data.Entity;
  using System.Data.Entity.Infrastructure;
  public partial class MySampleDBEntities : DbContext
  {
    public MySampleDBEntities(): base("name=MySampleDBEntities")
    {
    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      throw new UnintentionalCodeFirstException();
    }
    public DbSet<EmployeeDetail> EmployeeDetails { get; set; }
  }
}

If you observe the above class, it inherits from DbContext class. In the constructor, it has specified the name as ”MySampleDBEntities”. So we will be using this to create an object of the context class to access our objects using Entity Framework. Also, it creates a Dbset of type EmployeeDetail to interact with the database. If we include more tables in your Entity Model, you need to specify the Dbset of the new class type.

 

We have another class “EmployeeDetail.cs” file that contains all the properties of the EmployeeDetail table we selected while creating the Entity Data Model that will be like as shown below.

 

namespace LINQtoEntities
{
  using System;
  using System.Collections.Generic;

  public partial class EmployeeDetail
  {
    public int EmpId { get; set; }
    public string EmpName { get; set; }
    public string Location { get; set; }
    public string Gender { get; set; }
  }
}

For every table that we selected from the database, a class file will be generated by the Entity Framework automatically.

 

Now we will add the web page to the application for that Right-click on your application à select Add à New Item à Select Web Form à give name as “Default.aspx” and click the OK button it will create a new page in the application.

 

Now open the Default.aspx page and write the code as shown below.

 

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1">
<title>Bind Gridview with LINQ to ADO.NET Operations</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDiv">
<asp:GridView ID="gvDetails" CssClass="Gridview" runat="server" AutoGenerateColumns="False">
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField HeaderText="Name" DataField="Name" />
<asp:BoundField HeaderText="Location" DataField="Location" />
<asp:BoundField HeaderText="Gender" DataField="Gender" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>

Now open the code behind file and write the code as shown below.

 

using System;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace LINQtoEntities
{
  public partial class Default : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      if (!Page.IsPostBack)
      {
        BindGridData();
      }
    }
    private void BindGridData()
    {
      // Create the object of the Context defined in the constructor of the context class.
      MySampleDBEntities db = new MySampleDBEntities();
      var result = from emp in db.EmployeeDetails
                   select new
                   {
                     Id = emp.EmpId,
                     Name = emp.EmpName,
                     Location = emp.Location,
                     Gender = emp.Gender
                   };
      gvDetails.DataSource = result.ToList();
      gvDetails.DataBind();
    }
  }
}

If you observe the above example, we created an object for the context file “MySampleDBEntities” to access database objects. Now we will run and see the output of the application.

Result of LINQ to Entities Example

Following is the result of the LINQ to Entities example.

 

LINQ to Entities Example in Asp.Net using C#, VB.NET

 

This is how we can use entities with LINQ to get the required data from the database.

Previous
 
Next