ASP.NET DataBinding with LINQ to Entities

Tuesday, August 12 2008

(Note: Originally posted on using Entity Framework Beta 3)

In this tutorial, you will create a read-only ASP.NET Web page which uses LINQ to Entities as the source for data-bound controls. The website will have a drop down list of Customers. When a customer is selected, the customer's orders will display in a grid.

[This tutorial was written using Entity Framework Beta 3. The RTM of Entity Framework will features an EntityDataSource for additional scenarios.]    

  1. Create a new Web Site. [For help with this task see MSDN Topic: Walkthrough: Creating a New ASP.NET WebSite]

  2. Add an Entity Data Model to the project. [For help with this task, see Creating an ADO.NET Entity Framework Entity Data Model]
    Alternatively, if you have created an EDM in a separate project, you can add a reference to that project. 

Add data-binding controls to the default web page

  1. Click the tab for Default.aspx in the Document windows to view the empty default web page.

  2. Type "Adventure Works Customer Orders" directly onto the the page and hit the enter key twice.

  3. In the Toolbox, select the DropDownList control (in the Standard section). Drag and drop it onto the web page.

  4. Check Enable AutoPostBack in the DropDownList Tasks menu

  5. Click a blank area on the web page to make the DropDownList's Tasks Menu close.

  6. In the Toolbox, select the GridView control (in the Data section). Drag and drop it onto the web page.

  7. Click a blank area on the web page to make the GridView Tasks Menu close.


Populate the DrowDownList with Customer Name

  1. Double click on a blank area of the web page.
  2. This will open up the code view of the window and create a Page_Load event.
  3. Above the Page Load event add the following code

    VB:  Dim aw As AWModel.AWEntities
    C#: awModel.awEntities aw;
  4. Inside of the Page Load event add the following code

    aw = New AWModel.AWEntities
    If Not IsPostBack Then
    Dim custQuery = From cust In aw.Customers _
                     Where cust.SalesOrderHeaders.Any _
                     Select cust.CustomerID, cust.CompanyName _
                     Order By CompanyName
    With DropDownList1
       .DataValueField = "CustomerID"
       .DataTextField = "CompanyName"
       .DataSource = custQuery
    End With
    End If

    aw = new awModel.awEntities();
    var custQuery = from cust in aw.Customers
                     where cust.SalesOrderHeaders.Any(soh => soh.TotalDue>0)
                     orderby cust.CompanyName
                     select new { cust.CustomerID, cust.CompanyName };
    DropDownList1.DataValueField = "CustomerID";
    DropDownList1.DataTextField = "CompanyName";
    DropDownList1.DataSource = custQuery;

  5. Press Ctrl-F5 to see what you have created so far


Populate the Gridview with Orders from the selected Customer

  1. Return to the design view of the page
  2. Double-click the DropDownList
  3. This will open up the code view and automatically add a SelectedIndexChanged event for the DropDownList

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles
    End Sub

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
  4. Replace with the following code:

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
      Dim ordQuery = From ord In aw.SalesOrderHeaders _
                   Where ord.Customer.CustomerID = DropDownList1.SelectedValue _
                   Order By ord.OrderDate _
                   Select ord.SalesOrderNumber, ord.OrderDate, ord.TotalDue
      GridView1.DataSource = ordQuery
    End Sub

    //C# is pickier about the SelectedValue being returned as a string
    int custID;
    if (int.TryParse(DropDownList1.SelectedValue, out custID))
      var ordQuery = from ord in aw.SalesOrderHeaders
                     where ord.Customer.CustomerID == custID
                     orderby ord.OrderDate
                     select new { ord.SalesOrderNumber, ord.OrderDate, ord.TotalDue };
      GridView1.DataSource = ordQuery;
  5. Press Ctrl-F5 to run the page again
  6. Change the customers in the drop down list to see different sales order appear



Notice the default format OrderDate and TotalDue . This cannot be changed in the LINQ query. The GridView is responsible for the formatting.

Additionally, the Visual Basic solution forces the returned properties to be in alphabetical order. This is specific to VB.

Formatting the data in the gridview

  1. Select the Default.aspx page in the Document Windows.
  2. Switch to Source View by clicking Source at the bottom of the design window.
  3. Locate the <asp:GridView>
  4. Replace the highlighted markup with the following:

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="300px">
           <asp:BoundField DataField="SalesOrderNumber" HeaderText="Order #" />
           <asp:BoundField DataField="OrderDate" DataFormatString="{0:d}" HeaderText="Order Date" />
           <asp:BoundField DataField="TotalDue" DataFormatString="{0:c}" HeaderText="Total"/>

  5. Press Ctrl-F5 to see the results