none
Problem updating datasource in Lightswitch

    Question

  • Hi,

    I have a problem with updating datasource in the Lightswitch application. The datasource is created directly from database and the references in the datasource are formed from the foreign key references in the database. The DB has been changed later and foreign key column switched from "allow null" to "not null". Now I have conflict with multiplicity in the lightswitch datasource reference and I get error messages when I try to update datasource. 

    How can I update datasource or change relationship multiplicity on my dataitems (automatically or manually)?

    Thanks in advance!

    Tuesday, October 23, 2012 8:07 AM

Answers

  • Hi Guest231 and Yann,

    Something for you to quickly try. Create new test LS project, add data source to your database and just select the two tables involved in the relationship in question. Report back.

    That may give a clearer error message (relationship not supported, 1-1 etc...), or it may even work (then you have valid new XML in LSML files from which to progress manually).


    Dave Baker | Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.


    Tuesday, October 23, 2012 9:04 AM
  • There's a bit of a Catch-22 here. You need to change the existing multiplicity of the relationship in LS (which you'd normally just do in the table designer), but you can't because that information comes from the RIA service (& therefore isn't changeable in LS), but the update datasource process doesn't seem to be able to cope with that.

    I think the test scenario that Dave suggested will be successful, because there's no "old" relationship for the update datasource process to have to change.

    Unless Dave can suggest something else, it might be a case of having to delete the existing datasource (which will remove the properties from any screens etc), then adding it again.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Tuesday, October 23, 2012 9:25 AM
    Moderator

All replies

  • "I get error messages"

    Can you tell us what the error messages say?


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Tuesday, October 23, 2012 8:43 AM
    Moderator
  • Error 12 jrfpkhcs..csdl(4161,6) : error 0113: Multiplicity conflicts with the referential constraint in Role 'ORGJ' in relationship 'FK_Table2_ORGJ'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'. Server
    Tuesday, October 23, 2012 8:49 AM
  • Hi Guest231 and Yann,

    Something for you to quickly try. Create new test LS project, add data source to your database and just select the two tables involved in the relationship in question. Report back.

    That may give a clearer error message (relationship not supported, 1-1 etc...), or it may even work (then you have valid new XML in LSML files from which to progress manually).


    Dave Baker | Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.


    Tuesday, October 23, 2012 9:04 AM
  • There's a bit of a Catch-22 here. You need to change the existing multiplicity of the relationship in LS (which you'd normally just do in the table designer), but you can't because that information comes from the RIA service (& therefore isn't changeable in LS), but the update datasource process doesn't seem to be able to cope with that.

    I think the test scenario that Dave suggested will be successful, because there's no "old" relationship for the update datasource process to have to change.

    Unless Dave can suggest something else, it might be a case of having to delete the existing datasource (which will remove the properties from any screens etc), then adding it again.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Tuesday, October 23, 2012 9:25 AM
    Moderator
  • Hello,

    I have just experienced the same problem and hence came to this thread, my datasource too is generated directly from the databaseand the foreign key column was changed to "not null".

    My solution in VS 2012 was as follows: right click on datasource and select 'update datasource'. In the 'Choose your database objects' deselecte the 2 tables and click 'Finish' to update the Datasource. Then richtclick on the Datasurce once more and select 'update datasource" again. Select the 2 tables and click finish. Now the entities generated from the tables have the proper Multiplicity properties 'one'-'many' instead of 'Zero or one'-'many'.

    Beware: all your screens based on these tables will be without any data item. In my case I was lucky to only have to redo one list - details screen.


    Jan D'Hondt - Database and .NET development

    Friday, January 11, 2013 2:36 PM
  • Same Problem: Switched from "Allow Null" to "Not allow null" and the datasource didn't update the "0..1" to "1" which leeds into the error described above.

    Removing the complete datasource or just a view tables will get you into the troubles @jandho described. For me it's a bug and should be fixed very soon.

    Wednesday, March 06, 2013 1:55 PM
  • I managed to fix this problem by editting the Common.lsml file and fixing the multiplicity on the assoc.

    Wednesday, March 13, 2013 12:46 PM
  • I think this is major bug in LS. In greater Applications this can be take hours or days to repair.

    Any other Workarounds or Suggestions to repair this Misbehaviour?

    I had to redefine 6 Screens because of this bug! Very annoying.

    Is the LW Team aware of this Problem?

    Thx for Answers

    Tuesday, April 23, 2013 3:07 PM
  • Things sometimes go wrong in LightSwitch, it's just a fact of development life.

    Without meaning to sound facetious, before initiating an operation like Update Database, make a backup of your solution. Then if you have a problem, simply restore the back up that you made before the attempt.


    Yann Duran
         - Co-Author of Pro Visual Studio LightSwitch 2011
         - Author of the  LightSwitch Central Blog

    FREE Download: Luminous Tools for LightSwitch
    (a Visual Studio productivity extension for LightSwitch)
     
    Click Mark as Answer, if someone's reply answers your question
    Click  Vote as Helpful, if someone's reply is helpful
     
    By doing this you'll help everyone find answers faster.

    Tuesday, April 23, 2013 4:48 PM
    Moderator
  • Thanks for the trick! So i haven't create all my views from scratch!!!

    Remove line

    Andreas

    Friday, May 31, 2013 2:37 PM
  • The only problem with your reasoning is that it implies complacency. The other people on here are correct. Microsoft is providing a tool which causes loss of data and loss of work. Lightswitch is great for putting together quick applications.

    What I have learned is that eventhough lightswitch is great at creating basic applications I will never release another one to a client. The problem I have is that I may save time on the front end but I always spend more time on the back end fixing problems like those detailed in this thread. There has not been one single application where I have not encountered some issue which causes more time to fix than that gained by using lightswitch.

    There appears to be a lightswitch road block at which time it would have been more efficient to create a WPF application.

    So, I create an application (lightswitch) for me to use internally and when it comes to releasing something to a client I will go back and write WPF. It is actually more efficient in the long run.

    By making posts as you do above you only cater to Microsoft employees that pass the buck off to the individuals. They in effect decide that problems like these are less important than enhancements which will allow their project managers to boast to upper management.

    For me, I would rather have a tool that works than one which can ding a bell but not make a sound. It's like having a car that looks nice but when you drive it off the lot the wheels fall off.

    Friday, August 02, 2013 1:56 PM
  • Sadly I agree with Stumple

    There is so much to love about Lightswitch but in using it I've come to realise that its hopeless for the kinds of jobs I wanted it to do.

    Here's my situation. I wanted to build some applications to legacy SQL server data and thought Lightswitch might make it easy. How wrong I was. It was a painful process. I have been here since the early days of Lightswitch and I can honestly say I'd have been far better off building these apps with .net web forms. There were a lot of issues and several nasty bugs.

    But finally, I got the applications delivered and they have done their job; just about. However, the other day I noticed a core table was missing some FK's. So I added them in SQL server and the app carried on working fine. But I wanted to make a minor change to a field and so I tried to "update datasource".

    This broke the entire application by throwing out the existing relationships, discarding all sorts of screen and data objects and rendering it impossible to re-compile.

    I use Subversion to manage my code and thank heavens I do. I can just revert when this happens. And believe me I have used it a lot.  But imagine if I hadn't?

    I have come to the conclusion that the only way out of this mess is to start again with a fresh project. Get the database fixed first and then re-build the whole app again from scratch using the original as a reference.  Since it was over a year ago that I built this app I cannot remember how I did much of the complex stuff and there is a lot of code in there. Its a big job.

    So, right now I am faced with a decision. To ditch LS and start again from scratch or to go through the process I describe above. If I stick with LS then what then happens if I need to add another FK to the table or the client decides to make some other alteration?  And this happens all the time in the real world.

    So, I am leaning towards abandoning LS. This is a shame but I cannot live with a tool that forces me to rebuild every time DB schema changes. So it looks like months of work and learning down the drain.

    Gus

    Friday, November 22, 2013 9:57 AM
  • Andreas: thank you for posting the sample. For large Lightswitch projects when importing the schema twice (once without and once with the updated table) makes it difficult to know which screens had the field removed, the manual edit of the lsml file is the answer for now. It worked perfect for us.

    However just a quick note of clarifiction for future readers: the OP had a table where the FK was updated from null to not null. This means the cardinality for that end of the relationship should have been updated in Lightswitch from "(zero or one) "  to "one". 

    For such a case in particular, we would actually want to add the attribute 'Multiplicity="One"', not remove it.

    From what I can tell, in the Association element, the AssociationEnd for the table to which the FK refers:

    'Multiplicity="One"' means cardinality of  one ("1..1") on that end

    'Multiplicity="Many"' means cardinality of "Many" on that end

    No 'Multiplicity' attribute means cardinality of  one ("0..1") on that end




    • Edited by mdisibio Thursday, April 03, 2014 2:08 PM
    Thursday, April 03, 2014 2:05 PM
  • For anyone else ending up at this thread, I also just had the issue where LS would not update the model correctly after a nullable property was changed in the underlying datasource on a file not involved in a relation.

    In my case I did not get a multiplicity error (specifically), LS just failed to update the relevant Entity details and compile correctly.

    All the lsml files were correct, and the issue was narrowed to the automatically generated code file for the datasource.

    In my case all I needed to do was search for the Property name of the entity in question and remove the Nullable<> return type definition from the public property definition (leaving global:: inner type intact) and then also removing the Nullable<> type definition from the On<EntityName>Changing() partial method definition.

    Recompile and all sweet.

    On a side note, future version of LS could really do with some improvement in tools that help re factoring of the datasource. The loss of screen collection data when data source items are simply renamed can be frustrating at times.

    Tuesday, April 15, 2014 12:18 PM
  • Hello

    I absolutely agree with this post. This is a K.O. bug for professional working. Let's see if it ever get's fixed.

    hansjörg

    Friday, June 27, 2014 10:20 AM
  • While it may not work for your particular situation, if there are tables in the legacy database that you use as read-only, I've found creating a simple View that references the table and handles any null/not-null conversions in its code can be a type of buffer/hedge against breaking a Lightswitch app when the table's schema changes. Yes, it adds another layer of abstraction between the app and the data, but I find it easier to alter a View to translate the new schema into the existing exposed data types and keep them consistent rather than directly connecting to a table whose schema might change.

    As I said, it's not a universal solution by any means, but it might help in some cases.

    Regards,

    R.T. Watkins

    Friday, June 27, 2014 6:24 PM