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:

enter image description here

after applied odata query options:

enter image description here


Comments

Post a Comment

Popular posts from this blog

go - serving up pdfs using golang -

Image
just wondering if guys can code. it works serving images not pdf brochure. in advance func main(){ http.handlefunc("/", func(w http.responsewriter, r *http.request){ filename := "/var/www/filedipenser/brochure.pdf" streampdfbytes, err := ioutil.readfile( filename ) if err != nil { fmt.println(err) os.exit(1) } b := bytes.newbuffer(streampdfbytes) w.header().set("content-type", "application/pdf") if _, err := b.writeto(w); err != nil { fmt.fprintf(w, "%s", err) } w.write([]byte("pdf generated")) }) err := http.listenandserve(":4111", nil) if err != nil { log.fatal("listenandserve: ", err) fmt.println(err) } } the code bit inefficient appears working. however, pdf viewers might sensitive extraneous output adding end of http response stream...
Read more

python - Best design pattern for collection of objects -

i have following architecture: +-----------------+ | collection | | | | | | | | | +-----------------+ | ^ | | create() | | | | v | getitem(b) +------------+ | | item | | | | | | +------+ | | +------------+ a class managing collection of items, creates items. , these items may need other items collection. the actual code in python, , collection passes parameter created item . see it, bad practice. improvement can see pass few functions of collection needed item , instead of whole instance. for example: class collection: getitem(self, id): ... createitem(self, id): item = item(id, self) # <-- pass self ... class item: __init__(self, id, col): ... col.getite...
Read more

how to add preprocess loader in webpack 2 -

i trying add preprocess loader in webpack 3. have installed successfully. not working here's webconfing file var debug = process.env.node_env !== "production"; var webpack = require('webpack'); var path = require('path'); module.exports = { context: path.join(__dirname, "src"), devtool: debug ? "inline-sourcemap" : null, entry: "./js/app.js", module: { loaders: [ { test: /\.jsx?$/, exclude: /(node_modules|bower_components)/, loader: 'babel-loader!preprocess-loader?+debug', // query: { // presets: ['react', 'es2015'], // plugins: ['react-html-attrs', 'transform-class-properties', 'transform-decorators-legacy'], // } }, { test: /\.css$/, loader: "style-loader!css-loader!sass-loader" } ] }, output: { path: __dirname + "/src/", f...
Read more