Chapter Querying Data

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>