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

i'm trying bottom of entity framework issue when using tablecontroller

i've created following setup.

1. the basic todoitem example provided new mobile web api leverages entityframework, tablecontroller & 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;     } 

2. 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 tablecontroller code fine tooth comb, digging query method, proxying call via domainmanager add in where(_ => !_.isdeleted) modification iqueryable

yet 2 queries produce different sql.

for regular web api controller, following sql.

select      [extent1].[id] [id],      [extent1].[version] [version],      [extent1].[createdat] [createdat],      [extent1].[updatedat] [updatedat],      [extent1].[deleted] [deleted],      [extent1].[text] [text],      [extent1].[complete] [complete]     [dbo].[todoitems] [extent1] 

but tablecontroller, following chunk of sql has *magic* guid in middle of it, , results in nested sql statement. performance of goes complete garbage when start dealing of odatav3 queries $top, $skip, $filter , $expand.

select top (51)      [project1].[c1] [c1],      [project1].[c2] [c2],      [project1].[c3] [c3],      [project1].[complete] [complete],      [project1].[c4] [c4],      [project1].[text] [text],      [project1].[c5] [c5],      [project1].[deleted] [deleted],      [project1].[c6] [c6],      [project1].[updatedat] [updatedat],      [project1].[c7] [c7],      [project1].[createdat] [createdat],      [project1].[c8] [c8],      [project1].[version] [version],      [project1].[c9] [c9],      [project1].[id] [id]     ( select          [extent1].[id] [id],          [extent1].[version] [version],          [extent1].[createdat] [createdat],          [extent1].[updatedat] [updatedat],          [extent1].[deleted] [deleted],          [extent1].[text] [text],          [extent1].[complete] [complete],          1 [c1],          n'804f84c6-7576-488a-af10-d7a6402da3bb' [c2],          n'complete' [c3],          n'text' [c4],          n'deleted' [c5],          n'updatedat' [c6],          n'createdat' [c7],          n'version' [c8],          n'id' [c9]         [dbo].[todoitems] [extent1]     )  [project1]     order [project1].[id] asc 

you can see results of both queries here. https://pastebin.com/tsacq6eg

so questions are:

• why tablecontroller generating sql in way?

• what *magic* guid in middle of query? (it stay same until stop , restart app don't know if it's session, client or db context specific)

• where in pipeline tablecontroller making these modifications iqueryable? assume it's done through middleware step or on executed attribute later in request after query() method called, cannot life of me find it.

according description, did research , found azure mobile server sdk uses following code line under tablecontrollerconfigprovider.cs adding additional query related filters same actions queryableattribute enabling controller action support odata query parameters.

controllersettings.services.add(typeof(ifilterprovider), new tablefilterprovider()); 

note: additional filters executed after action has been executed , return iqueryable.

you check enablequeryattribute.cs , found onactionexecuted call executequery method , call odataqueryoptions.applyto applying odata query options ($filter, $orderby, $top, $skip, , $inlinecount, etc.) given iqueryable.

per understanding, nested sql statement generated odata component. after invoked odataqueryoptions.applyto, iqueryable has been modified , related sql statement has been modified too. did test in regular web api controller follows, refer it:

request:

get http://localhost:58971/api/todoitem?$top=2&$select=text,id,version 

before applying odata query options:

after applied odata query options: