如何:对两个联合查询排序(实体框架)
本主题说明如何将两个查询的结果组合成一个结果集,然后对此结果集排序。将使用以下每种 实体框架 查询技术演示同一示例:
Entity SQL with ObjectQuery<T>
ObjectQuery <T> 的查询生成器方法
本主题中的示例基于 AdventureWorks 销售模型 (EDM)。若要运行这些示例中的代码,必须已将 AdventureWorks 销售模型添加到您的项目中,并将项目配置为使用 实体框架。为此,请完成如何:手动配置实体框架项目和如何:手动定义实体数据模型(实体框架) 中的过程。也可以使用实体数据模型向导定义 AdventureWorks 销售模型。有关更多信息,请参见如何:使用实体数据模型向导(实体框架)。
示例
以下是 Entity SQL 示例。若要 Entity SQL 查询进行联合和排序,您必须使用嵌套。在 Entity SQL 中,嵌套的查询必须括在括号中。
Using advWorksContext As AdventureWorksEntities = New AdventureWorksEntities
Dim esqlQuery As String = "SELECT P2.Name, P2.ListPrice " & _
" FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice " & _
" FROM AdventureWorksEntities.Product as P1 " & _
" where P1.Name like 'A%') " & _
" union all " & _
" (SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice " & _
" FROM AdventureWorksEntities.Product as P1 " & _
" WHERE P1.Name like 'B%') " & _
" ) as P2 " & _
" ORDER BY P2.Name"
Try
Dim objQuery As New ObjectQuery(Of DbDataRecord)(esqlQuery, advWorksContext)
For Each rec As DbDataRecord In objQuery
Console.WriteLine("Name: {0} ListPrice: {1}", rec.Item(0), rec.Item(1))
Next
Catch ex As EntityException
Console.WriteLine(ex.ToString())
Catch ex As InvalidOperationException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
String esqlQuery = @"SELECT P2.Name, P2.ListPrice
FROM ((SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Product as P1
where P1.Name like 'A%')
union all
(SELECT P1.Name, P1.ProductID as Pid, P1.ListPrice
FROM AdventureWorksEntities.Product as P1
WHERE P1.Name like 'B%')
) as P2
ORDER BY P2.Name";
try
{
foreach (DbDataRecord rec in
new ObjectQuery<DbDataRecord>(esqlQuery, advWorksContext))
{
Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
这是查询生成器方法示例。
Using advWorksContext As New AdventureWorksEntities()
Dim query As ObjectQuery(Of DbDataRecord) = _
advWorksContext.Product.Select("it.Name, it.ProductID As Pid, it.ListPrice") _
.Where("it.Name LIKE 'A%'").Union(advWorksContext.Product _
.Select("it.Name, it.ProductID As Pid, it.ListPrice") _
.Where("it.Name LIKE 'B%'")).Select("it.Name, it.ListPrice").OrderBy("it.Name")
Try
For Each rec As DbDataRecord In query
Console.WriteLine("Name:{0}ListPrice: {1}", rec(0), rec(1))
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
ObjectQuery<DbDataRecord> query =
advWorksContext.Product.Select("it.Name, it.ProductID As Pid, it.ListPrice")
.Where("it.Name LIKE 'A%'").Union(advWorksContext.Product
.Select("it.Name, it.ProductID As Pid, it.ListPrice")
.Where("it.Name LIKE 'B%'")).Select("it.Name, it.ListPrice").OrderBy("it.Name");
try
{
foreach (DbDataRecord rec in query)
{
Console.WriteLine("Name: {0}; ListPrice: {1}", rec[0], rec[1]);
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}