locked
Adding a View to an existing external datasource breaks the project. RRS feed

  • Question

  • I added a simple SQL View to an external database and then ran "Update Datasource" in my Lightswitch project.  Suddenly my project is throwing 53 errors.  I didn't add any code nor did I do anything other than update the datasource after adding a View to SQL.  I did not change the original datasource in any way other than adding a SQL View although I did click the checkbox for the View to be included in the update datasource.

    It appears that adding the View has broken relationships in the Entity Designer.  Why should this happen and shouldn't there have been a warning that Lightswitch was about to throw a hissy fit because I added a simple View.

    So rather than attempt to track down and recreate all the broken relationships, I decided to restore the project from the full backup of the Project file I had taken immediately before adding the SQL View in the "Update Datasource".  The restore of the project completed without any problems but when I ran the project I had the same problem... broken links.

    I then restored the entire Project Directory from a backup taken the day before I added the View... again no joy.  The same errors.

    I'm confused.  How can it be that a backup of a fully functional project (taken the day before I added a SQL View to the datasource) is broken in the exact same way as the project after the SQL View was included in the data source, which is the thing that broke the project to begin with?   Have I violated the temporal time line where effect now precedes cause? 

       

    Tuesday, February 18, 2014 10:46 AM

All replies

  • Additional Information...

    I restored the missing relationships which also required that I restore the Child record datagrids on any screens that used the named relationships (holy crap!).

    I also had to change a line of code I didn't create... it was up in the system generated Screen.vb file.  The code referenced a Boolean value using the operator "is" which produced an error.  I changed the "is" to "=" and the error was gone.  I have no idea why the system generated bad code.

    Lastly, I was getting the error... "Could not copy the file .....\Client\bin\Debug\projectname.Client.xap "because it was not found".   I opened the location using Windows Explorer and other than a long list of directories there were only 4 files at that location none of which was the file the system was looking for.  I then opened the same location in the backup I had taken prior to this mess happening and the backup of that directory contains dozens and dozens of files including the file the compiler is saying it can't find.  (How can all of this have been caused by simply adding a SQL View to the datasource?) 

    I restored the projectname.Client.xap file and then ran a Rebuilt Solution and the error was gone.

    Is there any way to avoid all this mess when adding a SQL View to the data source?

    Tuesday, February 18, 2014 12:07 PM
  • Additional information...

    The error on the screen.vb code is back.  I closed the project and when I reopened it the error came back.

    The exact error is: 'Is' operator does not accept operands of type 'Boolean'.  Operands must be reference or nullable types.

    The code where the error occurs in the Screen.vb file is: (the error is Bold and underlined)

    Private Function ActivityDetailsQuery() as Global.Microsoft.Lightswitch.IdataServiceQueryable

    Return Global.Microsoft.Lightswitch.DataServiceQueryable.Where ( _

    Me.Screen.DataWorkspace.JobMgmtData.ActivityDetails, _

    Function(a) (a.web_Meeting is Nothing))

    Tuesday, February 18, 2014 12:53 PM
  • Hi Jyuma1

    For  SQl view issue, first I'm guessing whether column is not nullable in the external database, please check it on your side.

    You can also collect a stack trace , it will provide more details why this happens.

    Regards

    Angie


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 19, 2014 7:28 AM
  • Thank you Angie

    I used the ISNULL function on all the fields in the View so there is no problem with Lightswitch assigning each field as a primary key.

    My problem is that if I create a new SQL view and the underlying SQL table is already used in either side of a "Relationship" in Lightswitch.  For some reason Lightswitch is removing the "Relationship" and therefore the Navigation property (From To) in Lightswitch is also gone.  The navigation property  is used in any number of screens (as child records datagrid) so every screen that references the navigation property also breaks.  This does not happen when I update a View... it ony happens when I create a new View.  This is a major problem as it all but eliminates my ability to use new Views as the amount of work required to recreate the child records data grids is significant.

    Does anyone know of a way to create a new View when the underlying table is already part of a "relationship" in Lightswitch?

    Thanks    

    Wednesday, February 19, 2014 2:16 PM
  •    I don't have a solution for you other than to let you know that I have added multiple SQL views to my external database without breaking anything when doing a update of the data source. The only time I have seen what you are describing is when I change a foreign key relationship and do a update of data source.

    Dave 


    Dave Barker

    Wednesday, February 19, 2014 8:46 PM
  • Thanks Dave

    It has happened to me twice now and I'm reluctant to try it again but I have no choice.  I need to add another View and the underlying table definitely has pre-existing Lightswitch relationships.

    I'll know right away, as the errors are thrown immediately upon completion of the "Update Datasource".

    I wonder if it might have something to do with the fact that I also have multiple SQL triggers defined in the tables?  When I save the new views in SQL I get a message telling me that the change also effects  other tables.  Is it possible that Lightswitch is detecting the fact that a change occurred and is dropping the relationship as a result?   

    Wednesday, February 19, 2014 10:01 PM
  • Can you update the data source prior to adding a view without any error? I don't believe triggers would matter. Not sure how saving a view can have any affect on other tables in you DB? A View should stand alone with no relationship to any other tables

    Dave


    Dave Barker

    Thursday, February 20, 2014 1:48 AM
  • @Dave

    Yes, I can update the datasource without any problems prior to adding the new View. 

    This is strange as I have no problems when I change a view and then run update datasource, the problem only happens when I add a new View.

    I'll be adding a new view later today.  I'll let you know what happens.

    Ed

    Thursday, February 20, 2014 3:40 PM