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