How to convert DataTable to List<object>?

Question

I have a strongly typed class PersonExport. I initially get data into a DataTable and call the following method on the DataTable to convert it to List<PersonExport>:

public static List<T> ConvertToList<T>(DataTable dt, out string message)
    {
        message = string.Empty;
        var list = new List<T>();


        try
        {
            var columnNames = dt.Columns.Cast<DataColumn>()
            .Select(c => c.ColumnName)
            .ToList();

            var properties = typeof(T).GetProperties();

            list = dt.AsEnumerable().Select(row =>
            {
                var objT = Activator.CreateInstance<T>();

                foreach (var pro in properties)
                {
                    if (columnNames.Contains(pro.Name))
                    {
                        var value = row[pro.Name];
                        var typeName = value.GetType().FullName;

                        if (typeName == "MySql.Data.Types.MySqlDateTime")
                        {

                            var mySqlDateTime = (MySqlDateTime) value;
                            if (mySqlDateTime.IsValidDateTime)
                            {
                                value = Convert.ToDateTime(mySqlDateTime.ToString());
                                pro.SetValue(objT, value, null);
                            }
                        }
                        else
                        {
                            pro.SetValue(objT, row.IsNull(pro.Name) ? null : value, null);
                        }
                    }
                }

                return objT;
            }).ToList();

        }
        catch (Exception ex)
        {
            message = (ex.InnerException != null) ? ex.InnerException.Message : ex.Message;
        }

        return list;
    }

However, once I start removing columns from the DataTable returned, it no longer works because the columns in the DataTable don't match up with the properties in the PersonExport list.

I am eventually using the exported list here to export to excel, but it is not working since I have modified by DataTable and it can't Deserialize into a List<PersonExport>:

//Trying to get data into List<object>
List<object> persons = GetPersonExport(query, out message);
var exportData = new Dictionary<string, List<object>> { { "xldata", persons} };

//Deserialize to List<object> to export
var persons = JsonConvert.DeserializeObject<List<object>>(args["xldata"]);

The above line just returns a List of empty objects.

A few things got me thinking, but I am wondering what might be the best approach. I am using the EPPLUS library to export data to excel and it has the option of hiding columns, so would it be better to just export the whole object and hide columns you don't want, this way you avoid the anonymous type or what I can do is still get the whole object, but then convert it to a DataTable and then remove the columns? Thoughts?

Solution

All that you want is:

public IEnumerable<object> GetListOfObject()
{
    foreach (var prod in TenMostExpensiveProducts().Tables[0].AsEnumerable())
    {
        yield return prod;
    }
}

Or:

TenMostExpensiveProducts().Tables[0].AsEnumerable().Select (x => x).ToList<object>()

But you can get it work more elegant via linq like this:

from prod in TenMostExpensiveProducts().Tables[0].AsEnumerable()
where prod.Field<decimal>("UnitPrice") > 62.500M
select prod

Or like this (AsDynamic is called directly on DataSet):

TenMostExpensiveProducts().AsDynamic().Where (x => x.UnitPrice > 62.500M)

I prefer the last approach while is is the most flexible. P.S.: Don't forget to connect System.Data.DataSetExtensions.dll reference