Entity Framework 学习总结之八：EntitySQL 复杂查询及函数

复杂查询及函数

外键

Entity SQL 与其它的查询一样，可以通过外键的关系直接取值或判断，如：

using (var edm = new NorthwindEntities ())

{

    string sqlStr = "SELECT VALUE o FROM NorthwindEntities.Orders AS o WHERE o.Customers.Country = 'Mexico'" ;

 

    ObjectQuery <Orders > query = edm.CreateQuery<Orders >(sqlStr);

    ObjectResult <Orders > result = query.Execute(MergeOption .NoTracking);

 

 

    foreach (Orders o in result)

    {

        Console .WriteLine("{0},{1}" , o.OrderID, o.Customers.Address);

    }

 

    Console .WriteLine(query.ToTraceString());

}

 

我们直接用 o.Customers.Country 这是一个一对多的外键关系。

 

注： “ NorthwindEntities.Orders ” 中的 “ NorthwindEntities ” 可以省略的！

 

GroupBy

对数据进行分组查询可以使用以下语法：

SELECT o . OrderDate , Count ( o . OrderID ) AS Count FROM NorthwindEntities . Orders AS o GROUP BY o . OrderDate

直接进行 Group by 操作，而进行分组时也可以使用 Count 、 Max 、 Min 、 Sum 、 Avg 这几个函数，使用方法与 SQL 没有什么不同。

 

统计函数

Entity SQL 同样支持统计函数： Avg ， BigCount,Count,Max,Min,StDev,Sum 。

 

类型转换

Null 文本与 Entity SQL 类型系统中的任何类型都兼容，可以使用 cast 进行类型转换，例如：

SELECT Length ( cast (e . Notes as string) ) from NorthwindEntities . Employees as e where e . EmployeeID = 1

 

其中， Nvarchar 等可以成 string ，数字类型可以转成 int32 ，其他的类型转换类似。如果无法完成转换，则将报异常。

 

字符串函数

我们先查询出一个字符串供下面函数演示使用：

SELECT e.Notes from NorthwindEntities . Employees as e where e.EmployeeID=1

 

-- 结果： Education includes a BA in psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call." Nancy is a member of Toastmasters International.

 

IndexOf ： 获取前面字符串在后面字符串中的位置。

SELECT IndexOf ( 'i' , e . Notes ) from NorthwindEntities . Employees as e where e . EmployeeID = 1

-- 结果： 7

 

Right ： 获取前面字符串从右面的 N 个字符，注意字符串必须要转换。
SELECT Right ( cast (e.Notes as string) ,14) from NorthwindEntities . Employees as e where e.EmployeeID=1

-- 结果： International.

 

Left ： 同上

 

Length ： 获取字符串长度。

SELECT Length ( cast (e . Notes as string) ) from NorthwindEntities . Employees as e where e . EmployeeID = 1

-- 结果： 175

 

SUBSTRING ： 截取字符串，从字符串中 A 开始截取长度为 B 个字符。

SELECT SUBSTRING ( e.Notes,2,10 ) from NorthwindEntities . Employees as e where e . EmployeeID = 1

-- 结果： ducation i

 

LTrim 、 RTrim 、 Trim ： 分别为删除字符串左、右、两侧空格。

SELECT Trim ( SUBSTRING ( e.Notes,2,9 )) from NorthwindEntities . Employees as e where e . EmployeeID = 1

-- 结果： ducation

 

Replace ： 将字符串参数 A 中的字符串参数 B 替换为字符串参数 C 。

SELECT Replace ( cast (e.Notes as string), 'includes' , 'Astar' ) from NorthwindEntities . Employees as e where e . EmployeeID = 1

-- 结果： Education Astar a BA in psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call." Nancy is a member of Toastmasters International.

 

ToLower 、 ToUpper ： 大小写转换。

 

Reverse ： 将字符串反转。

SELECT Reverse ( cast (e.Notes as string) ) from NorthwindEntities . Employees as e where e . EmployeeID = 1

-- 结果： .lanoitanretnI sretsamtsaoT fo rebmem a si ycnaN ".llaC dloC eht fo trA ehT" detelpmoc osla ehS .0791 ni ytisrevinU etatS odaroloC morf ygolohcysp ni AB a sedulcni noitacudE

 

数字函数

四舍五入： Round(1.4)

向下取整： Floor(1.9) >> 1

向上取整： Ceiling(1.1) >> 2

 

时间函数

当前时间： CurrentDateTime() >> 2011/1/6 20:34:09

格林威治时间： CurrentUtcDateTime()

年、月、日、时、分、秒： Year(e.BirthDate),Month(e.BirthDate),Day(e.BirthDate),Hour(e.BirthDate),Minute(e.BirthDate),Second(e.BirthDate)

 

GUID ： NewGuid () >>4131d481-2291-4fe9-8756-6e2b1d99f607

 

位计算函数

如果提供 Null 输入，则这些函数返回 Null 。这些函数的返回类型与参数类型相同。如果函数采用多个参数，则这些参数必须具有相同的类型。若要对不同类型执行位运算，则需要显式强制转换为相同类型。（ BitWiseAnd,BitWiseNot,BitWiseOr,BitWiseXo ）

 

集合运算符

Set(Colleciton) ：我们假定 Collection 中可以包含重复元素，那么， Set(Collection) 就取出 Collection 中的非重复的元素。

例如： Set({1,1,3,3,4}) 将得到结果 {1,3,4} 。

 

e IN collection ： IN 运算符返回 e 是否在 collection 中。

例如： 1 IN {1,2,3} 返回 TRUE ， 1 IN {-1,-2,-3} 返回 FALSE 。

 

EXISTS(collection) ：判定 collection 是否为空，为空时返回 FALSE ，否则，返回 TRUE 。

例如： EXISTS({1}) 返回 TRUE ；而 EXISTS(SELECT V from {1,2,3} AS V WHERE V=-1) 返回 FALSE 。

 

collectionA UNION [ALL] collection ： UNION 求两个集合的并集，并且去除相同的元素；而 UNION ALL 求两个集合并集，但是，同时包含重复元素。

例如： {1,2,3} UNION {2,3,4} 得到 {1,2,3,4} ，而 {1,2,3} UNION ALL {2,3,4} 得到 {1,2,3,2,3,4} 。

 

collecitonA INTERSECT collectionB ： INTERSECT 求两个集合的交集。

例如： {1,2,3} INTERSECT {2,3} 得到 {2,3} 。注意，交集会去除重复元素。 {1,2,2,3} INTERSECT {2,2,3} 得到 {2,3} ，而非 {2,2,3}

 

collecitonA EXCEPT collecitonB ： A EXCEPT B 代表：从 A 集合里减去 B 集合的元素。换句话说，就是取得 A 中有 B 中没有的元素。

例如： {1,2,3} EXCEPT {2,3} 将得到 {1} 。

 

collecitonA OVERLAPS collectionB ： A OVERLAPS B 将判定 A 、 B 集合是否有交集，有则返回 TRUE ，没有则返回 FALSE 。其相当于 EXISTS(A INTERSECT B) 。

例如： {1,2,3} OVERLAPS {3,4} 返回 TRUE ，而 {1,2,3} OVERLAPS {4,5} 返回 FALSE 。

 

ANYELEMENT(collection) ： ANYELEMENT 随机返回集合中的任意一个元素。

例如： ANYELEMENT({1,2,3}) 有可能返回 1 ，有可能返回 2 也有可能返回 3 。