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;  
}