How To: Populate a List of Objects from a DataSet Using LINQ

Many of us use a list of objects between methods, classes, layers, etc. Sometimes the origin of this generic object list is a DataSet that was populated from a SQL stored procedure. To populate the list of objects from the DataSet, one would have to iterate through each row in the designated DataTable within the DataSet and assign values to the object's properties. It's not a bad method at all, and at least we are talking about working with objects in the first place (and not transporting raw ADO.NET objects through all layers), so we are already in a good place. Here is what that typical code described above might look like:

Dim CustomerData As New DataSet
'-->Get the data from the database; omitting code for brevity

'Create a new list of objects from type Customer
Dim CustomerList As New List(Of Customer)
Dim MyCustomer As Customer = Nothing

'Iterate through each of the rows in the DataTable
For Each dr As DataRow In CustomerData.Tables(0).Rows
   'Instantiate the Customer intance for the current iteration
   MyCustomer = New Customer()

   'Add the non null values to this object's properties
   If Not IsDBNull(dr("ID")) Then MyCustomer.ID = Integer.Parse(dr("ID"))
   If Not IsDBNull(dr("FirstName")) Then MyCustomer.FirstName = dr("FirstName").ToString()
   If Not IsDBNull(dr("LastName")) Then MyCustomer.LastName = dr("LastName").ToString()

   'Add the inidividual object to the list
   CustomerList.Add(MyCustomer)
Next

'Return the list if needed
Return CustomerList

However we can use LINQ to streamline this process a bit. The 'System.Data.DataSet' extensions namespace exposes methods that allow us to work with LINQ to DataSets in .NET. The issue is that a DataSet or better a DataTable in its raw form does not implement the 'IEnumerable(Of T)' or 'IQueryable' interfaces required to work with LINQ. By calling the 'AsEnumerable' method from this namespace on the DataTable in the FROM clause of a query, we can use a DataTable as a LINQ source.

With the DataTable as the source of our LINQ query, we can now select into a list of objects (or an anonymous type for local use only as opposed to a fully qualified class). The example of code below details how to do this on a simple 'Customer' class. Notice that I check to make sure the value returned from the database is not null before assignment to the property using the VB.NET ternary 'If()' operator.

Dim CustomerData As New DataSet
'-->Get the data from the database; omitting code for brevity

'Use LINQ to query the DataTable data into the 'CustomerList' row collection
Dim CustomerCollection = From MyCustomer In CustomerData.Tables(0).AsEnumerable() _
      Select New Customer With { _
      .ID = If(Not IsDBNull(MyCustomer.Field(Of Integer)("ID")), MyCustomer.Field(Of Integer)("ID"), 0), _
      .FirstName = If(Not IsDBNull(MyCustomer.Field(Of String)("FirstName")), MyCustomer.Field(Of String)("FirstName"), String.Empty), _
      .LastName = If(Not IsDBNull(MyCustomer.Field(Of String)("LastName")), MyCustomer.Field(Of String)("LastName"), String.Empty)}

'Return the list if needed
Return CustomerCollection.ToList()

That's just a taste of how you can use the power of LINQ with some traditional ADO.NET objects in .NET. It shows how you can begin migrating some older existing .NET code with some of the newer technologies and methods available.