Chapter Querying Data
XF获取数据的三种方法：
其中参数schema，参见 Chapter Schema。
下面列出一个后面将会用到的schema片段，称之为片段A：
<User Set="Users">
...
<Employee.Name Element="Employee" Field="Name" />
...
</User>
方法一：ElementQuerier.GetSet("User", "Id,UserName,IsLockedOut,Employee.Name", "EmployeeId eq 1", "Id desc", schema);
内部会调用3.调用时，已在schema插入片段A。
方法二：.Linq，需要using XData.Data.Query。返回 XElement。
ElementContext.GetElementSet("User", schema)
.Where(x => x.Element("EmployeeId").Value == "1")
.OrderByDescending(x => int.Parse(x.Element("Id").Value))
.Select(x => new XElement(x.Name, x.Element("Id"), x.Element("UserName"), x.Element("IsLockedOut"), x.Element("Employee.Name")));
内部会调用3.调用时，schema 中应有片段A。
方法三：.ElementContext.GetSet(query, schema);返回 XElement。
schema 中应有片段A。
参数query：
<User>
<Select>
<Id />
<UserName />
<IsLockedOut />
<Employee.Name />
</Select>
<OrderByDescending>
<Id />
</OrderByDescending>
<!-- Linq -->
<Where>
<BinaryExpression NodeType="Equal">
<MemberExpression NodeType="MemberAccess" Member="Id" />
<ConstantExpression NodeType="Constant" DataType="System.Int32" Value="1" />
</BinaryExpression>
</Where>
<!-- ElementQuerier -->
<Filter>
<Value>EmployeeId eq 1</Value>
</Filter>
</User>
直接的Sql
Database.SqlQuery("User",
"SELECT u.Id,u.UserName,u.IsLockedOut,e.Name [Employee.Name] FROM Users u LEFT JOIN Employees e ON u.EmployeeId = e.Id WHERE u.EmployeeId = 1 ORDER BY u.Id DESC;");返回 IEnumerable<XElement>
比较发现，方法二Linq不够简洁，方法三过于底层。因此更为推荐方法一。以下叙述，均以方法一为主。
ElementQuerier设计极力模仿OData，称之为XData（将在XData介绍）。下面列出XData支持的操作符和函数：
Operators:
|
OData |
SQL Server |
OData |
SQL Server |
|
eq |
= |
IsLockedOut eq true |
IsLockedOut = 1 |
|
eq null |
IS NULL |
Descr eq null |
Descr IS NULL |
|
ne |
!= |
Name ne 'Milk' |
Name != 'Milk' |
|
ne null |
IS NOT NULL |
Descr ne null |
Descr IS NOT NULL |
|
gt |
> |
Id gt 1 |
Id > 1 |
|
ge |
>= |
CreatedDate ge datetime’2012-12-1’ |
CreatedDate >= ’2012-12-1’ |
|
lt |
< |
Allowance lt 1000 |
Allowance < 1000 |
|
le |
<= |
Allowance le 1000.1 |
Allowance <= 1000.1 |
|
and |
AND |
(IsLockedOut eq true) and (Id eq 1) |
(IsLockedOut = 1) and (Id = 1) |
|
or |
OR |
(Id gt 1) OR (Allowance le 1234.56) |
(Id > 1) OR (Allowance <= 1234.56) |
|
not |
NOT |
not (Id gt 1) |
NOT (Id > 1) |
|
has |
Not supported |
has |
Not supported |
|
add |
+ |
Allowance add 12.34 |
Allowance + 12.34 |
|
sub |
- |
Allowance sub 12.34 |
Allowance - 12.34 |
|
mul |
* |
Allowance mul 2.15 |
Allowance * 2.15 |
|
div |
/ |
Allowance div 2.0 |
Allowance / 2.0 |
|
mod |
% |
Rating mod 5 |
Rating % 5 |
Canonical Functions:
|
OData |
SQL Server |
OData |
SQL Server |
|
contains(XData) |
Operator IN |
contains((1,3,5),Id) |
Id IN (1,3,5) |
|
contains |
Operator LIKE |
contains(CompanyName,'Alfreds') |
CompanyName LIKE '%Alfreds%' |
|
endswith |
Operator LIKE |
endswith(CompanyName,'Futterkiste') |
CompanyName LIKE '%Futterkiste' |
|
startswith |
Operator LIKE |
startswith(CompanyName,'Alfr') |
CompanyName LIKE 'Alfr%' |
|
length |
DATALENGTH |
length(CompanyName) |
DATALENGTH(CompanyName) |
|
indexof |
CHARINDEX |
indexof(CompanyName,'lfreds') |
CHARINDEX('lfreds', CompanyName) -1 |
|
substring |
SUBSTRING |
substring(CompanyName, 1) |
SUBSTRING(CompanyName, 2, 2147483647) |
|
substring |
SUBSTRING |
substring(CompanyName, 1, 2) |
SUBSTRING(CompanyName, 2, 2) |
|
tolower |
LOWER |
tolower(CompanyName) |
LOWER(CompanyName) |
|
toupper |
UPPER |
toupper(CompanyName) |
UPPER(CompanyName) |
|
trim |
LTRIM and RTRIM |
trim(CompanyName) |
LTRIM(RTRIM(CompanyName)) |
|
concat(XData) |
Operator + |
concat(City, ', ', Country) |
City + ', ' + Country |
|
year |
DATEPART |
year(BirthDate) |
DATEPART(YEAR, BirthDate) |
|
month |
DATEPART |
month(BirthDate) |
DATEPART(MONTH, BirthDate) |
|
day |
DATEPART |
day(BirthDate) |
DATEPART(DAY, BirthDate) |
|
hour |
DATEPART |
hour(BirthDate) |
DATEPART(HOUR, BirthDate) |
|
minute |
DATEPART |
minute(BirthDate) |
DATEPART(MINUTE, BirthDate) |
|
second |
DATEPART |
second(BirthDate) |
DATEPART(SECOND, BirthDate) |
|
fractionalseconds |
DATEPART |
fractionalseconds(BirthDate) |
DATEPART(MILLISECOND, BirthDate) /1000.0 |
|
date |
CAST and CONVERT |
date(BirthDate) |
CAST(CONVERT(char(10), BirthDate,120) AS datetime) |
|
time |
Not supported |
time(BirthDate) |
Not supported |
|
totaloffsetminutes |
Not supported |
totaloffsetminutes(datetime) |
Not supported |
|
now |
GETDATE |
now() |
GETDATE() |
|
Utcnow(XData) |
GETUTCDATE |
utcnow() |
GETUTCDATE() |
|
maxdatetime |
Not supported |
maxdatetime() |
Not supported |
|
mindatetime |
Not supported |
mindatetime() |
Not supported |
|
totalseconds |
Not supported |
totalseconds |
Not supported |
|
round |
ROUND |
round(Amount) |
ROUND(Amount, 0) |
|
floor |
FLOOR |
floor(Amount) |
FLOOR(Amount) |
|
ceiling |
CEILING |
ceiling(Amount) |
CEILING(Amount) |
|
isof |
Not supported |
isof |
Not supported |
|
cast |
Not supported |
cast |
Not supported |
注：现在明白古怪的"EmployeeId eq 1"是什么了吧。
高阶ElementQuerier(XData)查询
调用如下方法，会返回一个“聚合”结构的XElement：
ElementQuerier.GetSet("Job","Id,Name","Allowance gt 0",null, expands,schema);
参数schema，如前。
参数expands，类型为IEnumerable<Expand>，其内容为：
仔细查看返回的XElement，重点查看层次结构：
<Jobs>
<Job>
<Id>1</Id>
<Name>CEO</Name>
<Employees>
<Employee>
<Id>1</Id>
<Name>Carl Zeiss</Name>
<JobId>1</JobId>
<Users>
<User>
<Id>1</Id>
<UserName>Carl</UserName>
</User>
<User>
<Id>5</Id>
<UserName>Admin</UserName>
</User>
</Users>
</Employee>
</Employees>
</Job>
<Job>
<Id>2</Id>
<Name>CFO</Name>
<Employees>
<Employee>
<Id>2</Id>
<Name>Cherry Wong</Name>
<JobId>2</JobId>
<Users>
<User>
<Id>2</Id>
<UserName>Cherry</UserName>
</User>
</Users>
</Employee>
</Employees>
</Job>
</Jobs>
如前所述，ElementQuerier.GetSet内部会调用ElementContext.GetSet，这里也是一样，在调用前会将下列片段B，插入schema参数中。
schema 片段B：
<Job Set="Jobs">
...
<Employees Element="Employee">
<Users Element="User" Select="Id,UserName" />
</Employees>
...
</Jobs>
注：上述方法，其实由下面这个链接引发。
http://localhost:2012/XData/Jobs?$expand=Employees($expand=Users($select=Id,UserName))&$select=Id,Name&$filter=Allowance%20gt%200
上面为master -details，再来看一个detail-master 查询，注意$filter里面的Job与User没有直接关系，而是通过ReferencePath连接：
http://localhost:2012/XData/Users?$expand=Employee&$select=Id,EmployeeId,UserName&$filter=Job.Allowance%20gt%200
返回结果：
<Users>
<User>
<Id>1</Id>
<EmployeeId>1</EmployeeId>
<UserName>Carl</UserName>
<Employee>
<Id>1</Id>
<Name>Carl Zeiss</Name>
<JobId>1</JobId>
</Employee>
</User>
<User>
<Id>2</Id>
<EmployeeId>2</EmployeeId>
<UserName>Cherry</UserName>
<Employee>
<Id>2</Id>
<Name>Cherry Wong</Name>
<JobId>2</JobId>
</Employee>
</User>
<User>
<Id>5</Id>
<EmployeeId>1</EmployeeId>
<UserName>Admin</UserName>
<Employee>
<Id>1</Id>
<Name>Carl Zeiss</Name>
<JobId>1</JobId>
</Employee>
</User>
</Users>
多对多关系的查询：
http://localhost:2012/XData/Users?$select=Id,UserName&$filter=Id%20lt%203&$expand=Roles($select=Id,RoleName)
返回结果：
<Users>
<User>
<Id>1</Id>
<UserName>Carl</UserName>
<Roles>
<Role>
<Id>1</Id>
<RoleName>Admin</RoleName>
</Role>
<Role>
<Id>2</Id>
<RoleName>User</RoleName>
</Role>
</Roles>
</User>
<User>
<Id>2</Id>
<UserName>Cherry</UserName>
<Roles>
<Role>
<Id>2</Id>
<RoleName>User</RoleName>
</Role>
</Roles>
</User>
</Users>
XF遇到多对多关系“连接”就此“断路”，所以以下查询是错误的：
http://localhost:2012/XData/Users?$select=Id,UserName&$filter=Id%20lt%203&$expand=Roles($select=Id,RoleName$expand=Routes)
ElementQuerier 除了上面介绍的GetSet，还有下列主要方法：
public XElement Find(string elementName, string[] key, string select, XElement schema)
public XElement Find(string elementName, string[] key, string select, IEnumerable<Expand> expands, XElement schema)
public XElement GetPage(string elementName, string select, string filter, string orderBy, int skip, int take, XElement schema)
public XElement GetPage(string elementName, string select, string filter, string orderBy, int skip, int take, IEnumerable<Expand> expands, XElement schema)
public int GetCount(string elementName, string filter, XElement schema)
public XElement GetDefault(string elementName, string select, XElement schema)
为什么会有GetDefault方法，见如下查询的结果：
http://localhost:2012/XData/Employees/$default?$select=Id,Name,JobId,Jobs.Name
注意下面的Jobs.Name，它是通过"Where JobId = 3"查询得到的。
<Employees>
<Id/>
<Name/>
<JobId>3</JobId>
<Jobs.Name>Clerk</Jobs.Name>
</Employees>