Different ways to populate a dropdown list in ADO.NET Entity Framework

One of the most common tasks in web-application development is to fill a dropdown list with values from a table in the database or from a collection. With ASP.Net the various approaches are well-documented. What I wanted to show here is the various ways to populate a dropdown list when working with ADO.Net Entity Framework(using EntitySQL). So let’s get right to the code!

First let us assume that you have created an Entity Data Model (.edmx file), named it as “MyEntities”, the model name being “MyModel” and one of the entities added is the “Owners” (mapped to the “OwnersTable” table in the database) as shown below:












There will be the following entry in the web.config:

<add name="MyEntities" connectionString="metadata=res://;provider=System.Data.SqlClient;provider connection string="Data Source=MyDataSource;Initial Catalog=MyCatalog;Integrated Security=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />

Now let us assume in the page MyTestPage.aspx we have the following dropdown list control which we want to populate with the records in the Owner on the page load:

<asp:DropDownList runat="server" ID="ddlOwners" AutoPostBack="false"></asp:DropDownList>

Let us create a private procedure named PopulateOwners which will be invoked from the Page_Load event of MyTestPage.aspx page.

Method 1
This is the most straight forward way of populating a dropdown using the DataSource and DataBind properties of the dropdown list control. . _ent.Owners() returns an “ObjectQuery” which we assign to the ‘DataSource’ property of the dropdown and then invoke ‘DataBind’.

Public Sub PopulateOwners()
  Dim _ent As MyEntities = New MyEntities
  ddlOwners.DataSource = bsrtent.Owners()
  ddlOwners.DataTextField = "owner_name"
  ddlOwners.DataValueField = "owner_id"
  ddlOwners.DataBind()
End Sub


Method 2
Another way to populate the dropdown list is to loop through the Owners object ‘collection’. _ent.Owners() returns an “ObjectQuery” on which we use and ‘Extension’ function AsEnumerable which then returns the input typed as System.Collections.Generic.IEnumerable(Of T). We can then use an enumerator to loop through the collection and populate the dropdown list.

Public Sub PopulateOwners()
  Dim _ent As MyEntities = New MyEntities
  Dim en As IEnumerator = _ent.Owners().AsEnumerable.GetEnumerator()
    While en.MoveNext
      Dim o As Owners = DirectCast(en.Current, Owners)
      ddlOwners.Items.Add(New ListItem(o.owner_name, o.owner_id))
    End While
End Sub


Method 3
Another approach is to create a LINQ query against the “Owners” entity which returns an IQueryable object against which we can loop through and populate the dropdown list.
In this example we also see how to add a ‘blank item’ or a default entry to the list e.g. the very first value in the list should display “Select an Owner”. The same can be applied to Method 2 but not with Method 1

Public Sub PopulateOwners()
  Dim _ent As MyEntities = New MyEntities
  ddlOwners.Items.Add(New ListItem("Select an Owner", ""))
  Dim qry = From a As Owners In _ent.Owners Order By a.owner_name
    If qry IsNot Nothing AndAlso qry.Count > 0 Then
      For Each o As Owners In qry
        ddlOwners.Items.Add(New ListItem(o.owner_name, o.owner_id))
      Next
    End If
End Sub

Method 4
In this method I’ll demosntrate how to make use of ‘ObjectDataSource’ and ‘Entity Framework’ together in order to populate a dropdown list. Poluating a list is just one of the many things you can do with this approach.
First I’ll need a Data Access Layer (DAL) class which will interact with my Entity Model. Let’s name it MyEntityDAL. . It contains a contructor which isntantiates the MyEntities object and a public Owners() function that queries the Owners entity in the Entity Model, retreives all the records and returns it in form of a DataTable. In this example I am returning a DataTable but as you can see you can return any type of ‘collection’ e.g. IList, DataSet, IQueryable etc.

Public Class MyEntityDAL
    Private _ent As MyEntities

    Public Sub New()
      _ent = New MyEntities
    End Sub

Public Function Owners() As DataTable
        Dim results As New DataTable
        results.Columns.Add(New DataColumn("OwnerID", GetType(String)))
        results.Columns.Add(New DataColumn("OwnerName", GetType(String)))
        'Add an empty row
        Dim _emptyrow As DataRow = results.NewRow()
        _emptyrow.Item("OwnerID") = ""
        _emptyrow.Item("OwnerName") = "Select an Owner"
        results.Rows.Add(_emptyrow)
        Dim qry = From a As Owners In _ent.Owners Select a.owner_id, a.owner_name
        If qry.Count > 0 Then
            For Each rec In qry
                Dim dr As DataRow = results.NewRow()
                dr.Item("OwnerId") = rec.owner_id.ToString
                dr.Item("OwnerName") = rec.owner_name
                results.Rows.Add(dr)
            Next
            results.AcceptChanges()
        End If
        Return results
    End Function
End Class

Now that we have our DAL ready lets add an ObjectDataSource to our ASPX page.

<asp:ObjectDataSource ID="odsOwners" runat="server"  SelectMethod="Owners" TypeName="MyEntityDAL">
</asp:ObjectDataSource>

Next let’s re-write our owner dropdown lsit control as follows:

<asp:DropDownList runat="server" ID="ddlOwners" AutoPostBack="true" DataSourceID=" odsOwners" DataTextField="OwnerName" DataValueField="OwnerID">
</asp:DropDownList>

And that’s it! Now we are able to populate the dropdown list declartively. If you want you can event modify Method 1 to make use of the MyEntityDAL object and set the DataSource property to the Owners() function.