为什么实体框架为Azure移动服务表控制器生成以下嵌套SQL?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (22)

我试图在与a一起使用它时遇到实体框架问题的底部 TableController

我创建了以下设置。

  1. 基本的TodoItem示例提供了一个新的移动Web API,它利用了EntityFramework,TableController和默认的EntityDomainManager public class TodoItemController : TableController<TodoItem> { protected override void Initialize(HttpControllerContext controllerContext) { base.Initialize(controllerContext); context = new MobileServiceContext(); context.Database.Log += LogToDebug; DomainManager = new EntityDomainManager<TodoItem>(context, Request); } public IQueryable<TodoItem> GetAllTodoItems() { var q = Query(); return q; }
  2. Web API 2控制器。 public class TodoItemsWebController : ApiController { private MobileServiceContext db = new MobileServiceContext(); public TodoItemsWebController() { db.Database.Log += LogToDebug; } public IQueryable<TodoItem> GetTodoItems() { return db.TodoItems; }

我已经通过tablecontroller代码,深入挖掘该Query方法,该方法只是通过DomainManager添加Where(_ => !_.IsDeleted)修改来代理调用IQueryable

然而这两个查询产生了非常不同的SQL。

对于常规Web API控制器,可以获得以下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,它的中间有一个* Magic * 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

所以我的问题是:

  • 为什么TableController 用这种方式生成SQL?
  • 查询中间的* magic * guid是什么?(它会保持不变,直到我停止并重新启动应用程序,所以我不知道它是会话,客户端还是数据库上下文特定的)
  • 管道中究竟是哪些TableController对这些修改进行了修改IQueryable?我认为这是通过一些中间件步骤完成的,或者在Query()调用该方法后的请求中稍后执行的属性,但是我不能找到它。
提问于
用户回答回答于

controllerSettings.Services.Add(typeof(IFilterProvider), new TableFilterProvider());

注意:附加过滤器将在您的操作执行完成后执行并返回IQueryable

你可以检查EnableQueryAttribute.cs并发现OnActionExecuted将调用该ExecuteQuery方法并最终调用ODataQueryOptions.ApplyTo以将OData查询选项($ filter,$ orderby,$ top,$ skip和$ inlinecount等)应用于给定的IQueryable

根据我的理解,嵌套SQL语句由OData组件生成。调用之后ODataQueryOptions.ApplyTo,你的IQueryable已被修改,并且相关的sql语句也已被修改。我在我的常规Web API控制器中进行了如下测试,你可以参考它:

请求:

Get http://localhost:58971/api/todoitem?$top=2&$select=Text,Id,Version

在应用OData查询选项之前:

应用OData查询选项后:

扫码关注云+社区