Entity Framework - 15. Entity SQL

[ 2009-04-20 14:06:57 | 作者: yuhen ]

字号: 大 | 中 | 小

首先需要说明 ESQL 并不支持 Insert、Update、Delete 操作。

1. Select

先看一些最简单的 ESQL 语句。

select value u from TestEntities.Users as u

select u.Name, u.Age from TestEntities.Users as u

select value u from TestEntities.Users as u where u.Age > 12

select value u from TestEntities.Users as u where u.Age between 12 and 14

select value u from TestEntities.Users as u where u.Name like 'user4%'

select value top(3) u from TestEntities.Users as u

select u.Name, o.Price from TestEntities.Users as u, TestEntities.Orders as o where o.UserId = u.Id

当然，还有 Distinct，它支持基于单个或多个属性组合做 "去重" 处理。

select distinct o.Userid, o.Price from TestEntities.Orders as o

T-SQL

SELECT 
[Distinct1].[C1] AS [C1], 
[Distinct1].[UserId] AS [UserId], 
[Distinct1].[Price] AS [Price]
FROM ( SELECT DISTINCT 
    [Extent1].[Price] AS [Price], 
    [Extent1].[UserId] AS [UserId], 
    1 AS [C1]
    FROM [dbo].[Order] AS [Extent1]
)  AS [Distinct1]

ESQL 对子查询同样有良好的支持。

select u.Id, u.Name, o.Title, o.Price
from TestEntities.Users as u, 
    (select value o from TestEntities.Orders as o where o.Price >= 100) as o
where u.Id = o.UserId

T-SQL

SELECT 
1 AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent2].[Title] AS [Title], 
[Extent2].[Price] AS [Price]
FROM  [dbo].[User] AS [Extent1]
INNER JOIN [dbo].[Order] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
WHERE [Extent2].[Price] >= 100

2. Join

ESQL 对联接的支持要比 LINQ to Entities 强大，几乎支持所有的的联接方式。

(1) LEFT INNER JOIN

select u.Name, o.Title
from TestEntities.Users as u, TestEntities.Orders as o
where o.UserId == u.Id

T-SQL

SELECT 
1 AS [C1], 
[Extent1].[Name] AS [Name], 
[Extent2].[Title] AS [Title]
FROM  [dbo].[User] AS [Extent1]
INNER JOIN [dbo].[Order] AS [Extent2] ON [Extent2].[UserId] = [Extent1].[Id]

(2) LEFT INNER JOIN

select u.Name, o.Title
from TestEntities.Users as u
join TestEntities.Orders as o on o.UserId == u.Id

T-SQL

SELECT 
1 AS [C1], 
[Extent1].[Name] AS [Name], 
[Extent2].[Title] AS [Title]
FROM  [dbo].[User] AS [Extent1]
INNER JOIN [dbo].[Order] AS [Extent2] ON [Extent2].[UserId] = [Extent1].[Id]

(3) LEFT OUTER JOIN

select u.Name, o.Title
from TestEntities.Users as u
left outer join TestEntities.Orders as o on o.UserId == u.Id

T-SQL

SELECT 
1 AS [C1], 
[Extent1].[Name] AS [Name], 
[Extent2].[Title] AS [Title]
FROM  [dbo].[User] AS [Extent1]
LEFT OUTER JOIN [dbo].[Order] AS [Extent2] ON [Extent2].[UserId] = [Extent1].[Id]

3. GroupBy

支持基于单个或多个属性的分组。

select g, sum(o.Price) as total from TestEntities.Orders as o
group by o.UserId as g

T-SQL

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [UserId], 
[GroupBy1].[A1] AS [C2]
FROM ( SELECT 
    [Extent1].[UserId] AS [K1], 
    SUM([Extent1].[Price]) AS [A1]
    FROM [dbo].[Order] AS [Extent1]
    GROUP BY [Extent1].[UserId]
)  AS [GroupBy1]

select g, p as Price, count(p) as [Count] from TestEntities.Orders as o
group by o.UserId as g, o.Price as p

T-SQL

SELECT 
1 AS [C1], 
[GroupBy1].[K2] AS [UserId], 
[GroupBy1].[K1] AS [Price], 
[GroupBy1].[A1] AS [C2]
FROM ( SELECT 
    [Extent1].[Price] AS [K1], 
    [Extent1].[UserId] AS [K2], 
    COUNT([Extent1].[Price]) AS [A1]
    FROM [dbo].[Order] AS [Extent1]
    GROUP BY [Extent1].[Price], [Extent1].[UserId]
)  AS [GroupBy1]

我们还可以用 Having 对分组做条件处理。

select g as UserId, count(g) as [Count]
from TestEntities.Orders as o
group by o.UserId as g
having count(o.Price) >= 2

T-SQL

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [UserId], 
[GroupBy1].[A2] AS [C2]
FROM ( SELECT 
    [Extent1].[UserId] AS [K1], 
    COUNT([Extent1].[Price]) AS [A1], 
    COUNT([Extent1].[UserId]) AS [A2]
    FROM [dbo].[Order] AS [Extent1]
    GROUP BY [Extent1].[UserId]
)  AS [GroupBy1]
WHERE [GroupBy1].[A1] >= 2

4. OrderBy

ESQL 同样支持单个或多个属性的排序组合。

select value o from TestEntities.Orders as o
order by o.UserId desc, o.Price

T-SQL

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
[Extent1].[Price] AS [Price], 
[Extent1].[UserId] AS [UserId]
FROM [dbo].[Order] AS [Extent1]
ORDER BY [Extent1].[UserId] DESC, [Extent1].[Price] ASC

利用 Skip 和 Limit 可以实现分页处理，它们都是排序的子句。

select value o from TestEntities.Orders as o
order by o.Id skip 1 limit 2

T-SQL

SELECT TOP (2) 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
[Extent1].[Price] AS [Price], 
[Extent1].[UserId] AS [UserId]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[Price] AS [Price], 
    [Extent1].[UserId] AS [UserId], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
    FROM [dbo].[Order] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 1
ORDER BY [Extent1].[Id] ASC