Convert var query to DataTable

Question

Good Afternoon,

    I have one little doubt, as it still does not have much experience in Linq, I have difficulty in solving, the next problem.

I created following table:

            

 DataTable table = new DataTable();
table.Columns.Add("Source", typeof(int));
 table.Columns.Add("Target", typeof(int));
 table.Columns.Add("Hit", typeof(long));
 table.Columns.Add("Min", typeof(int));
 table.Columns.Add("Max", typeof(int));
 table.Columns.Add("Average", typeof(int));

I'm trying to use the following statement to group the results.

  var query = from row in table.AsEnumerable()
            group row by new { column1 = row.Field<int>("Source"), column2 = row.Field<int>("Target") } into grp
            orderby grp.Key
            select new
               {
                      Id = grp.Key,
                      Sum = grp.Sum(r => r.Field<long>("Hit")),
                      Min = grp.Min(r => r.Field<long>("Min")),
                      Max = grp.Min(r => r.Field<long>("Min")),
                      Average = grp.Min(r => r.Field<long>("Min"))
                };

    DataTable dt = query.CopyToDataTable();

The problem is that I'm trying to save the results in another table but not result.
what I'm doing the wrong

Thanks

Answer 1

Try this:

  var query = from row in table.AsEnumerable()
            group row by new { column1 = row.Field<int>("Source"), column2 = row.Field<int>("Target") } into grp
            orderby grp.Key
            select new
               {
                   column1 = grp.Select((r => r.Field<long>("Source"))),
                      column2 = grp.Select((r => r.Field<long>("Target"))),
                      Id = grp.Key,
                      Sum = grp.Sum(r => r.Field<long>("Hit")),
                      Min = grp.Min(r => r.Field<long>("Min")),
                      Max = grp.Min(r => r.Field<long>("Min")),
                      Average = grp.Min(r => r.Field<long>("Min"))
                };

  foreach (var item in query)
  {
      table.Rows.Add(item.column1, item.column2,item.Id, item.Sum, item.Min, item.Max, item.Average);
  }

Answer 2

Hi, 

query execution returns IEnumerable of anonymous type, and there is no CopyToDataTable built on extension available with IEnumerable.

You can have a code like to convert list of anonymous type to datatable which is generic, 

var resultTable = new DataTable();
bool firstPass = true;
foreach (var item in query.ToList())
 {
  if (firstPass)
   {
    Array.ForEach(item.GetType().GetProperties(), p => resultTable.Columns.Add(new DataColumn(p.Name)));
    firstPass = false;
   }
  var newRow = resultTable.NewRow();
  Array.ForEach(item.GetType().GetProperties(), p => newRow[p.Name] = p.GetValue(item, null));
  resultTable.Rows.Add(newRow);   
 }

You can further optimize this code by introducing GetProperties() in PeopertyInfo[] cache 

I hope this helps you...

---

If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

Answer 3

Hi,

I changed the code, but give the error

{"At least one object must implement IComparable."}

     var query = from row in table.AsEnumerable()
                        group row by new { column1 = row.Field<int>("Source"), column2 = row.Field<int>("Target") } into grp
                        orderby grp.Key
                        select new
                        {
                            Id = grp.Key,
                            Sum = grp.Sum(r => r.Field<long>("Hit")),
                            Min = grp.Min(r => r.Field<long>("Min")),
                            Max = grp.Min(r => r.Field<long>("Min")),
                            Average = grp.Min(r => r.Field<long>("Min"))
                        };
            var resultTable = new DataTable();
            bool firstPass = true;
            foreach (var item inquery.ToList())
            {
                if (firstPass)
                {
                    Array.ForEach(item.GetType().GetProperties(), p => resultTable.Columns.Add(new DataColumn(p.Name)));
                    firstPass = false;
                }
                var newRow = resultTable.NewRow();
                Array.ForEach(item.GetType().GetProperties(), p => newRow[p.Name] = p.GetValue(item, null));
                resultTable.Rows.Add(newRow);
            }

Answer 4

Hi Ricardo,

First, I recommend you ensure you have the rows in table to avoid that the query is empty.

Second, please check this part:

Sum = grp.Sum(r => r.Field<long>("Hit")),
Min = grp.Min(r => r.Field<long>("Min")),
Max = grp.Min(r => r.Field<long>("Min")),
Average = grp.Min(r => r.Field<long>("Min"))

In original definition of table, the column "Min" is Int32, but it is Int64 here.

I hope this helps.

Best Reagrds,

---

Alexander Sun

Answer 5

Hi,

thanks for the reply, I checked the initial table, and contain data

long num_rows = dt.Rows.Count;
DataTable table = new DataTable();
table.Columns.Add("Source", typeof(int));
table.Columns.Add("Target", typeof(int));
table.Columns.Add("Hit", typeof(long));
table.Columns.Add("Min", typeof(long));
table.Columns.Add("Max", typeof(long));
table.Columns.Add("Average", typeof(long));


 /*var query = from row in table.AsEnumerable()
                        group row by new { column1 = row.Field<int>("Source"), column2 = row.Field<int>("Target")} into grp
                        orderby grp.Key
                        select new
                        {
                            Id = grp.Key,
                            Sum = grp.Sum(r => r.Field<long>("Hit")),
                            Min = grp.Min(r => r.Field<long>("Min")),
                            Max = grp.Min(r => r.Field<long>("Max")),
                            Average = grp.Min(r => r.Field<long>("Average"))
                        };


            var resultTable = new DataTable();
            bool firstPass = true;
            foreach (var item in query.ToList())
            {
                if (firstPass)
                {
                    Array.ForEach(item.GetType().GetProperties(), p => resultTable.Columns.Add(new DataColumn(p.Name)));
                    firstPass = false;
                }
                var newRow = resultTable.NewRow();
                Array.ForEach(item.GetType().GetProperties(), p => newRow[p.Name] = p.GetValue(item, null));
                resultTable.Rows.Add(newRow);
            }
            */

//write all the rows.
 //Save in file
            string ficheiro_csv = @"C:\\Proximity\\ResMin.csv";
            StreamWriter sw = new StreamWriter(ficheiro_csv, false);

        foreach (DataRow dr in table.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }

                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();
        Console.ReadKey();



Without making the calculations the table returns the following result set, so the problem you have to be in the query

Source,Target,Hit,Min,Max,Average
93,106,1,356,356,356
106,93,1,0,0,0
93,106,1,601,601,601
106,86,1,307,307,307
106,86,1,569,569,569
106,86,1,31,31,31

The idea with the consultation is to obtain the following results
93,106,2,356,601,478,5
106,93,1,0,0,0
106,86,3,31,569,302,33

                              

Answer 6

Hi Ricardo,

I checked your code. I think the problem is caused by this statement: orderby grp.Key

If you comment this line, you will find the query is not empty. Since there are two properties under grp.Key (Since the type of Key is the anonymous type you created before which have two properties (column1 and column2)), the CLR do not know how to compare the Key properties. Thus, it thrown a exception to let you implement IComparable interface to implement the CompareTo method to tell the CLR how to compare Key.

I recommend you modify this statement as: orderby grp.Key.column1; or orderby grp.Key.column2;. 

I hope this helps.

Best Regards,

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us