Linq实现DataTable的分组统计

DataTable dt = GetTestData(10); //获取10条测试数据 
                var queryByService = from r in dt.AsEnumerable()   
                            group r by r.Field<string>(4) into g   
                            select new  
                            {   
                                Service = g.Key,   
                                Bookings = g.Count(p => p.Field<string>(1) !=""),   
                                ConfirmedBookings = g.Count(p => p.Field<string>(1) =="Confirmed"),   
                                PendingBookings = g.Count(p => p.Field<string>(1) =="Pending"),   
                                CancelledBookings = g.Count(p => p.Field<string>(1) =="Cancelled")                          
                            };   
                var queryByClient = from r in dt.AsEnumerable()   
                                    where r.Field<string>(1) =="Confirmed"  
                            group r by r.Field<string>(5) into g                             
                            select new  
                            {   
                                BookingClient = g.Key,   
                                _20DV = g.Count(p => p.Field<string>(2)=="20DV"),   
                                _40DV = g.Count(p => p.Field<string>(2) =="40DV"),   
                                _40HC = g.Count(p => p.Field<string>(2) =="40HC"),   
                                Bookings = g.Count(),   
                                TotalOceanFreight = g.Sum(p => p.Field<decimal>(3)),   
                                AverageOceanFreight = g.Average(p => p.Field<decimal>(3))                             
                            };   
                var queryByType = from r in dt.AsEnumerable()                                   
                                    group r by r.Field<string>(2) into g   
                                    select new  
                                    {   
                                        EquipmentType = g.Key,   
                                        Total = g.Count(),   
                                        Confirmed = g.Count(p => p.Field<string>(1) =="Confirmed"),   
                                        Pending = g.Count(p => p.Field<string>(1) =="Pending"),   
                                        Cancelled = g.Count(p => p.Field<string>(1) =="Cancelled")   
                                    };   
                GridView1.DataSource = dt;   
                GridView1.DataBind();   
                GridView2.DataSource = queryByService;   
                GridView2.DataBind();   
                GridView3.DataSource = queryByClient;   
                GridView3.DataBind();   
                 
                DataTable dtByType = ConvertToDataTable(queryByType);   
                GridView4.DataSource = dtByType; //或 GridView4.DataSource = queryByType;   
                GridView4.DataBind();  

 
另外ConvertToDataTable()是在网上看到的方法

public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist)   
        {   
            DataTable dtReturn =new DataTable();   
            // column names    
            PropertyInfo[] oProps =null;   
            if (varlist ==null) return dtReturn;   
            foreach (T rec in varlist)   
            {   
                // Use reflection to get property names, to create table, Only first time, others will follow    
if (oProps ==null)   
                {   
                    oProps = ((Type)rec.GetType()).GetProperties();   
                    foreach (PropertyInfo pi in oProps)   
                    {   
                        Type colType = pi.PropertyType;   
                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() ==typeof(Nullable<>)))   
                        {   
                            colType = colType.GetGenericArguments()[0];   
                        }   
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));   
                    }   
                }   
                DataRow dr = dtReturn.NewRow();   
                foreach (PropertyInfo pi in oProps)   
                {   
                    dr[pi.Name] = pi.GetValue(rec, null) ==null? DBNull.Value : pi.GetValue   
                    (rec, null);   
                }   
                dtReturn.Rows.Add(dr);   
            }   
            return dtReturn;   
        }