How to survive in LightSwitch application without primary key


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

<EntitySet
Name=”MyTableOrViewName”
EntityType=”MyTableOrViewName” />

Here MyTableOrViewName is the name of your entity. Modify this tag as the following:

<EntitySet
Name=”MyTableOrViewName”
CanDelete=”True”
     CanInsert=”True”
     CanUpdate=”True”
EntityType=”MyTableOrViewName” />

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:

  1. 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 :(.
  2. 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.

Advertisements

About Andrew Butenko

https://www.mcpvirtualbusinesscard.com/VBCServer/a9939be0-be6f-4249-a775-6665eccff2e4/card https://www.microsoftvirtualacademy.com/Profile.aspx?alias=530492
This entry was posted in LightSwitch, SQL. Bookmark the permalink.

6 Responses to How to survive in LightSwitch application without primary key

  1. Abraham says:

    There is no

    Tag

  2. Peder Rice says:

    Is there a way to do insert rows when using varchar primary keys?

    • Hi Peder,
      I think it is up to you what type of primary key you need. But LightSwitch is aware of 2 types: integer (with server side auto incremental identity) and guid (with client generated id). In case of integer it tries to evaluate new id in after saved event. It works for tables. But if you have view based entity with instead of trigger I recommend to use guid field. Lightswitch is smart enough to generate new guid without server post back. Using guid is also recommended in distributed systems with replication topology.
      Again it is up to you which field to use as primary key on server side. But you can use surrogate key referencing to another field which is essential for LightSwitch.

  3. dave White says:

    Hi Andrew
    This seems like a solution to my problem of trying to update views from Lightswtich.
    But i don’t think the common.lsml file is used in VS2013 Lightswitch desktop apps.
    Is there an alternative location in the project where I can find and modify the entityset tag or an equivalent attribute tag?

  4. Hello Dave,
    You are right common.lsml was used in 2012. But in VS 2013 Microsoft has splitted that file by entity (which is really good for team development and source control friendly). You can find all of them under AppName.Server\DataSources\DataSourceName\*.lsml
    That means you can control each entity separately. Each entity lsml file has structure which I have described in the post. You need to update EntitySet node like the following

    • dave white says:

      That’s great, many thanks Andrew. I will have a play.
      I am trying to see how far I can get before I go the way of the majority and set aside the time to master WCF RIA as an alternative.
      Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s