How to convert Collections to Datatable

Question

Can you please help me to solve this problem
And is that I want to affect object de type collection Adhérent to datatable

Code:

BiblioEntities b = new BiblioEntities();
var ad = b.Adhérent.SqlQuery(@"select * from Adhérent ").ToList();
DataTable dt = (DataTable)ad;

Error:

Impossible de convertir le type 'System.Collections.Generic.List<Biblio.Adhérent>' en 'System.Data.DataTable'

Please post your question in English, however it seems you are trying to convert your List to DataTable, if so, you can do it like this https://stackoverflow.com/a/42550827/2946329 — Salah Akbari, Aug 12 '20 at 14:12
It needs the converter: DataTableConverter.ToDataTable(model.Values) — SteveC, Aug 12 '20 at 14:14
1) why do you want a DataTable when you already have a strongly typed list? 2) if you really want a strongly-typed list, why use EF/EF Core at all? Use `SqlCommand` to get an IDataReader from the results and load it with [DataTable.Load](https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.load?view=netcore-3.1). EF Core uses SqlCommand underneath, so going through a list and then back to a DataTable only wastes time and resources — Panagiotis Kanavos, Aug 12 '20 at 14:26
What are you really trying to do? There doesn't seem to be a need for an ORM like EF if you want a DataTable — Panagiotis Kanavos, Aug 12 '20 at 14:27
You need to use a DataAdapter to fill a DataTable : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter?view=dotnet-plat-ext-3.1 — jdweng, Aug 12 '20 at 14:32
@PanagiotisKanavos Sometimes I have to work with E.F, For me, when I want to write a complex Request, I have a problem writing it in an E.F form. Therefore, this method that I take is easy and requires a few lines, but the problem is when I have RQ which selects which cent more than one line and I don't know how to work with this multiple line return the only way I know I have a datatabe and do my treatment at this table — Omar M'bairik, Aug 12 '20 at 15:07

iSR5 · Answer 1 · 2020-08-12T15:23:18.093

I used the following extension with Entity Framework models, it would work with any type of collection :

public static System.Data.DataTable ToDataTable<T>(this IEnumerable<T> source)
{
    var table = new System.Data.DataTable(typeof(T).Name);

    var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

    foreach (var prop in props)
    {
        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
    }

    foreach (var item in source)
    {
        var values = new object[props.Length];
        for (var i = 0; i < props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }

        table.Rows.Add(values);
    }

    return table;
}

and applying it to your sample code would be like this :

var dt= new BiblioEntities().Adhérent.SqlQuery(@"select * from Adhérent ").ToDataTable();

SteveC · Answer 2 · 2020-08-12T15:26:05.550

Here's another implementation

    public static class DataTableConverter
    {
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }
    }

To add to your code:

var dt = DataTableConverter.ToDataTable(ad);

How to convert Collections to Datatable

2 Answers2