Convert DataTable to IEnumerable<T> in ASP.NET Core 2.0

Question

I need to generate an 'IEnumerable from a DataTable that I receive as an input from another system. The following code worked in ASP.NET 4.6.1.

public static IEnumerable<UserAssignmentDto> StaffAssignmentsUsingStoredProcedure(System.Data.DataTable dataTable)
    {
        var data = dataTable.AsEnumerable().Select(row =>
            new UserAssignmentDto
            {
                Id = ((string)row["AssignmentNumber"]),
                Position = (string) row["EsrPositionTitle"],

            });

        return data;
    }

However, 'DataTable' no longer contains a definition for 'AsEnumerable' in ASP.NET Core 2.0.

What would be the most efficient way to generate the 'IEnumerable' that I need?

Answer 1

The correct nuget package for .net core 2 is System.Data.DataSetExtensions

Answer 2

Here is a generic AsEnumerable extension function to mimic what the classic AsEnumerable() did, return an Enumerable collection of DataRows from a DataTable. Can be useful for someone that wishes to mimimize the amount of refactoring needed when porting their code to .net core.

    public static IEnumerable<DataRow> AsEnumerable(this DataTable table)
    {
        for (int i = 0; i < table.Rows.Count; i++)
        {
            yield return table.Rows[i];
        }
    }

Answer 3

Not about most efficient but for an alternative, you can use the Select method:

DataRow[] rows= dataTable.Select();

And now you have an IEnumerable of rows. This method may help someone:

public static List<T> ConvertDataTableToGenericList<T>(DataTable dt)
{
     var columnNames = dt.Columns.Cast<DataColumn>()
            .Select(c => c.ColumnName)
            .ToList();

     var properties = typeof(T).GetProperties();
     DataRow[] rows= dt.Select();
     return rows.Select(row =>
     {
          var objT = Activator.CreateInstance<T>();
          foreach (var pro in properties)
          {
              if (columnNames.Contains(pro.Name))
                   pro.SetValue(objT, row[pro.Name]);
          }

          return objT;
     }).ToList();
}

Answer 4

One of the most efficient things you can do is write the code for the iteration yourself, using a for loop instead of LINQ, just iterate over the DataTable rows and build/hydrate the IEnumerable<UserAssignmentDto> method return value "by hand".

Since there DataTable does not implement an Enumerator in .NET Core 2.0 you will need to use a "regular" for loop to iterate over the rows. You cannot use foreach because DataTable has not implemented IEnumerable in .NET Core 2.0.

public static IEnumerable<UserAssignmentDto> StaffAssignmentsUsingStoredProcedure(System.Data.DataTable dataTable)
{
    var retList = new List<UserAssignmentDto>();

    for(int i = 0; i < dataTable.Rows.Count; i++)
    {
          var row = dataTable.Rows[i];

          var temp = new UserAssignmentDto(){
              Id = row["AssignmentNumber"],
              Position = row["EsrPositionTitle"]
          };

          retList.Add(temp);     
    }

    return retList;
}

Answer 5

AsEnumerable() does exist in .NET Core 2.0, you just need to add a reference to System.Data.DataExtensions as mentioned in the answer to a similar question. LINQ to DataSet, DataTable.AsEnumerable() not recognized