DataBind GridView with Entities and LINQ
A simple article for understanding LINQ with Entities for data binding a GridView. While the main objective is not about the GridView data binding, the goal is to find how we can use the LINQ effectively along with Entities to bind data. Why I have not decided not to use DataTable with LINQ to bind is, because I would like to stay away from SQL queries completely.
Adding ADO.NET Entity Data Model
As we planned to go for entity based approach, the model has to be created. To be simpler I always prefer to work with Northwind Database. This time we select the Category table for our sample.
1) Right Click the website and add new Item
2) Select the Categories as Data in the left side pane
3) Select the ADO.NET Entity Data Model.
4) Name the Model appropriately and click Add.
Configuring the Created Entity Model
As we know the Data model has to be configured along with relational data if applicable. In our case we are not going to deal with very complex data rather we are going to try as simple as possible. So the Categories table is what we have opted for. Now after the above steps immediately you will be asked to configure the Model with the wizard named Entity Data Model Wizard.
1) Select the Generate From Database option and go next
2) Select an appropriate connection from the list, if not create new. And go next
3) Check the table from the list, in our case it is Categories.
4) Click finish to finish the configuration
You can visually see the relations if you have added any tables with references. In our case it is very simple so you will see the name of table and all the field names.
Using LINQ to query the data
As planned we are not going to write the SQL queries to hit the Database. But it is going to be handled with Entity Framework and LINQ. While LINQ is not a replacement of SQL queries but it may look a bit like them. The keywords hold same meaning for FROM, SELECT, WHERE. And once you know how to use LINQ to query a set of data from group of objects, (may be a collection, ArrayList, DataTable, or GenericList.), it is much easier to work.
Source Code
Markup(ASPX)
<%@ Page Language=
"VB" AutoEventWireup=
"false" CodeFile=
"Default.aspx.vb" Inherits=
"_Default" %>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns=
"http://www.w3.org/1999/xhtml">
<head id=
"Head1" runat=
"server">
<title>Untitled Page</title>
</head>
<body>
<form id=
"form1" runat=
"server">
<asp:GridView runat=
"server" AutoGenerateColumns=
"false" AllowSorting=
"true" ID=
"GridView1" ShowFooter=
"true" DataKeyNames=
"CategoryID">
<Columns>
<asp:BoundField DataField=
"CategoryName" HeaderText=
"CategoryName" />
<asp:BoundField DataField=
"Description" HeaderText=
"Description" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
Code-Behind(ASPX.VB)
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Imports System.Linq
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(
ByVal sender
As Object, _
ByVal e
As System.EventArgs)
Handles Me.Load
If (Not IsPostBack)
Then LoadGrid()
End If End Sub Protected Sub LoadGrid()
Dim EntityData =
New NorthwindModel.Entities
Dim EntityQuery = From Category In EntityData.Categories _
Where Category.CategoryName.Contains(
"n") _
Select _
Category.CategoryID, _
Category.CategoryName, _
Category.Description
GridView1.DataSource = EntityQuery
GridView1.DataBind()
End Sub
End Class