C# CopyToDataTable() function not avaialble when use LINQ & GroupBY with datatable

T.Zacks 3,986 Reputation points
2022-04-20T06:31:00.697+00:00

Here i am grouping data by LINQ on datatable but i was trying to use CopyToDataTable() which was not available. please see my code and tell me what i missed in my code for which CopyToDataTable() not available

DataTable perioddata = ds.Tables[1].AsEnumerable()
.GroupBy(a => new
{
NewPeriod = a.Field<string?>("NewPeriod").ToString(),
PeriodOrder = a.Field<int>("PeriodOrder").ToString().ToInt32()
})
.Select(b => new PeriodDto
{
NewPeriod = b.Key.NewPeriod,
PeriodOrder = b.Key.PeriodOrder
}).ToList().ToDataTable();

I tried this way to use CopyToDataTable() but no luck

DataTable perioddata = ds.Tables[1].AsEnumerable()
.GroupBy(a => new
{
NewPeriod = a.Field<string?>("NewPeriod").ToString(),
PeriodOrder = a.Field<int>("PeriodOrder").ToString().ToInt32()
})
.Select(b => new PeriodDto
{
NewPeriod = b.Key.NewPeriod,
PeriodOrder = b.Key.PeriodOrder
}).CopyToDataTable();

Thanks

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,605 questions
{count} votes

Accepted answer
  1. Jack J Jun 24,491 Reputation points Microsoft Vendor
    2022-04-21T06:51:01.903+00:00

    @T.Zacks , Welcome to Microsoft Q&A, based on my test, I reproduced your problem.

    As the Microsoft doc DataTableExtensions.CopyToDataTable Method said, the generic parameter T is DataRow in the DataTableExtensions.CopyToDataTable Method.

    Therefore, we need to mplement CopyToDataTable<T> Where the Generic Type T is not a datarow.

    You could try the following code to do it.

        public class PeriodDto  
        {  
            public string NewPeriod { get; set; }  
            public int PeriodOrder { get; set; }  
        }  
        public static class CustomLINQtoDataSetMethods  
        {  
            public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)  
            {  
                return new ObjectShredder<T>().Shred(source, null, null);  
            }  
      
            public static DataTable CopyToDataTable<T>(this IEnumerable<T> source,  
                                                        DataTable table, LoadOption? options)  
            {  
                return new ObjectShredder<T>().Shred(source, table, options);  
            }  
        }  
        public class ObjectShredder<T>  
        {  
            private System.Reflection.FieldInfo[] _fi;  
            private System.Reflection.PropertyInfo[] _pi;  
            private System.Collections.Generic.Dictionary<string, int> _ordinalMap;  
            private System.Type _type;  
      
            // ObjectShredder constructor.  
            public ObjectShredder()  
            {  
                _type = typeof(T);  
                _fi = _type.GetFields();  
                _pi = _type.GetProperties();  
                _ordinalMap = new Dictionary<string, int>();  
            }  
      
            /// <summary>  
            /// Loads a DataTable from a sequence of objects.  
            /// </summary>  
            /// <param name="source">The sequence of objects to load into the DataTable.</param>  
            /// <param name="table">The input table. The schema of the table must match that  
            /// the type T.  If the table is null, a new table is created with a schema  
            /// created from the public properties and fields of the type T.</param>  
            /// <param name="options">Specifies how values from the source sequence will be applied to  
            /// existing rows in the table.</param>  
            /// <returns>A DataTable created from the source sequence.</returns>  
            public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)  
            {  
                // Load the table from the scalar sequence if T is a primitive type.  
                if (typeof(T).IsPrimitive)  
                {  
                    return ShredPrimitive(source, table, options);  
                }  
      
                // Create a new table if the input table is null.  
                table = new DataTable(typeof(T).Name);  
      
                // Initialize the ordinal map and extend the table schema based on type T.  
                table = ExtendTable(table, typeof(T));  
      
                // Enumerate the source sequence and load the object values into rows.  
                table.BeginLoadData();  
                using (IEnumerator<T> e = source.GetEnumerator())  
                {  
                    while (e.MoveNext())  
                    {  
                        if (options != null)  
                        {  
                            table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);  
                        }  
                        else  
                        {  
                            table.LoadDataRow(ShredObject(table, e.Current), true);  
                        }  
                    }  
                }  
                table.EndLoadData();  
      
                // Return the table.  
                return table;  
            }  
      
            public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)  
            {  
                // Create a new table if the input table is null.  
                table = new DataTable(typeof(T).Name);  
      
                if (!table.Columns.Contains("Value"))  
                {  
                    table.Columns.Add("Value", typeof(T));  
                }  
      
                // Enumerate the source sequence and load the scalar values into rows.  
                table.BeginLoadData();  
                using (IEnumerator<T> e = source.GetEnumerator())  
                {  
                    Object[] values = new object[table.Columns.Count];  
                    while (e.MoveNext())  
                    {  
                        values[table.Columns["Value"].Ordinal] = e.Current;  
      
                        if (options != null)  
                        {  
                            table.LoadDataRow(values, (LoadOption)options);  
                        }  
                        else  
                        {  
                            table.LoadDataRow(values, true);  
                        }  
                    }  
                }  
                table.EndLoadData();  
      
                // Return the table.  
                return table;  
            }  
      
            public object[] ShredObject(DataTable table, T instance)  
            {  
      
                FieldInfo[] fi = _fi;  
                PropertyInfo[] pi = _pi;  
      
                if (instance.GetType() != typeof(T))  
                {  
                    // If the instance is derived from T, extend the table schema  
                    // and get the properties and fields.  
                    ExtendTable(table, instance.GetType());  
                    fi = instance.GetType().GetFields();  
                    pi = instance.GetType().GetProperties();  
                }  
      
                // Add the property and field values of the instance to an array.  
                Object[] values = new object[table.Columns.Count];  
                foreach (FieldInfo f in fi)  
                {  
                    values[_ordinalMap[f.Name]] = f.GetValue(instance);  
                }  
      
                foreach (PropertyInfo p in pi)  
                {  
                    values[_ordinalMap[p.Name]] = p.GetValue(instance, null);  
                }  
      
                // Return the property and field values of the instance.  
                return values;  
            }  
      
            public DataTable ExtendTable(DataTable table, Type type)  
            {  
                // Extend the table schema if the input table was null or if the value  
                // in the sequence is derived from type T.  
                foreach (FieldInfo f in type.GetFields())  
                {  
                    if (!_ordinalMap.ContainsKey(f.Name))  
                    {  
                        // Add the field as a column in the table if it doesn't exist  
                        // already.  
                        DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]  
                            : table.Columns.Add(f.Name, f.FieldType);  
      
                        // Add the field to the ordinal map.  
                        _ordinalMap.Add(f.Name, dc.Ordinal);  
                    }  
                }  
                foreach (PropertyInfo p in type.GetProperties())  
                {  
                    if (!_ordinalMap.ContainsKey(p.Name))  
                    {  
                        // Add the property as a column in the table if it doesn't exist  
                        // already.  
                        DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]  
                            : table.Columns.Add(p.Name, p.PropertyType);  
      
                        // Add the property to the ordinal map.  
                        _ordinalMap.Add(p.Name, dc.Ordinal);  
                    }  
                }  
      
                // Return the table.  
                return table;  
            }  
        }  
    }  
    

    Call the code in the main method:

     static void Main(string[] args)  
            {  
                  
                DataTable dataTable = new DataTable();  
                dataTable.Columns.Add("NewPeriod",typeof(string));  
                dataTable.Columns.Add("PeriodOrder",typeof(int));  
                dataTable.Rows.Add("test6", 11);  
                dataTable.Rows.Add("test2", 99);  
                dataTable.Rows.Add("test2", 99);  
                dataTable.Rows.Add("test4", 66);  
      
                DataTable table = dataTable.AsEnumerable()  
     .GroupBy(a => new  
     {  
         NewPeriod = a.Field<string>("NewPeriod").ToString(),  
         PeriodOrder = a.Field<int>("PeriodOrder")  
     })  
     .Select(b => new PeriodDto  
     {  
         NewPeriod = b.Key.NewPeriod,  
         PeriodOrder = b.Key.PeriodOrder  
     }).CopyToDataTable<PeriodDto>();  
                  
            }  
    

    Result:

    194966-image.png

    Best Regards,
    Jack


    If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Ian Chen 6 Reputation points MVP
    2022-04-21T06:23:46.8+00:00

    try

    var query = ds.Tables[1].AsEnumerable()
    .GroupBy(a => new
    {
    NewPeriod = a.Field<string?>("NewPeriod").ToString(),
    PeriodOrder = a.Field<int>("PeriodOrder").ToString().ToInt32()
    })
    .Select(b => new PeriodDto
    {
    NewPeriod = b.Key.NewPeriod,
    PeriodOrder = b.Key.PeriodOrder
    })

    var dt = query.ToDataTable();

    0 comments No comments