Follow @PDSAInc February 2013 - Posts - Paul Sheriff's Blog for the Real World

Paul Sheriff's Blog for the Real World

This blog is to share my tips and tricks garnered over 25+ years in the IT industry

Paul's Favorites

February 2013 - Posts

Creating Collections of Entity Objects using LINQ and Field Method

Let’s now look at another advantage of using a DataTable. A lot of developers today are used to using LINQ. After loading data into a DataTable you can iterate using a foreach statement, or you can use LINQ to create a collection of entity objects. The DataRow class has an extension method called Field that allows you to check the data and return either a null or the real data value. Of course this means you have to use Nullable types for your properties in your class. Below is the definition of a Product class that uses all Nullable types.

C#
public class Product
{
  public int? ProductId { get; set; }
  public string ProductName { get; set; }
  public DateTime? IntroductionDate { get; set; }
  public decimal? Cost { get; set; }
  public decimal? Price { get; set; }
  public bool? IsDiscontinued { get; set; }   
}

Visual Basic
Public Class Product
  Public Property ProductId() AsNullable(Of Integer)
  Public Property ProductName() As String
  Public Property IntroductionDate() As Nullable(Of DateTime)
  Public Property Cost() As Nullable(Of Decimal)
  Public Property Price() As Nullable(Of Decimal)
  Public Property IsDiscontinued() As Nullable(Of Boolean)
End Class

Reading Data into a Collection using LINQ

Field is a generic method that allows you to pass in the data type you wish to convert the data to if the data within the column is not null. In the code below you can see an example of filling a DataTable with product data, then iterating over the data table using a LINQ query. As you create each new instance of the product class use the Field method to retrieve the data and place it into your nullable property.

Because the Field method is an extension method you will need to add a reference to the System.Data.DataSetExtensions.dll in your project. You could use this method to load properties of a class that does not use Nullable types, but you could not have any null values in your table. This is not a very likely scenario, so you probably want to stick with nullable types.

C#
public List<Product> GetProducts()
{
  DataTable dt = new DataTable();
  SqlDataAdapter da = null;
     
  da = new SqlDataAdapter("SELECT * FROM Product",
                          AppSettings.Instance.ConnectString);

  da.Fill(dt);
     
  var query = (from dr in dt.AsEnumerable()
       select new Product
       {
        ProductId = dr.Field<int?>("ProductId"),
        ProductName = dr.Field<string>("ProductName"),
        IntroductionDate =
             dr.Field<DateTime?>("IntroductionDate"),
        Cost = dr.Field<decimal?>("Cost"),
        Price = dr.Field<decimal?>("Price"),
        IsDiscontinued = dr.Field<bool?>("IsDiscontinued")
       });

  return query.ToList();
}


Visual Basic
Public Function GetProducts() As List(Of Product)
  Dim dt As New DataTable()
  Dim da As SqlDataAdapter = Nothing

  da = New SqlDataAdapter("SELECT * FROM Product", _
                  AppSettings.Instance.ConnectString)

  da.Fill(dt)

  Dim query = (From dr In dt.AsEnumerable() _
     Select New Product() With { _
      .ProductId = dr.Field(Of Nullable(Of _
          Integer))("ProductId"), _
      .ProductName = dr.Field(Of String)("ProductName"), _
      .IntroductionDate = dr.Field(Of Nullable(Of _
          DateTime))("IntroductionDate"), _
      .Cost = dr.Field(Of Nullable(Of Decimal))("Cost"), _
      .Price = dr.Field(Of Nullable(Of Decimal))("Price"), _
      .IsDiscontinued = dr.Field(Of Nullable(Of _
          Boolean))("IsDiscontinued") _
    })

  Return query.ToList()
End Function

Summary

In this blog post you learned how to use the Field method on the DataRow class in ADO.NET to help you load up a collection of product objects. When using the Field method you should use .NET nullable data types.

NOTE: You can download the sample code for this article by visiting my website at http://www.pdsa.com/downloads. Select "Tips & Tricks", then select "Creating Collections of Entity Objects using LINQ and Field Method" from the drop down list.

Creating Collections of Entity Objects using LINQ

As discussed in my last two blog posts you have a variety of ways to create collections of Entity classes. Using a DataSet or DataTable is a little slower than using a DataReader, but in most cases the difference is in milliseconds so in a real world app this difference would not be a killer. For instance, in my sample data I was loading 6,261 records from the Product table discussed in the last blog post and it took 45 milliseconds on average to load those records into an entity collection using a DataTable. It took only 30 milliseconds on average to load the same entity collection using a DataReader. The rendering of that data would probably take longer than that, so you can choose which one you wish to use.

Let's now look at one advantage of using a DataTable. A lot of developers today use LINQ. After loading data into a DataTable you can iterate using a foreach statement, or you can use LINQ to create a collection of entity objects.

Below is a typical entity class that models a Product table in a database:

C#
public class Product
{
  public int ProductId { get; set; }
  public string ProductName { get; set; }
  public DateTime IntroductionDate { get; set; }
  public decimal Cost { get; set; }
  public decimal Price { get; set; }
  public bool IsDiscontinued { get; set; }   
}

Visual Basic
Public Class Product
  Public Property ProductId() Integer
  Public Property ProductName() As String
  Public Property IntroductionDate() As DateTime
  Public Property Cost() As Decimal
  Public Property Price() As Decimal
  Public Property IsDiscontinued() As Boolean
End Class

Reading Data into a Collection using LINQ

Let's now use a LINQ query to iterate over the collection of DataRow objects within a DataTable. In the code below you can see the use of the SqlDataAdapter to fill a DataTable. You now use the AsEnumerable() method on the DataTable to turn the collection of DataRow objects into an enumerable list that can be used in a LINQ statement. In the LINQ statement you create the new Product object use the same DataConvert class to check for valid data and convert that data into a value that can be stored into each property.

C#
public List<Product> GetProducts()
{
  DataTable dt = new DataTable();
  SqlDataAdapter da = null;

  da = new SqlDataAdapter("SELECT * FROM Product",
                          AppSettings.Instance.ConnectString);

  da.Fill(dt);

  var query =
     (from dr in dt.AsEnumerable()
      select new Product
      {
        ProductId = Convert.ToInt32(dr["ProductId"]),
        ProductName = dr["ProductName"].ToString(),
        IntroductionDate =
              DataConvert.ConvertTo<DateTime>(
                dr["IntroductionDate"], default(DateTime)),
        Cost = DataConvert.ConvertTo<decimal>(
                dr["Cost"], default(decimal)),
        Price = DataConvert.ConvertTo<decimal>(
                dr["Price"], default(decimal)),
        IsDiscontinued = DataConvert.ConvertTo<bool>(
                dr["IsDiscontinued"], default(bool))
       });

  return query.ToList();
}


Visual Basic
Public Function GetProducts() As List(Of Product)
  Dim dt As New DataTable()
  Dim da As SqlDataAdapter = Nothing

  da = New SqlDataAdapter("SELECT * FROM Product", _
              AppSettings.Instance.ConnectString)

  da.Fill(dt)

  Dim query = (From dr In dt.AsEnumerable() _
               Select New Product() With { _
  .ProductId = Convert.ToInt32(dr("ProductId")), _
  .ProductName = dr("ProductName").ToString(), _
  .IntroductionDate = DataConvert.ConvertTo(Of _
    DateTime)(dr("IntroductionDate"), DateTime.MinValue), _
  .Cost = DataConvert.ConvertTo(Of Decimal)(dr("Cost"), 0D), _
  .Price = DataConvert.ConvertTo(Of Decimal) _
             (dr("Price"), 0D), _
  .IsDiscontinued = DataConvert.ConvertTo(Of _
        Boolean)(dr("IsDiscontinued"), False) _
  })

  Return query.ToList()
End Function

Summary

In this blog post you learned how to create an entity class and a collection of entity classes using LINQ. When using a DataTable filled with data, LINQ allows you to write more expressive code to create a collection of entities compared to a foreach loop.

NOTE: You can download the sample code for this article by visiting my website at http://www.pdsa.com/downloads. Select "Tips & Tricks", then select "Creating Entity Collections using LINQ" from the drop down list.

Posted: Feb 11 2013, 07:17 AM by psheriff | with 3 comment(s)
Filed under: , ,
More Posts