Aggregate functions in ADO.NET with GROUP BY functionality

This is a more direct question stemming from an earlier more general question i had earlier now that I've spend more time looking into ADO.NET

I want to take an ADO.NET DataTable and perform the equivalent of a SQL SELECT query with aggregate functions (such as SUM) on some columns, and GROUP BY set for the remaining columns. I then want to take the result and display it in a DataGrid.

I understand that I can create a DataView of a DataTable that contains filter criteria and aggregate functions. But the MSDN page on Expressions say that

"If you use a single table to create an aggregate, there would be no group-by functionality. Instead, all rows would display the same value in the column."

How do I get GROUP BY type functionality out of ADO.NET without writing my Table to a separate database and running a query there? Is there some way to do it by creating or using a second table?

From stackoverflow

• You can use the grouping ability of LINQ to accomplish this. Also, you can bind a DataGrid to a LINQ query, but the data will be read only.

  A web search for LINQ grouping should get you where you're going.

  Eric Anastas : Ahh I was thinking LINQ might be the answer. It's not a problem if the data is read only. Thanks!

• One way around it, is to turn your linq query result into a DataTable using reflection. Here is an example. Once you have a DataTable, you will have full groupby, paging, etc...

      private static System.Data.DataTable ObjectArrayToDataTable(object[] data)
      {
          System.Data.DataTable dt = new System.Data.DataTable();
          // if data is empty, return an empty table
          if (data.Length == 0) return dt;
  
          Type t = data[0].GetType();
          System.Reflection.PropertyInfo[] piList = t.GetProperties();
  
          foreach (System.Reflection.PropertyInfo p in piList)
          {
              dt.Columns.Add(new System.Data.DataColumn(p.Name, p.PropertyType));
          }
  
          object[] row = new object[piList.Length];
  
          foreach (object obj in data)
          {
              int i = 0;
              foreach (System.Reflection.PropertyInfo pi in piList)
              {
                  row[i++] = pi.GetValue(obj, null);
              }
              dt.Rows.Add(row);
          }
  
          return dt;
      }
  
      internal static DataTable GetAllStoredFileDetailsByUserID(int userID)
      {
          var db = GetDataContext();
          object[] result;
          try
          {
              result = (from files in db.Files
                        where files.OwnerUserID == userID && files.IsThumbnail == false
                        select new
                        {
                            FileID = files.FileID,
                            Name = files.Name,
                            DateCreated = files.DateCreated,
                            Size = files.Size,
                            FileHits = (from act in db.FileWebActivities where act.FileID == files.FileID select act).Count()
                        }).ToArray();
          }
          catch (Exception)
          {
             //omitted
          }
          return ObjectArrayToDataTable(result);
      }
  

• i have the exact same question. however i am suing .net 2.0, so cant use LINQ..any other solution?

  Michael La Voie : If you have a question of your own, please delete this answer and ask a new question.

• http://weblogs.asp.net/fmarguerie/archive/2007/09/05/linq-support-on-net-2-0.aspx