Why is Entity Framework generating the following nested SQL for Azure Mobile Services Table Controllers

Question

I'm trying to get to the bottom of an entity Framework issue when using it with a TableController

I've created the following setup.

The basic TodoItem example provided with a new Mobile Web API which leverages EntityFramework, TableController & the default 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;
    }

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;
    }

I've gone through the tablecontroller code with a fine tooth comb, digging into the Query method, which is just proxying the call via the DomainManager to add in the Where(_ => !_.IsDeleted) modification to the IQueryable

Yet the two queries produce VERY different SQL.

For the regular Web API Controller, you get the following 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]

But for the TableController, you get the following chunk of SQL which has a *Magic* Guid in the middle of it, and results in a Nested SQL statement. The performance of this goes to complete garbage when you start dealing with any of the ODATAv3 queries like $top, $skip, $filter and $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

You can see the results of both queries here. https://pastebin.com/tSACq6eg

So my questions are:

Why is the TableController generating the SQL in this way?
What is the *magic* guid in the middle of the query? (it will stay the same until I stop and restart the app so I don't know if it's session, client or DB context specific)
Where exactly in the pipeline is the TableController making these Modifications to the IQueryable? I assume it's done through some middleware step or an on executed attribute later in the request after the Query() method is called, but I cannot for the life of me find it.

I guess this is related with the mobile server SDK to implement Odata queries. I found if we use var items= Query().ToList(), the sql query is right as the web api's. But we couldn't use the Odata queries. — Brando Zhang, Jul 26 '17 at 09:35
that's not really an option though because the client consumer will rely on using Odata $vars. e.g. on initial load it will use `$top` & `$skip` to apge through the APIs calls to perform the initial DB Sync. — Eoin Campbell, Jul 26 '17 at 11:13
This is because EntityDomainManager downloads and keeps values of fields along with each row for concurrency checks. And Guid is kind of ETAG from the source https://github.com/Azure/azure-mobile-apps-net-server/blob/master/src/Microsoft.Azure.Mobile.Server.Entity/EntityDomainManager.cs — Akash Kava, Jul 31 '17 at 19:58

score 5 · Accepted Answer · answered Aug 03 '17 at 06:21

According to your description, I did some research and found that Azure Mobile Server SDK uses the following code line under TableControllerConfigProvider.cs for adding additional query related filters for the same actions with the QueryableAttribute for enabling a controller action to support OData query parameters.

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

Note: the additional filters would be executed after your action has been executed and return the IQueryable.

You could check EnableQueryAttribute.cs and found that OnActionExecuted would call the ExecuteQuery method and eventually call ODataQueryOptions.ApplyTo for applying OData query options ($filter, $orderby, $top, $skip, and $inlinecount, etc.) to the given IQueryable.

Per my understanding, the Nested SQL statement is generated by OData component. After invoked ODataQueryOptions.ApplyTo, your IQueryable has been modified and the related sql statement has been modified too. I did some test in my regular Web API Controller as follows, you could refer to it:

Request:

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

Before applying OData query options:

After applied OData query options:

score 1 · Answer 2 · answered Aug 02 '17 at 11:43

Your one of the tables being synced between the backend and client because if that your are getting 2nd sql.

Read more here : https://documentation.devexpress.com/wpf/17927/Common-Concepts/Scaffolding-Wizard/Tutorials/Building-Outlook-Inspired-and-Hybrid-UI-Applications/Lesson-3-Customize-Layout-of-the-Collection-Views

Why is Entity Framework generating the following nested SQL for Azure Mobile Services Table Controllers

2 Answers2