我正在解决在使用TableController时与Entity Framework一起使用的问题。
我创建了以下设置。
使用新的Mobile Web API提供的基本TodoItem示例，利用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;
}
一个普通的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代码，其中有一个魔法Guid，结果是一个嵌套SQL语句。当你开始处理任何ODATAv3查询，如$top、$skip、$filter和$expand时，这个性能会完全变差。
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？查询中间的*magic* guid是什么？（它会一直保持不变，直到我停止并重新启动应用程序，所以我不知道它是会话、客户端还是DB上下文特定的）
TableController在管道的哪个位置对IQueryable进行修改？我假设它是通过某些中间件步骤或在请求后期的on executed属性中完成的，但我无论如何都找不到它。
$top和$skip来浏览API调用以执行初始DB同步。 - Eoin Campbell