Blog - comments

Hi Christopher, It's there. I't not an option that you check at install. Just use it by setting inme...
Hello, Thanks for the nice blog. I tried the latest 12c download available in Oracle's website and I...
Christopher Bernard
-- Here is a quick script to display which objects are locked in Share. Parameters: owner tablename....
Hey...I think you forgot that Hotspot have a JIT compiler too. The difference is in the time wherer ...
Anderson

Thanks for the content..

vani
Blog Stéphane Savorgnano SQL Server View problem in LightSwitch 2012

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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 ;-)

Rate this blog entry:
5

Stéphane Savorgnano is Consultant at dbi services. He has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. Stéphane Savorgnano is Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server implementation and maintenance. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. Stéphane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking / Financial Services, Chemicals & Pharmaceuticals, etc.

Comments

  • Guest
    Pedro Berumen Saturday, 09 November 2013

    Great tip! I needed to use a query with aggregate functions that was not imported to Lightswitch, but thanks to your post I was able to use it in the application.
    Thanks!

Leave your comment

Guest Thursday, 24 July 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter