locked
Using Lightswitch with Azure Mobile Services RRS feed

  • Question

  • Hi,

    Been trying to use a existing LS SQL database with Mobile Services but it's not working. Why?

    1. LS publish the database schema with the dbo.Teblename

    Mobile Services use the servicename first in the schema lika myservice.tablename.

    To make it work you have to drop and change the LS schema to myservice.tablename. That is not working because you then cannot use the LS application anymore.

    This is a dead end and it's impossible to make a workaround, or?

    Any thoughts here?

    Regards

    Sven


    Sven Elm

    Wednesday, October 8, 2014 5:46 AM

Answers

  • Either the LightSwitch app or the Mobile Service need to attach to the database as external (using the LightSwitch terminology).  There's 2 approaches:

    1)  Create a mobile service from the ground up using the Mobile Service "way" of creating tables and then have an LS app attach to that database as an external data source

    2)  Create a LightSwitch app from the ground up and have write custom controllers in your Mobile Service that either connect to the LightSwitch DB or the OData endpoint.

    Can you describe what you did to get to the point where you discovered the naming convention mismatch?

    (just a note that I have used approach 1 in mobile services that I am developing, i.e. Mobile Service used via devices and a web front end using LightSwitch)


    David Kidder | Senior SDET | Microsoft | LightSwitch

    • Proposed as answer by Angie Xu Tuesday, October 21, 2014 6:21 AM
    • Marked as answer by Angie Xu Thursday, October 23, 2014 3:27 AM
    Monday, October 13, 2014 4:32 PM
    Moderator

All replies

  • Anyone?

    //Sven


    Sven Elm

    Monday, October 13, 2014 1:58 PM
  • I think we have a great opportunity here to build native apps through Xamarin in VS2013 with Azure Mobile Services with offline sync.

    We can use our existing databases generated with LS and therefore produce SL desktop applications, html clients and native apps in the same environment and get offline support.

    The only problem is the schema where Azure Mobile Services uses the name of the service as database schema.

    Either we can in a easy way get control over LS schema, by default dbo.tablename or map the service Mobile service schema to the dbo.

    No one else having problem with 3G/4G blind spots?

    Sven


    Sven Elm

    Monday, October 13, 2014 2:08 PM
  • Either the LightSwitch app or the Mobile Service need to attach to the database as external (using the LightSwitch terminology).  There's 2 approaches:

    1)  Create a mobile service from the ground up using the Mobile Service "way" of creating tables and then have an LS app attach to that database as an external data source

    2)  Create a LightSwitch app from the ground up and have write custom controllers in your Mobile Service that either connect to the LightSwitch DB or the OData endpoint.

    Can you describe what you did to get to the point where you discovered the naming convention mismatch?

    (just a note that I have used approach 1 in mobile services that I am developing, i.e. Mobile Service used via devices and a web front end using LightSwitch)


    David Kidder | Senior SDET | Microsoft | LightSwitch

    • Proposed as answer by Angie Xu Tuesday, October 21, 2014 6:21 AM
    • Marked as answer by Angie Xu Thursday, October 23, 2014 3:27 AM
    Monday, October 13, 2014 4:32 PM
    Moderator
  • Hi, thanks for your reply. Is it even possible to write custom contollers for the mobile service. Do you have an example? I also did your first way and it worked but it's not possible for me. I have 100 clients and the same amount databases out there and it's growing every month. What i'm missing is offline sync for some scenarios in my app.

    Sven Elm

    Monday, October 13, 2014 6:00 PM
  • Hi, thanks for your reply. Is it even possible to write custom contollers for the mobile service. Do you have an example? I also did your first way and it worked but it's not possible for me. I have 100 clients and the same amount databases out there and it's growing every month. What i'm missing is offline sync for some scenarios in my app.

    Sven Elm


    It could be so easy and I was so near, but that schema issue got in my way :(

    Sven Elm

    Monday, October 13, 2014 6:04 PM
  • You can just rip out the code in the controller method bodies and put whatever you want in there.  Mobile Service is essentially a WebAPI deployed to an Azure Website (with the major value-add being the Client libraries and push notifications, etc.)

    Sorry, I can't find the custom controller code that I tried previously.  Basically you just need to know that on the client side of the application, if you use the Table classes defined in the client libraries to do the requests to the server, they expect the controllers to be routed a certain way (whatever way they are routed when you setup a ground-up mobile service) and that you will need to define a Data Transfer Object (DTO) class on the client that has the same properties as whatever you are sending over from the server (e.g. an EntityFramework entity).  This is the same thing you need to do for client code if you use a ground-up Mobile Service.


    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 13, 2014 6:38 PM
    Moderator
  • Hi, yeah I get that and did some tests before in the table classes but I'm not sure how to make the service work with another schema or rout them to the dbo schema. I looked through every post I could find regarding this issue and I found nothing that works beside renaming the Dbo schema to the service name. i'm lost here here.

    Sven Elm


    • Edited by Sven Elm Monday, October 13, 2014 7:07 PM
    Monday, October 13, 2014 7:06 PM
  • Sven,

    I think the route to follow is Angular and PouchDB as intermediair i.a.w. MEAN Stack?

    Have you read the arctiles from Michiel en Paul. Could be the Rabbit in the Head for the Roadmap


    Eric


    Monday, October 13, 2014 7:31 PM
  • Hi Dave,

    It would be super sweet to use LS intrinsic db in Azure Mobile Services.  I came across the naming convention issue (and this thread) after trying the steps in this article:

    http://blogs.msdn.com/b/jpsanders/archive/2013/05/24/using-an-existing-azure-sql-table-with-windows-azure-mobile-services.aspx

    Is there any way to change the schema name for intrinsic db to something other than 'dbo'?

    and how about rename 'Id' to 'id' lowercase? I get screen validation errors when I do that in table.lsml

    Can these be done in SSDT database project somehow?

    TIA,

    Josh

    Monday, October 27, 2014 4:35 PM
  • Josh, let me think about this for a while and I will respond later this week.  Just to clarify, you'd like to have a LightSwitch app that has an intrinsic database, then hookup a mobile service to the intrinsic database, right?  What exactly are the issues that you run into with this?  (I know that they have been alluded to here, but specifics would help me out).  Thanks

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 4:40 PM
    Moderator
  • Nice, the post is alive again. I want the same and have tried a lot of coding without any luck. There are two issues. The firts is easy. LS uses ID and azure uses id. All you have to do is some conversion, not a big deal. Second, the schema cannot be resolved with mapping. Ls uses db and Azure the name of the service. If we could map the schema name someway it would be worth alot. This is the only issue, nothing else.

    Sven Elm

    Monday, October 27, 2014 4:53 PM
  • I don't think that tightly coupling the LS database schema to the MobileServices conventions is the right way to go about doing this.  That schema and their conventions could change at pretty much any time (Mobile Services is still a very young feature).  In my opinion, the value for Mobile Services is more on the Client APIs than the Server APIs (I am assuming the the EntityData class is really the only thing that is requiring the schema to be setup in such a manner).  You can still get the Client APIs to work if you write the Web API code using Entity Framework (database first approach).  I think that MobileServices is eventually going to need to fully support the "Bring your own database" approach, because it is such a popular model.

    While it's a little more work to have to write the EntityFramework code to get data from the database instead of using EntityData, I think it's a small price to pay to keep the whole setup more flexible and less rigid.

    (These opinions are my own and don't necessarily reflect Microsoft)   :) 


    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 5:33 PM
    Moderator
  • As Sven said, those are the two issues that I've come across so far:

    1) Lowercase ID field:  LS uses 'Id' int, AMS uses 'id' nvarchar.  I believe from the article I linked above, that AMS would be alright with int, but the lower case 'id' is critical for some reason.  I'm not sure what conversion or where Sven is doing to make this a non-issue, but I have changed an intrinsic table to 'id' by modifying table.lsml and everything seems fine in the designer.  but when I create new screen set, I get errors saving add\edit screen 'Id is required'.

    2) Schema name for LS tables are 'dbo' while AMS uses the service name.  It would be nice to be able to set the schema name on LS intrinsic tables for this scenario and others.

    I tried defining a synonym in the database and then adding to Mobile Service

    CREATE

    SYNONYM servicename.Accounts

    FOR dbo.Accounts

    but didn't fool nobody.  Got 'table already exists' error in azure.

    I figured the unique schema was about multi-tenant separation since I believe you can scale between share & dedicated resources.

    I agree they will have to better support 'bring your own database'

    I'd still love to know if there's a way to change schema name of intrinsic tables.

    TIA,

    Josh

    Monday, October 27, 2014 5:49 PM
  • Thanks David. Maby you are right. Maby there will be a change/fix for this in future release. it's sad though. It could be so easy and a great offline option for LS in certain situations. I leave this option for now and hope for a fix in the future.

    Sven Elm

    Monday, October 27, 2014 5:53 PM
  • Josh, after taking what action did you get the "table already exists" error?

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 5:59 PM
    Moderator
  • Hi Josh. Found an article on the id issue and mapping for this. Dont know if it works though because I cannot get past the schema issue. So maby I was a bit fast in making it a non issue. Sorry

    Sven Elm

    Monday, October 27, 2014 6:00 PM
  • Hi josh, Don't think you can use synonym on Schema. Nice thinking though.

    Sven Elm

    Monday, October 27, 2014 6:07 PM
  • You guys have peaked my interest.  I am going to email the guy that wrote the blog post and ask him.  I have chatted with him before on some schema related stuff with Mobile Services

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 6:12 PM
    Moderator
  • And of course, he's out of office this week :-)

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 6:14 PM
    Moderator
  • Create Table in Azure Mobile Service seems to update existing tables (adds the _createdat, _deleted fields, etc.)  I wondered if it would do so to dbo.accounts is I created a synonym in the db exposing dbo.accounts as servicename.accounts.

    After creating a synonym in the database, 'Create Table' in Azure Mobile Service fails with an error 'table already exists'

    While we're on the subject here's why I want this:

    I believe Azure Mobile Services as a backend has some advantages over LS backend for html javascript apps.

    familiar promise pattern

    javascript query syntax is better

    love mssql object for joins, stored procs, maybe tvfs(?)

    SignalR push built-in

    Offline sync

    server side table CRUD scripts (think real computed props & defaults in HTML client)

    All in javascript.

    Josh

    Monday, October 27, 2014 6:16 PM
  • Thanks David. Love to find a solution because I can see so many great things in this.

    Sven Elm

    Monday, October 27, 2014 6:17 PM
  • Josh, I totally get this scenario and agree that it is powerful.  When you say "Create Table seems to update existing fields", are you saying that upon creating a record in Mobile Services is when you get the "table already exists" error?

    That would make sense because what seems to be happening is Mobile Services (which I will be referring to as "Zumo" since it's shorter) or possibly Entity Framework doesn't think that there is an Accounts table since it is named differently.  Zumo uses the code-first approach so the database tables are created "just-in-time".

    I may have missed it, but have you guys tried renaming the SQL tables and seeing if the LightSwitch app still worked?


    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 6:22 PM
    Moderator
  • Hi josh, Don't think you can use synonym on Schema. Nice thinking though.

    Sven Elm

    The synonym defined above would work great a runtime across schemas.

    SELECT * FROM servicename.Accounts would return the exact same as

    SELECT * FROM dbo.Accounts.  I believe even DDL works on synonyms.

    My thinking was if azure can modify and existing table, then maybe it would do the same to dbo.Table through the synonym service.Table.  But no go. 

    Prolly the DDL scripts check IFEXISTS type = table, when one wasn't found it proceeded - until failure cuz (with synonym defined) there was 'a table by the same name'.

    Later,

    Josh

    Monday, October 27, 2014 6:31 PM
  • Hi, if you mean the schema name then yes and the LS app stopped working.

    Sven Elm

    Monday, October 27, 2014 6:38 PM
  •  When you say "Create Table seems to update existing fields", are you saying that upon creating a record in Mobile Services is when you get the "table already exists" error?

    The only place I've seen anything remotely like "bring your own db" is the article I linked above.  The author explains how you can use an existing azure SQL table in 'ZUMO'.  Basically you change the schema name to that of your zumo service then you go through existing db > create table in zumo.

    If the table exists in the database (as it does in this case)  zumo alters the table adding fields such as (_createdat, _deleted, id, etc).  Altering the fields of an existing SQL table is what I mean by 'Create Table seems to update existing fields.'  Not sure about just in time, but that's my experience.

    I only got table exists error when I have synonym defined - which makes sense and would be out of the ordinary - I suppose.

    If you change schema on intrinsic tables, LS just creates new ones with 'dbo' on next run.

    It would be really swell to know if there was a way to set the schema on intrinsic.

    HTH,

    Josh

    Monday, October 27, 2014 6:44 PM
  • Sure that makes sense about the created_at fields, but you wouldn't be getting an error about the table already existing unless it was attempting to create a table (as opposed to altering it).

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 6:50 PM
    Moderator
  • Basically, you aren't going to want Zumo to ever be updating your database schema, you'd want to configure the EntityFramework context to NEVER update the database schema as you'd only want this to be done from the LightSwitch side of the world (i.e. someone in this relationship needs to be the boss and in the case we are talking about that should be LightSwitch).

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 6:55 PM
    Moderator
  • It was indeed attempting to create table because there was no table by that name existing.  What existed was a synonym by that name.  Thus the error.

    I had a LS table dbo.Accounts and synonym:

    CREATE SYNONYM servicename.Accounts

    FOR dbo.Accounts

    so maybe if the zumo DDL checked for the table:

    IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[servicename].[Accounts]') AND type in(N'U'))

    none exists so it proceeds to create but fails thus the error.

    Make sense?

    Josh

    Monday, October 27, 2014 6:58 PM
  • Yeah, Lightswitch rules, but need some help to be great

    Sven Elm

    Monday, October 27, 2014 6:59 PM
  • @Josh, yup, we're on the same page

    David Kidder | Senior SDET | Microsoft | LightSwitch

    Monday, October 27, 2014 7:01 PM
    Moderator
  • Basically, you aren't going to want Zumo to ever be updating your database schema, you'd want to configure the EntityFramework context to NEVER update the database schema as you'd only want this to be done from the LightSwitch side of the world (i.e. someone in this relationship needs to be the boss and in the case we are talking about that should be LightSwitch

    That would be fine, if we could get the required zumo schema in the intrinsic tables.

    Thanks Dave, Let us know when your guys get off vaca.

    Monday, October 27, 2014 7:07 PM
  • Hey Dave Have you had a chance to follow up on this?
    Monday, November 17, 2014 1:19 AM
  • Any news on this?

    It would be great if we could use the database from lightswitch to create an azure mobile service.

    Martin 

    Saturday, March 7, 2015 10:26 PM
  • No news. Waiting.....

    Sven Elm

    Sunday, March 8, 2015 1:13 AM
  • Hello people! This topic is excellent. The Visual Studio 2015 brought something new for the matter?
    Thursday, May 26, 2016 1:39 PM
  • This looks promising:

    http://stackoverflow.com/questions/36036455/azure-mobile-app-using-existing-database

    Wednesday, November 16, 2016 2:56 PM