在TableController
中使用实体框架时,我试图找出问题的根源
我创建了以下设置。
公共类TodoItemController : TableController { protected override void Initialize(HttpControllerContext controllerContext) { base.Initialize(controllerContext);context =新的MobileServiceContext();context.Database.Log += LogToDebug;DomainManager =新的EntityDomainManager(context,Request);}公共IQueryable GetAllTodoItems() { var q= Query();return q;}
公共类TodoItemsWebController : ApiController {私有MobileServiceContext db =新MobileServiceContext();公共TodoItemsWebController() { db.Database.Log += LogToDebug;}公共IQueryable GetTodoItems() { return db.TodoItems;}
我仔细梳理了tablecontroller
代码,深入研究了Query
方法,该方法只是通过DomainManager
代理调用,将Where(_ => !_.IsDeleted)
修改添加到IQueryable
中
然而,这两个查询产生的SQL却截然不同。
对于常规的Web API Controller,您将获得以下SQL。
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[CreatedAt] AS [CreatedAt],
[Extent1].[UpdatedAt] AS [UpdatedAt],
[Extent1].[Deleted] AS [Deleted],
[Extent1].[Text] AS [Text],
[Extent1].[Complete] AS [Complete]
FROM [dbo].[TodoItems] AS [Extent1]
但是对于TableController,您会得到以下SQL块,其中中间有一个*魔术* Guid,并产生一个嵌套的SQL语句。当你开始处理像$top,$skip,$filter和$expand这样的ODATAv3查询时,它的性能就变成了垃圾。
SELECT TOP (51)
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
[Project1].[C3] AS [C3],
[Project1].[Complete] AS [Complete],
[Project1].[C4] AS [C4],
[Project1].[Text] AS [Text],
[Project1].[C5] AS [C5],
[Project1].[Deleted] AS [Deleted],
[Project1].[C6] AS [C6],
[Project1].[UpdatedAt] AS [UpdatedAt],
[Project1].[C7] AS [C7],
[Project1].[CreatedAt] AS [CreatedAt],
[Project1].[C8] AS [C8],
[Project1].[Version] AS [Version],
[Project1].[C9] AS [C9],
[Project1].[Id] AS [Id]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Version] AS [Version],
[Extent1].[CreatedAt] AS [CreatedAt],
[Extent1].[UpdatedAt] AS [UpdatedAt],
[Extent1].[Deleted] AS [Deleted],
[Extent1].[Text] AS [Text],
[Extent1].[Complete] AS [Complete],
1 AS [C1],
N'804f84c6-7576-488a-af10-d7a6402da3bb' AS [C2],
N'Complete' AS [C3],
N'Text' AS [C4],
N'Deleted' AS [C5],
N'UpdatedAt' AS [C6],
N'CreatedAt' AS [C7],
N'Version' AS [C8],
N'Id' AS [C9]
FROM [dbo].[TodoItems] AS [Extent1]
) AS [Project1]
ORDER BY [Project1].[Id] ASC
您可以在此处查看这两个查询的结果。https://pastebin.com/tSACq6eg
所以我的问题是:
TableController
以这种方式生成SQL?IQueryable
进行这些修改?我假设它是通过某个中间件步骤完成的,或者是在调用Query()
方法后稍后在请求中的一个on executed属性完成的,但我终生找不到它。https://stackoverflow.com/questions/45300933
复制相似问题