Infrastructure at your Service

Stéphane Savorgnano

SQL Server View problem in LightSwitch 2012

I recently faced a problem in Visual Studio LightSwitch 2012 with SQL Server views. I attached an external datasource to my project and when I tried to import my view (which is very simple) I experienced some issues.

Here is my View in SQL Server 2012:

During the import process a warning says  my view doesn’t have any primary key (apparently it’s mandatory in LightSwitch) and that a key has been inferred…

When I check my view in my LightSwitch data source…

…one of my field(rating_id_session) has been changed to a key which means that I will not be able anymore to view all lines of my views in a grid but just one record per session id, which is just impossible for me…

To avoid this issue, we have to find a workaround which needs two steps:

The first one is to avoid that our field rating_id_session becomes a key during the import. For that we will force SQL Server to mark it as “nullable” by converting it.

If you now try to re-import your view, you will have the following error:

All fields are nullable now, so there is no way to create an inferred key: the view is also simply not imported.
We now have to create a dummy primary key to be able to import our view and to see all records.
The best way to create this primary key – which could be unique for each record – is to use NewID().
I will also use the function ISNULL to add my new field to the inferred key.

With this new key, my view is now imported and I will be able to use it correctly.

To resume

When you want to take out a field from an inferred key, you can use CAST or CONVERT functions.
To add a field to an inferred key, use the ISNULL function with 0 for integer fields and N” for char fields.
And if you don’t have one or more fields to use for keys, you have the possibility to use NEWID() to create one.

Hope this will help you ;-)


Leave a Reply

8 − four =

Stéphane Savorgnano
Stéphane Savorgnano