locked
Need a generic method to access a LightSwitch query RRS feed

  • Question

  • Is there a generic way to access specific queries on the server by passing in a string for the DataService, a string for the query name, and a string for the query parameter.  Something like this:

    string myDataWorkspace = "ApplicationData";
    string myModeledQuery = "CustomersByName";
    string myParam = "Smith";
    
    int queryCount = ctx.DataWorkspace[myDataWorkSpace][myModeledQuery](myParam).Count();

    Assume ctx in the above example is a LightSwitch ServerApplicationContext.

    I tried looking through the weakly typed api but can't seem to find a solution.  Appreciate your help.


    • Edited by Hessc Tuesday, January 12, 2016 6:13 PM
    Tuesday, January 12, 2016 6:12 PM

Answers

  • Glad it works.  Not sure why it's much slower than ajax if you're only selecting top(1).  I wonder if you return a promise then it would seem faster since the above code might be blocking on every execution - which would be often if your params are bound to screen and users change them one at a time. 

    Another thing about changing params on the screen is you can wait to execute the query until all params by binding user screen property rather than the query parameter and set or clear the query params when ready.

    I agree a params array would be nice, but that's a lot of change on both server and client.  You could consider using a generic OData filter as alternative in some cases like so:

    var myDataSource = "ApplicationData";
    var myQuery = "CustomersByName";
    var myFilter = "(Name eq Smith) and (City eq Seattle)";
    
    myapp.getTotalCount = function (myDataSource, myQuery, myFilter) {
        return myapp.activeDataWorkspace[myDataSource][myQuery](myFilter)
            .top(1)
            .includeTotalCount()
            .filter(myFilter)
    };
    var count;
    myapp.getTotalCount(myDataSource, myQuery, myFilter)
            .execute()
    	.then(function (result) {
    	    count = result.totalCount;
    	    }, function (error) {
    		count = -1;
    	});

    (Air-code again - also made change to return a promise)

    HTH,

    Josh

    PS...fixed the 'return =' 


    • Edited by joshbooker Wednesday, January 13, 2016 10:40 PM
    • Marked as answer by Hessc Thursday, January 14, 2016 3:45 AM
    Wednesday, January 13, 2016 10:38 PM

All replies

  • I spent some time trying to figure this out using the various interfaces available in Microsoft.LightSwitch and Microsoft.LightSwitch.Details and still no luck.  I just need a simple way to get the total record count from any query.  The count on the client only includes records that have loaded into the visual collection.  It would be nice if there was a way to get the total count on the client without having to use an AJAX call to Web API to execute the query a second time, but if I have to do that, I at least don't want to have to write a specific method for each query.  Surely it can't be this difficult to construct a generic method in LightSwitch... 
    • Edited by Hessc Tuesday, January 12, 2016 9:21 PM
    Tuesday, January 12, 2016 9:16 PM
    • Edited by joshbooker Wednesday, January 13, 2016 2:14 PM
    Wednesday, January 13, 2016 1:32 AM
  • Josh, 

    Thanks, this is definitely much less setup than using the Web API workaround!  I would still like to know how to make a generic method that can call a dataservice query if given the necessary paramters for dataService, queryName, and queryParams -- but this does address my current issue.  Thanks again.

    Wednesday, January 13, 2016 2:06 AM
  • Hessc,

    Not sure about the Server Context, but I bet you can do it in js like so (air-code not tested):

    var myDataSource = "ApplicationData";
    var myQuery = "CustomersByName";
    var myParam = "Smith";
    
    myapp.getTotalCount = function (myDataSource, myQuery, myParam) {
        myapp.activeDataWorkspace[myDataSource][myQuery](myParam)
            .top(1)
            .includeTotalCount()
            .execute()
    	.then(function (result) {
    	    return result.totalCount;
    	    }, function (error) {
    		return -1;
    	});
    };
    
    var count = myapp.getTotalCount(myDataSource, myQuery, myParam)

    HTH,

    Josh

    • Edited by joshbooker Wednesday, January 13, 2016 10:11 PM code fix
    Wednesday, January 13, 2016 2:43 PM
  • Josh,

    This indeed works.  A couple things though:

    You can remove the = in the return statements (air code, it happens)

    The javascript query can be quite a bit slower than the Web API query executed on the server.  Especially where there are thousands of records.  My example was made to be as simple as possible, but in reality most queries have multiple parameters, and when changing parameters on the screen, it can bog down a lot while trying to get the record count (doesn't happen with the AJAX method).

    This works great for queries with one parameter, as in my example, but to make it more generic I tried to make it accept an array or object with multiple parameters and I found parsing those to be difficult. Multiple parameters must be inserted like (p1, p2, p3, etc.), and they need to be in the correct type, (i.e. number, string, date, bool).  I suppose I could work around it by having all queries accept one JSON string parameter and parsing it out on the server, and I do that in many queries, but not all (at least not yet).  

    In any case, thanks a bunch for your help! 

    Wednesday, January 13, 2016 9:54 PM
  • Glad it works.  Not sure why it's much slower than ajax if you're only selecting top(1).  I wonder if you return a promise then it would seem faster since the above code might be blocking on every execution - which would be often if your params are bound to screen and users change them one at a time. 

    Another thing about changing params on the screen is you can wait to execute the query until all params by binding user screen property rather than the query parameter and set or clear the query params when ready.

    I agree a params array would be nice, but that's a lot of change on both server and client.  You could consider using a generic OData filter as alternative in some cases like so:

    var myDataSource = "ApplicationData";
    var myQuery = "CustomersByName";
    var myFilter = "(Name eq Smith) and (City eq Seattle)";
    
    myapp.getTotalCount = function (myDataSource, myQuery, myFilter) {
        return myapp.activeDataWorkspace[myDataSource][myQuery](myFilter)
            .top(1)
            .includeTotalCount()
            .filter(myFilter)
    };
    var count;
    myapp.getTotalCount(myDataSource, myQuery, myFilter)
            .execute()
    	.then(function (result) {
    	    count = result.totalCount;
    	    }, function (error) {
    		count = -1;
    	});

    (Air-code again - also made change to return a promise)

    HTH,

    Josh

    PS...fixed the 'return =' 


    • Edited by joshbooker Wednesday, January 13, 2016 10:40 PM
    • Marked as answer by Hessc Thursday, January 14, 2016 3:45 AM
    Wednesday, January 13, 2016 10:38 PM
  • Thanks Josh!  The promise solves the blocking and the OData filter works for multiple params.
    Thursday, January 14, 2016 3:46 AM