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

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

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

当我试图找到一个实体框架问题的根源时,我会将它与TableController

我创建了以下设置。

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;     } 
A vanilla Web API 2 controller.
public class TodoItemsWebController : ApiController {      private MobileServiceContext db = new MobileServiceContext();     public TodoItemsWebController()     {         db.Database.Log += LogToDebug;     }      public IQueryable<TodoItem> GetTodoItems()     {         return db.TodoItems;     }

然而,这两个查询产生的SQL非常不同。对于常规的WebAPI控制器,您可以获得以下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]
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/tSACq6g

为什么TableController以这种方式生成SQL?

提问于
用户回答回答于

您的一个表在后端和客户端之间同步,因为如果这样,您将得到第二sql。

用户回答回答于

请求:

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

在应用OData查询选项之前:

在应用OData查询选项之后:

扫码关注云+社区