Generic Method to convert datatable to list

Question

I'm using below code:

 public static List<T> ConvertToList1<T>(DataTable dt)
        {
            var columnNames = dt.Columns.Cast<DataColumn>()
                    .Select(c => c.ColumnName)
                    .ToList();
            var properties = typeof(T).GetProperties();
            return dt.AsEnumerable().Select(row =>
            {
                T objT1 = Activator.CreateInstance<T>();
                foreach (var pro in properties)
                {
                    if (columnNames.Contains(pro.Name))
                    {
                        PropertyInfo? pI = objT.GetType().GetProperty(pro.Name);
                        pro.SetValue(objT, row[pro.Name] == DBNull.Value ? null : Convert.ChangeType(row[pro.Name], pI.PropertyType));

                    }
                }
                return objT1;
            }).ToList();
        }

But I'm getting error for decimal field having null values.

Invalid cast from 'System.Decimal' to 'System.Nullable`1[[System.Decimal, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral

 public class SampleModel
    {
        public Guid Id { get; set; }
        public Guid ProjectId { get; set; }
        public string? Code { get; set; } = null!;
        public string? Description { get; set; }
        public decimal? Quantity1 { get; set; }
        public decimal? Quantity2 { get; set; }
    }

Can anyone suggest how to fix this?

Does this answer your question? [Set property Nullable<> by reflection](https://stackoverflow.com/questions/1488706/set-property-nullable-by-reflection) — Ryan Wilson, Oct 03 '22 at 13:15
I would use [this answer](https://stackoverflow.com/a/3531824/2557128). — NetMage, Oct 06 '22 at 20:05

Jeroen van Langen · Answer 1 · 2022-10-03T13:37:53.293

1

Um, reading again... You mean that a non-null decimal value in the row cannot be assigned to a nullable decimal? Or did you mean a dbnull that cannot be assigned to the nullable decimal?

You can use the DataRow.IsNull to check if the value is dbnull. In that case you just skip it as the nullable already has default null.

// <CODE SNIP />

if (columnNames.Contains(pro.Name))
{
    // if the value was null, just skip it.
    if(!row.IsNull(pro.Name))
    {
        PropertyInfo? pI = objT.GetType().GetProperty(pro.Name);
        pro.SetValue(objT, Convert.ChangeType(row[pro.Name], pI.PropertyType));
    }
}

edited Oct 03 '22 at 13:37

answered Oct 03 '22 at 13:31

Jeroen van Langen

21,446
3
42
57

The former - `Convert.ChangeType` fails to convert `decimal` to `decimal?`. `null` is not involved in the issue. – NetMage Oct 06 '22 at 20:48

Brett · Answer 2 · 2022-10-04T12:59:28.083

I've had a play. The link from @Ryan Wilson was very useful.

In short, where you have a nullable type, you want to cast to the underlying type. Consider:

decimal? target;
decimal source = 42;
target = source;

This is perfectly reasonable code. We can assign a decimal value to a decimal? variable.

Using Nullable.GetUnderlyingType(myType) returns a type or null if myType is not nullable. Extending the above snippet:

var underlying1 = Nullable.GetUnderlyingType(target.GetType());
var underlying2 = Nullable.GetUnderlyingType(source.GetType());

Here, underlying2 is null, and underlying1 is decimal.

To put this into practice, then, we slightly alter the innermost part of your conversion method to become:

PropertyInfo pI = objT1.GetType().GetProperty(pro.Name);
var targetType = Nullable.GetUnderlyingType(pI.PropertyType) ?? pI.PropertyType;
pro.SetValue(objT1, row[pro.Name] == DBNull.Value 
  ? null
  : Convert.ChangeType(row[pro.Name], targetType));

score -1 · Answer 3 · answered Oct 03 '22 at 13:42

Try following :

       public static List<SampleModel> ConvertToList1<T>(DataTable dt)
        {
            List<SampleModel> results = dt.AsEnumerable().Select(x => new SampleModel()
            {
                Id = x.Field<Guid>("Id"),
                ProjectId = x.Field<Guid>("ProjectId"),
                Code = x.Field<string>("Code"),
                Description = x.Field<string>("Description"),
                Quantity1 = x.Field<decimal>("Quantity1"),
                Quantity2 = x.Field<decimal>("Quantity1")
            }).ToList();


            return results;
        }

I don't think you understood the purpose of `ConvertToList1`... — NetMage, Oct 06 '22 at 20:33

Generic Method to convert datatable to list

3 Answers3