C#之DataTable方法的表达式
一 DataTable.Select()中的表达式
1 Expression 属性
Expression 属性的一个用途是创建计算出的列。
例如，若要计算税值，就要将单价乘以给定地区的税率。由于各地税率不同，不可能将单一的税率放在列中；于是便用 Expression 属性来计算这个值，如:
DataSet1.Tables("Products").Columns("tax").Expression = "UnitPrice * 0.086"
第二个用途是创建聚合列。类似于计算出的值，聚合基于 DataTable 中的整个行集执行操作。一个简单的示例是对返回到集中的行数进行计数，这也就是您可能用来对某个特定销售人员完成的交易次数进行计数的方法，如：
DataSet1.Tables("Orders").Columns("OrderCount").Expression = "Count(OrderID)"
注:日期参数需用#或'圈住
2 表达式语法
ColumnName
在创建表达式时，使用 ColumnName 属性来引用列。例如，如果一个列的 ColumnName 是“UnitPrice”（单价），而另一个是“Quantity”（数量），则表达式将是：
"UnitPrice * Quantity"
在为筛选器创建表达式时，将字符串放到单引号中：
"LastName = 'Jones'"
下面的字符是特殊字符，如下面所解释的，如果它们用于列名称中，就必须进行转义：
/n(newline) /t(tab) /r(return) ~ ( ) | # / = ^ > < + ` - * % "
如果列名称包含上面的字符之一，该名称必须用中括号括起来。例如，若要在表达式中使用名为“Column#”的列，应写成“[Column#]”：
Total * [Column#]
由于中括号是特殊字符，如果它是列名称的组成部分，必须使用斜杠 ("/") 将中括号转义。例如，名为“Column[]”的列应写成：
Total * [Column[/]] /*只有第二个中括号必须转义*/
用户定义的值
用户定义的值可以用在将与列值进行比较的表达式内。
字符串值应放在单引号内。日期值应放在磅符号 (#) 内。对于数值，允许使用小数和科学记数法。例如：
"FirstName = 'John'" "Price <= 50.00" "Birthdate < #1/31/82#"
对于包含枚举值的列，将值强制转换为整数数据类型。例如：
"EnumColumn = 5"
运算符
使用布尔值 AND、OR 和 NOT 运算符时允许串联。可以使用括号来组合子句和强制优先级。AND 运算符优先于其他运算符。例如：
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
在创建比较表达式时，允许使用下列运算符：
< > <= >= <>(不等于) = IN LIKE
在表达式中还支持下列算术运算符：
+（加） -（减） *（乘） /（除） %（模数）
字符串运算符
若要连接字符串，请使用 + 字符。字符串比较是否区分大小写由 DataSet 类的 CaseSensitive 属性的值来确定。但是，可以用 DataTable 类的 CaseSensitive 属性重写此值。
通配符
在 LIKE 比较中，* 和 % 两者可以互换地作为通配符。如果 LIKE 子句中的字符串包含 * 或 %，那么这些字符应用中括号（[]）对其进行转义。如果子句中有中括号，那么中括号字符应用中括号对其进行转义（例如 [[] 或 []]）。在模式的开头和结尾，或者在模式的结尾，或在模式的开头允许使用通配符。例如：
"ItemName LIKE '*product*'" "ItemName LIKE '*product'" "ItemName LIKE 'product*'"
在字符串的中间不允许使用通配符。例如，不允许 'te*xt'。
父/子关系引用
通过在列名称前面加 Parent，就可以在表达式中引用父表。例如，Parent.Price 引用父表的名为 Price 的列。
通过在列名称前面加一个 Child，就可以在表达式中引用子表中的列。但是，因为子关系可以返回多行，所以必须在聚合函数中包括对子列的引用。例如，Sum(Child.Price) 将返回子表中名为 Price 的列的总和。
如果某个表有多个子表，则语法是：Child(RelationName)。例如，如果某个表有两个子表，它们的名称分别为 Customers 和 Orders，则 DataRelation 对象被命名为 Customers2Orders，引用将为：
Avg(Child(Customers2Orders).Quantity)
父子表表达式应用实例:
1 //初始化子表,父子表关系 2 DataTable tableChild = new DataTable(); 3 4 tableChild.Columns.Add("id", typeof(string)); 5 tableChild.Columns.Add("value", typeof(int)); 6 7 System.Data.DataSet ds = new DataSet(); 8 ds.Tables.Add(tableChild); 9 ds.Tables.Add(table); 10 DataRelation relation = new DataRelation("relation", table.Columns["id"], tableChild.Columns["id"]); 11 ds.Relations.Add(relation); 12 13 for (int i = 1; i <= 10; i++) 14 { 15 System.Data.DataRow dRow = tableChild.NewRow(); 16 dRow["id"] = "id1"; 17 dRow["value"] = i; 18 tableChild.Rows.Add(dRow); 19 } 20 21 //计算子表记录数 22 column.Expression = "count(child(relation).value)"; 23 test = table.Select("id='id1'")[0]["exp1"]; 24 Console.WriteLine(test); 25 //test=10; 26 27 //计算父子表的百分比 28 column.Expression = "value/sum(child(relation).value)"; 29 test = table.Select("id='id1'")[0]["exp1"]; 30 Console.WriteLine(test); 31 //test=0.01818182; 32 33 //计算父子表的差值,比如父表为库存数量，子表为订购数量，计算得出需要补充的数量 34 column.Expression = "iif(value-sum(child(relation).value)>0,0,value-sum(child(relation).value))"; 35 test = table.Select("id='id1'")[0]["exp1"]; 36 Console.WriteLine(test); 37 //test=-54;
聚合
支持下列聚合类型：
- Sum（求和）
- Avg（平均）
- Min（最小值）
- Max（最大值）
- Count（计数）
- StDev（统计标准偏差）
- Var（统计方差）
聚合通常沿着关系执行。通过使用上面列出的函数之一和上面“父/子关系引用”中详述的子表列，来创建聚合表达式。例如：
Avg(Child.Price) Avg(Child(Orders2Details).Price)
聚合也可以在单个表上执行。例如，若要为名为“Price”的列中的数字创建汇总，就用：
Sum(Price)
注意 如果使用一个表来创建聚合，将没有组合功能。相反，所有行都在列中显示相同的值。
如果表没有行，聚合函数将返回空引用（Visual Basic 中为 Nothing）。
数据类型总是可以通过检查列的 DataType 属性来确定。还可以使用 Convert 函数来转换数据类型.
还支持下列函数：
CONVERT
描述 将给定表达式转换为指定的 .NET Framework 类型。 语法 Convert(expression, type) 参数 expression — 要转换的表达式;type — 值将转换成的 .NET Framework 类型。
例如：myDataColumn.Expression="Convert(total, 'System.Int32')"
注:Convert只能操作原列(myDataColumn)之外的列,否则会有循环异常报错.
所有转换都是有效的，只有下列情况例外：
Boolean 只能与 Byte、SByte、Int16、Int32、Int64、UInt16、UInt32、UInt64、String 和它本身相互转换。Char 只能与 Int32、UInt32、String 和它本身相互转换。DateTime 只能与 String 和它本身相互转换。TimeSpan 只能与 String 和它本身相互转换。
LEN
描述 获取字符串的长度 语法 LEN(expression) 参数 expression — 要计算的字符串。
例如：myDataColumn.Expression="Len(ItemName)"
ISNULL
描述 检查表达式并返回已检查的表达式或返回替换值。 语法 ISNULL(expression, replacementvalue) 参数 expression — 要检查的表达式;replacementvalue — 如果表达式为空引用 (Nothing)，则返回 replacementvalue
例如:myDataColumn.Expression="IsNull(price, -1)"
IIF
描述 根据逻辑表达式的结果，获取两个值之一。 语法 IIF(expr, truepart, falsepart) 参数 expr — 要计算的表达式;truepart — 表达式为真时返回的值;falsepart — 表达式为假时返回的值
TRIM
描述 移除所有前导的和后缀的空格字符，如/r、/n、/t、' ' 语法 TRIM(expression) 参数 expression — 要剪裁的表达式。
SUBSTRING
描述 获取从字符串中的指定点开始，具有指定长度的子字符串。 语法 SUBSTRING(expression, start,length) 参数 expression — 子字符串的源字符串;start — 指定子字符串开始位置的整数;length — 指定子字符串长度的整数
例如：myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
注意 可以向 Expression 属性分配一个空值或空字符串，来重置该属性。如果为表达式列设置了一个默认值，则在重置 Expression 属性之后，这个默认值就赋给所有以前填充过的行。
Select 表达式应用举例
1 System.Data.DataColumn column = new DataColumn("exp1", typeof(float)); 2 table.Columns.Add(column); 3 //简单计算 4 column.Expression = "value*2"; 5 test = table.Select("id='id1'")[0]["exp1"]; 6 Console.WriteLine(test); 7 //test=2; 8 //字符串函数 9 column.Expression = "len(id)"; 10 test = table.Select("id='id1'")[0]["exp1"]; 11 Console.WriteLine(test); 12 //test=3; 13 //字符串函数 14 column.Expression = "len(' '+id+' ')"; 15 test = table.Select("id='id1'")[0]["exp1"]; 16 Console.WriteLine(test); 17 //test=5; 18 //字符串函数 19 column.Expression = "len(trim(' '+id+' '))"; 20 test = table.Select("id='id1'")[0]["exp1"]; 21 Console.WriteLine(test); 22 //test=3; 23 //字符串函数 24 column.Expression = "substring(id,3,len(id)-2)"; 25 test = table.Select("id='id1'")[0]["exp1"]; 26 Console.WriteLine(test); 27 //test=1; //substring的起始字符位置为1不是0 28 //类型转换 29 column.Expression = "convert(substring(id,3,len(id)-2),'System.Int32')*1.6"; 30 test = table.Select("id='id1'")[0]["exp1"]; 31 Console.WriteLine(test); 32 //test=1.6; 33 //相当于sqlserver的isnull 34 column.Expression = "isnull(value,10)"; 35 test = table.Select("id='id1'")[0]["exp1"]; 36 Console.WriteLine(test); 37 //test=1; 38 //三元运算符,相当于sqlserver的case when 39 column.Expression = "iif(value>5,1000,2000)"; 40 test = table.Select("id='id1'")[0]["exp1"]; 41 Console.WriteLine(test); 42 //test=2000; 43 //like运算符 44 column.Expression = "iif(id like '%1',1000,2000)"; 45 test = table.Select("id='id1'")[0]["exp1"]; 46 Console.WriteLine(test); 47 //test=1000; 48 //in运算符 49 column.Expression = "iif(id not in('id1'),1000,2000)"; 50 test = table.Select("id='id1'")[0]["exp1"]; 51 Console.WriteLine(test); 52 //test=2000; 53 //嵌套的三元运算 54 column.Expression = "iif(value>5,1000,iif(id like '%1',4000,2000))"; 55 test = table.Select("id='id1'")[0]["exp1"]; 56 Console.WriteLine(test); 57 //test=4000; 58 //客户端计算所占总数的百分比 59 column.Expression = "value/sum(value)"; 60 test = table.Select("id='id1'")[0]["exp1"]; 61 Console.WriteLine(test); 62 //test=0.01818182 63 //客户端计算差值,比如nba常规赛的胜场差 64 column.Expression = "max(value)-value"; 65 test = table.Select("id='id1'")[0]["exp1"]; 66 Console.WriteLine(test); 67 //test=9
应用扩展例子:
DataRow[]有个扩展方法CopyToDataTable()，还可以利用DataView来达到检索的目的。
DataTable dataSource = new DataTable();
DataView dv = dataSource.DefaultView; dv.RowFilter = "columnA = 'abc'";
//1.过滤后直接获取DataTable
DataTable newTable1 = dv.ToTable();
//2.设置新DataTable的TableName
DataTable newTable2 = dv.ToTable("NewTableName");
//3.设置新表是否过滤重复项,拥有的列的列名以及出现的顺序
//即可以设置新表的字段。但是字段名肯定是老表dataSource中拥有的。
DataTable newTable3 = dv.ToTable(true, new string[] { "columnA,columnF,columnC" });
//4.综合了2.3两点。
DataTable newTable4 = dv.ToTable("NewTableName", true, new string[] { "columnA,columnF,columnC" });
二 DataTable.Compute的表达式
基本与Select方法的表达式相同,看实例:
1 //嵌套的三元运算 2 object test= dt.Compute("iif(1000=5,1000,iif(100>100,4001,2000))", null); //null:无条件 3 test = table.Compute("1+1", "false"); //满足false的条件 4 test = table.Compute("abs(1)", ""); //无条件,即全部 5 test = table.Compute("2%2", ""); //取模 6 test = table.Compute("value+1", "true"); 7 test = table.Compute("count(id)", "false"); //求数量 8 test = table.Compute("sum(value)", ""); //求和,value不能是string 9 test = table.Compute("avg(value)", ""); //平均 10 test = table.Compute("min(value)", ""); //最小 11 test = table.Compute("max(value)", ""); //最大 12 test = table.Compute("StDev(value)", ""); //统计标准偏差 13 test = table.Compute("Var(value)", ""); //统计方差 14 test = table.Compute("max(value)/sum(value)", ""); //复杂计算
三 DataTable的Lambda表达式
Lambda表达式
1. (x, y) => x * y //多参数，隐式类型=>表达式
2. x => x * 5 //单参数，隐式类型=>表达式
3. x => { return x * 5; } //单参数，隐式类型=>语句块
4. (int x) => x * 5 //单参数，显式类型=>表达式
5. (int x) => { return x * 5; } //单参数，显式类型=>语句块
6. () => Console.WriteLine() //无参数
string[] names={"agen","balen","coure","apple"};
string[] findNameA=Array.FindAll<string>(names,delegate(string v){return v.StartsWith("a");});
string[] findNameB=Array.FindAll<string>(names,v=>v.StartsWith("a"));
1, Where 过滤/查询
var rownumber = dt.AsEnumerable().Where<DataRow>(r=>r["ProductName"].ToString()=="Camara"); int row = rownumber.Count<DataRow>();//产品是相机的行数List<Person> persons1 = persons.Where(p => p.Age > 6).ToList(); //所有Age>6的Person的集合Person person = persons.SingleOrDefault(p => p.Age == 1); //Age=1的单个people类List<Person> persons2 = persons.Where(p => p.Name.Contains("Tom")).ToList(); //所有Name包含Tom的Person的集合List<Person> persons3= Students.Where(s => ( s.CLASS == "95031" || s.CLASS == "女"));//或者
2, Select 返回特定对象
var list = dt.AsEnumerable().Select(x => x["ShortName"].ToString()).ToList();
var list = dt.AsEnumerable().Select(x => new 对象名称 {id="", name="" }).ToList();
var rows = dt.AsEnumerable().Select(string.Format("ProductName='{0}'", _product));
combobox.DataSource = dt.AsEnumerable().Select(d => d.Field<string>("SKU")).ToArray();/ToList();
3, GroupBy
//结果中包括的字段：
//1、分组的关键字：Name = g.Key
//2、每个分组的数量：count = g.Count()
//3、每个分组的年龄总和：ageC = g.Sum(item => item.Age)
//4、每个分组的收入总和：moneyC = g.Sum(item => item.Money)
var ls = persons1.GroupBy(a => a.Name).Select(g => (new { name = g.Key, count = g.Count(), ageC = g.Sum(item => item.Age), moneyC = g.Sum(item => item.Money) }));
//分组过滤
Scores.Where( s => s.CNO.StartsWith("3") ).GroupBy( s => s.CNO ).Where( cc => ( cc.Count() >= 5) ).Select( cc => cc.Average( c => c.DEGREE) )
4, OrderBy
List<user> userList=userList.OrderBy(a => a.GetType().GetProperty("字段名").GetValue(a, null).ToString()).ToList();
Students.OrderByDescending(s => s.CLASS)
5, Distinct 去重复
List list= dt.Distinct().Select( t => t.Name).ToList();
6, Contains
Scores.Where( s => new Decimal[] {85,86,88}.Contains(s.DEGREE))
7, Count/Average/Sum
int cnt = Students.Where( s => s.CLASS == "95031" ).Select( s => s).Count();
float avg = Scores.Where( s => s.CNO == "3-105").Select( s => s.DEGREE).Average();
decimal sum = dt.AsEnumerable().Select(d => Convert.ToInt32(d.Field<decimal>("数量"))).Sum();
8,通过LINQ对DataTable数据查询，结果生成DataTable
LinQ获得IEnumerable对象,再转DataTable
var query = from g in dt_stu.AsEnumerable() group g by new { t1 = g.Field < string > ("STU_ID"), t2 = g.Field < string > ("CLASS_ID") } into m select new { STU_ID = m.Key.t1, CLASS_ID = m.Key.t2, 成绩总合计 = m.Sum(a => a.Field < decimal > ("成绩")), 优秀人数 = m.Count(a => a.Field < decimal > ("成绩") > 95) };
或
var query = dtPO.AsEnumerable().GroupBy(f => new { PO = f.Field < string > ("采购单号"), SKU = f.Field < string > ("物料代码") }).Select(f => new { PO = f.Key.PO, SKU = f.Key.SKU, QTY = f.Sum(x => x.Field < decimal > ("物料台数")) }); //.ToList();
再
public static DataTable Linq2Dt < T > (IEnumerable < T > varlist) { DataTable dtReturn = new DataTable(); // column names PropertyInfo[] oProps = null; if(varlist == null) return dtReturn; foreach(T rec in varlist) { 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; }
更新:
DataTable dt2 = new DataTable(); dt2.Columns.AddRange(new DataColumn[] {new DataColumn("DIV",typeof(string)), new DataColumn("GR", typeof(int)), new DataColumn("QTY", typeof(int)) }); var t = dt.AsEnumerable() .GroupBy(r => new { div = r["DIV"], gr = r["GR"] }) .Select(g => { var row = dt2.NewRow();row[0] = g.Key.div;row[1] = g.Key.gr;row[2] = g.Sum(r => (decimal)r["QUANTITY"]); return row; }); dt2 = t.CopyToDataTable();
