Let’s pretend that we need to build LightSwitch application connected to external database with tables and views.
In case of tables with primary key it works fine. But when you need to attach view or table without primary key you get a warning
The table/view ‘MyDatasource..MyTableOrViewName’ does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
What if you need to edit that table/view? Easy. All you need to do add 3 attributes in Common\Properties\Common.lsml file manually. Open file in notepad and find tag
Here MyTableOrViewName is the name of your entity. Modify this tag as the following:
It allows you to update and delete data. As for insert you need to take into account that Lightswitch stands on auto incremental nature of primary key. Let’s call this field ID. There are 2 scenarios:
- If ID type is integer. Lightswitch will try to get new Id using scope_identity() function. But it does not work in our case. As mentioned above, we have no auto incremental ID in table/view. Even if you assign ID from trigger (which is possible in views as well) it would not work. LightSwitch is not aware of “instead of” triggers :(.
- If ID type is uniqueidentifier. This is the best candidate for our example because it is automatically converted by LightSwitch to GUID and assigned new unique key value on client before you click save button.
Remember my post about virtual database where I use only views as a logical entities. The database works very well with LightSwitch if all ID fields are uniqueidentifier.