LINQ to Entities

  Total Views : 124
  Comments : 0
 
  Keywords : Linq to entities with example in c# / vb.net, How to write linq queries on entity framework in c# / vb.net with example, Linq to entities to write linq queries on entity object with example

Here we will learn LINQ to Entities in c# / vb.net with example and how to write LINQ queries on ADO.NET entity data model in c#, vb.net with example.

LINQ to Entities

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

 

Here we will see how to use LINQ with Entities for that we will create one new web application and make connection with SQL Server using Entity Framework and by using entity framework we will display the data in gridview.

LINQ to Entities Example

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

 

CREATE TABLE [dbo].[EmployeeDetails](

[EmpId]  INT IDENTITY (1, 1NOT NULL,

[EmpName]  VARCHAR (50NULL,

[Location] VARCHAR (50NULL,

[Gender] VARCHAR (20NULL

PRIMARY KEY CLUSTERED ([EmpId] ASC)

);

 

insertinto EmployeeDetails values ('Suresh Dasari','Chennai','Male')

insertinto EmployeeDetails values ('Rohini Alavala','Chennai','Female')

insertinto EmployeeDetails values ('Praveen Alavala','Guntur','Male')

insertinto EmployeeDetails values ('Sateesh Chandra','Vizag','Male')

insertinto EmployeeDetails values ('Sushmitha','Vizag','Female')

insertinto EmployeeDetails values ('Madhav','Nagpur','Male')

Once we execute above query table will create with data like 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 new project new popup will open in that select Asp.Net Empty Web Application and give name as “LINQtoADONET” and click OK to create new web application like 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 to import SQL tables and convert them into objects using 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 new popup will open like as shown below screen. Here we already created database with 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 Next button following screen will appear here we need to configure our database for that click on New Connection button like as shown below.

 

Configure database connection in LINQ to Entity Application

 

Once we click on New Connection button new window will open in that choose 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 connection properties window will open in that mention all required connection properties to connect required database like 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 correct or not. In case if all the details correct means we will get message like “Test Connection Succeeded” like as shown below

 

Test database connection in linq to entities application

 

Once connection succeeded click OK button now all the connection configurations will be added to our data connection like as shown below

 

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

 

Now click Next button it will show all the database objects available in 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 Entity Data Model file our project will be like as shown below.

 

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

 

If you observe above project structure, we have context file “MySampleDBEntities” it contains our database connection settings that will be like 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 above class its 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 Dbset of type EmployeeDetail to interact with the database. If we include more tables to our Entity Model, then we have to specify the Dbset of the new class type.

 

We have another class file “EmployeeDetail.cs” that contains all the properties of EmployeeDetail table which we selected while creating 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 which we selected from database a class file will be generated by the Entity Framework automatically.

 

Now we will add 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 OK button it will create new page in application.

 

Now open Default.aspx page and write the code like 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 code behind file and write the code like 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 above example, we created object for context file “MySampleDBEntities” to access database objects. Now we will run and see the output of the application.

Output of LINQ to Entities Example

Following is the result of 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 required data from database.

 

PREVIOUS

LINQ to Dataset
 

NEXT

LINQ to XML  
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User