locked
Adding FK in SQL Server Management Studio trashes my Lightswitch project. RRS feed

  • Question

  • Perplexing problem.

    I have a fully functional Lightswitch project that needs just one more feature to be complete...  Cascade Delete.

    I was told that handling the Cascade Delete on the SQL server was the way to go but the three tables involved are standalone tables in SQL with all the key relationships being generated in the Entity Designer inside Lightswitch.  It's not a terribly complicated key structure.  There is a One to Many relationship between table 1 and table 2 and also a One to Many relationship between table 2 and 3.  Simple enough.

    I created the FK constraints in SQL and configured Cascade Delete (and Cascade Update I think) and then opened my Lightswitch project so I could test the Cascade Delete...  I did not update the datasource in Lightswitch. 

    The Cascade delete functioned flawlessly as did all the other functions of the project.  Then I started thinking about the fact that I had not updated the datasource in my project and although the Cascade Delete I had added in SQL Server Management Studio was indeed working exactly as expected, I  decided to be on the safe side and update the datasource.

    Immediately after updating the datasource my project would no longer run.  It loads fine and issues the first screen as it should, but any attempt to access the database on the SQL server throws an error... "Data Service Operation Exception was Unhandled by User Code".  It then says to check the Server Logs... which of course tell me nothing.

    My prior experience with Lightswitch has taught me to backup everything before I make any changes and this time my caution paid off...  I had created a full backup of both the SQL Server and my Lightswitch project just prior to adding Cascade Delete to SQL.

    I restored my SQL (data) from backup and restored my Lightswitch project and once again my project was working perfectly (without Cascade Delete). I then re-created the FK Constraints in SQL and opened my Lightswitch project and sure enough... Cascade Delete was working perfectly again. I then updated the datasource and again the update of the datasource broke my project. 

     Sometimes I get the error I mentioned above and sometimes I just get a Red X and a message that tells me to check my network connection.

    Sorry for the length of my post but I didn't want to leave anything out in case you needed it.     

    Friday, September 6, 2013 6:04 AM

Answers

  • Can you clarify something for me to make sure I have an accurate understanding of your situation?  To begin with, you did not have any relationship defined within the SQL database rather you attached to the tables with LightSwitch and created the relationships within the LightSwitch Entity Designer.  After this you created the relationships within SQL and configured them to cascade delete.  You then updated the data source without explicitly deleting the relationships you defined.

    If this is indeed the case, I think you would need to delete the relationships you defined before updating the data source.  Updating the data source will then bring in the relationships defined within SQL correctly.  After updating the data source, you will need to fix up any screens or queries that referenced the affected relationships.

    Friday, September 6, 2013 1:41 PM

All replies

  • Can you clarify something for me to make sure I have an accurate understanding of your situation?  To begin with, you did not have any relationship defined within the SQL database rather you attached to the tables with LightSwitch and created the relationships within the LightSwitch Entity Designer.  After this you created the relationships within SQL and configured them to cascade delete.  You then updated the data source without explicitly deleting the relationships you defined.

    If this is indeed the case, I think you would need to delete the relationships you defined before updating the data source.  Updating the data source will then bring in the relationships defined within SQL correctly.  After updating the data source, you will need to fix up any screens or queries that referenced the affected relationships.

    Friday, September 6, 2013 1:41 PM
  • Thank you for your response.

    I think I understand what you mean however, I need to ask a few follow-up questions.

    I inherited the tables in SQL which are comprised of compound keys that are also data fields in the records.  As an example: The key to Table A is two fields; Client, Job#... the key to Table B is three fields; Client, Job#, Order#... the key to Table C is Client, Job#, Order#, Seq#.

     In the Lightswitch Entity Designer I created a relationship in Table A to Table B using the compound key of Table A as the FK in Table B.  Lightswitch created a Collection in Table A which I named “JobDetails”.  If I understand you correctly then in order for the Cascade Delete I created in SQL to work correctly I will need to remove the relationship I created in the Entity Designer?  But what happens to all the references to “JobDetails” I have running through the rest of the project?  What do I replace them with?  

    The relationships I created in the Entity Designer are numerous and involve many fields that also use the keys of the tables.  In fact, there are likely few (if any) relationships that don’t involve the keys.  Does that mean I will need to remove essentially all the relationships I created in the Entity Designer and recreate them in SQL?   If so that’s a huge amount of work and it might be easier to just handle the delete of child records using the for each method.

    Thank you for you assistance

    Friday, September 6, 2013 2:52 PM
  • You do not need to define all of your relationships within SQL.  I am simply saying that for the two you defined within SQL with the cascade delete behavior, you will need to deleted the corresponding relationships you modeled within the LightSwitch Entity Designer and then update the data source at which point the ones you defines within SQL will get imported correctly. 

    Does that make sense?

    Friday, September 6, 2013 8:28 PM
  • Yes... thank you.  I'll give it a try and let you know. 
    Friday, September 6, 2013 10:08 PM
  • One more question...

    Will it be necessary to remove every relationship in the Entitiy Designer that makes use of the same keys (or any portion of the same keys) that I am creating in SQL?

    Example:  Client is used in relationships with several other tables in the db, not just Table A, B and C.  So if I create the relationship in SQL between Table A, B and C that uses Client as part of the key, do I also need to remove all relationships in the Entity Designer that use Client?

    What if I have a realtionship created using the Entity Designer that connects Table A to Table D using Client?   Such as a Client name and address table that uses Client as the key.  So the relationship in the Entity Designer would be a one to many between the client name and address table (Table D) and the Orders table (Table A).  Can that relationship stay in the Entity Designer?

    Thanks      

    Friday, September 6, 2013 10:55 PM
  • Holy crap!  It worked.  I left all the relationships in the Entity Designer as they were and only removed the ones that I created in SQL.  

    I see I'll need to rebuild some "other screen data" stuff that is now missing but the overall function of the screens appears to be as it was before. 

    Thank you.

    Friday, September 6, 2013 11:48 PM