Efficient DataTable to IEnumerable<T> Conversion in C#: Best Practices and Techniques

Introduction

In C# programming, converting a DataTable to an IEnumerable<T> where T is a custom type is a common task, especially when dealing with database results or legacy data. The traditional approach often involves creating an intermediate List<T>, but there are more efficient methods available. This article explores the best practices for this conversion, focusing on the use of the yield keyword and LINQ extensions.

Using the Yield Keyword for Efficient Conversion

The yield keyword in C# enables the implementation of iterators that return elements one at a time, without the need to store the entire collection in memory. This is particularly useful for large datasets. Based on the accepted answer, here is an optimized method:

private IEnumerable<TankReading> ConvertToTankReadings(DataTable dataTable)
{
    foreach (DataRow row in dataTable.Rows)
    {
        yield return new TankReading
        {
            TankReadingsID = Convert.ToInt32(row["TRReadingsID"]),
            TankID = Convert.ToInt32(row["TankID"]),
            ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]),
            ReadingFeet = Convert.ToInt32(row["ReadingFeet"]),
            ReadingInches = Convert.ToInt32(row["ReadingInches"]),
            MaterialNumber = row["MaterialNumber"].ToString(),
            EnteredBy = row["EnteredBy"].ToString(),
            ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]),
            MaterialID = Convert.ToInt32(row["MaterialID"]),
            Submitted = Convert.ToBoolean(row["Submitted"])
        };
    }
}

This approach avoids the creation of a List<T> and directly returns an IEnumerable<T>, enabling deferred execution and reducing memory overhead. Additionally, List<T> is already an IEnumerable<T>, so calling AsEnumerable() is unnecessary.

Alternative Method Using LINQ Select

As a supplementary method, LINQ can be used to achieve the same result. The DataTableExtensions.AsEnumerable() method converts a DataTable to an IEnumerable<DataRow>, which can then be projected to the custom type using Select.

private IEnumerable<TankReading> ConvertToTankReadings(DataTable dataTable)
{
    return dataTable.AsEnumerable().Select(row => new TankReading
    {
        TankReadingsID = Convert.ToInt32(row["TRReadingsID"]),
        TankID = Convert.ToInt32(row["TankID"]),
        ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]),
        ReadingFeet = Convert.ToInt32(row["ReadingFeet"]),
        ReadingInches = Convert.ToInt32(row["ReadingInches"]),
        MaterialNumber = row["MaterialNumber"].ToString(),
        EnteredBy = row["EnteredBy"].ToString(),
        ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]),
        MaterialID = Convert.ToInt32(row["MaterialID"]),
        Submitted = Convert.ToBoolean(row["Submitted"])
    });
}

This method is concise and leverages the power of LINQ, but it may involve additional overhead due to the lambda expression and projection.

Comparative Analysis

Both methods have their merits. The yield-based approach offers better control over iteration and is more memory-efficient for large datasets, as it uses lazy evaluation. The LINQ method is more expressive and aligns with functional programming paradigms. In terms of performance, the yield method might be slightly faster in some scenarios due to reduced allocations.

Conclusion

For converting a DataTable to an IEnumerable<T> in C#, using the yield keyword is recommended for its efficiency and clarity. It eliminates the need for intermediate collections and supports deferred execution. The LINQ method serves as a valid alternative for those preferring a more declarative style. Developers should choose based on specific requirements such as performance, readability, and team coding standards.