Skip to content

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 😉

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Stéphane Savorgnano

Stéphane Savorgnano

Stéphane Savorgnano has more than 20 years of experience in Microsoft software development and SQL Server database solutions, specializing in SQL Server installation, performance analysis, and best practices. He holds Microsoft Certified Solutions Associate (MCSA) and Expert (MCSE) for SQL Server 2012 through 2017, MCTS, MCITP for SQL Server 2008, and ITIL V3 Foundation certifications. Prior to dbi services, he was a software engineer at Ciba Specialty Chemicals in Basel. He holds a Master's degree in Informatics from the University of Mulhouse. His industry experience covers banking and financial services, chemicals, and pharmaceuticals.View Author posts

75 posts since November 2012

Most read in Application Server

The Application Server posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *