Filling an object from a DataReader with LINQ using DataContext.Translate by ThinqLinq

Filling an object from a DataReader with LINQ using DataContext.Translate

One of the key things that LINQ to SQL does for us is offers a quick way to fill a set of objects with data from a database. Typically this is done by setting up some mapping and calling the GetTable method on the DataContext. There are cases, particularly when you already have an infrastructure set-up to populate objects using a DbDataReader, where it would be nice if you could just populate the columns without the need to set-up a mapping.

The DataContext has a little known method called Translate which can take a DBDataReader as a parameter and fill an IEnumerable list of objects without the need for mapping structures. To use it, specify the type you want to populate and pass the instance of the reader and let it do it's magic. Here's a sample:

Using cn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)
  Using cmd As New SqlClient.SqlCommand("SELECT * FROM Region", cn)
    Dim reader = cmd.ExecuteReader()
    Using nwind As New NorthwindDataContext

      Dim RegionList = nwind.Translate(Of Region)(reader)

      For Each item In RegionList
        Console.WriteLine(" {0}: {1}", item.RegionId, item.RegionDescription)
End Using
End Using
End Using

In this case, we tell the context to translate the resultset of the reader into a generic IEnumerable(Of Region). There are number of caveats to keep in mind here:

  1. The column name in the result set must correspond to the property name.The translation is quite forgiving however. It is not case sensitive.
  2. If you have a row in the result set but no corresponding property, that row will be ignored (no exception will be thrown).
  3. If you have a property in your object with no corresponding row in the result set, that property's value will not be set.
  4. The translation must be done to a known type. You can not project into an anonymous type. You can return an object or simple IEnumerable if the type is not known and then use reflection to work with the results.
  5. Since the translate method returns an IEnumerable, make sure not to close the connection or the reader before iterating over the results.

The Translate method supports 3 overloads as follows:

    Public FunctionTranslate(Of TResult)(ByVal reader As DbDataReader) As IEnumerable(Of TResult)
    Public Function Translate(ByVal reader As DbDataReader) As IMultipleResults
    Public Function Translate(ByVal elementType As Type, ByVal reader As DbDataReader) As IEnumerable

Translate does not require any mappings to be set. It simply sets the public property values based on the column names. If you have business logic in your Property Set implementations, that logic will run. As far as I can see, there is no way to instruct Translate to use the private storage field instead of the public property.

Posted on - Comment
Categories: VB Dev Center - LINQ - VB -
comments powered by Disqus