In Visual Studio 2013 Update 2 (as well as Office Developer Tools for Visual Studio 2013 – March 2014 Update) some enhancements are available in the area of attaching to SQL Views. These enhancements were made based on the feedback we received from the community. With this update, you can now model the primary key for attached SQL Views. Additionally you are now allowed to perform create, update, and delete (e.g CUD) operations against attached SQL Views. In this post, I will walk through some scenarios that illustrate the new functionality.
Modeling the Key
I am going to build an application that attaches to the Invoices SQL View defined in the Northwind sample database. I am going to start by creating a new LightSwitch HTML Application, but you can utilize these new SQL View enhancements in either the LightSwitch HTML Application, LightSwitch Desktop Application (Silverlight) or Cloud Business App (SharePoint). Next I am going to attach to the Northwind database using the Attach to Existing Data functionality. If you are unfamiliar with how to connect to a SQL Server Database, you can learn how to on MSDN.
The first thing you may notice when attaching to a SQL views is that the key is more than likely nonsensical. This is because SQL views do not define primary keys therefore LightSwitch assumes every required field is part of the primary key.
This is only the default however and you are free to change this within the entity designer. To do so, you can toggle the Is Key property in the property sheet for each field as illustrated below.
There are a few rules you should keep in mind when defining a key.
- Only required fields can be part of the key. The LightSwitch design time will enforce this rule.
- You must define a least one property to be part of the key.
- The key must be unique. If the key is not unique, the application you build will not behave correctly.
Given these guidelines, I have modified the key for the Invoice entity to be the OrderID and ProductID as illustrated below.
There are several reasons that you should consider refining the key for Entities that are based on SQL Views. The first is that it logically makes sense for your data model. Secondly, there are several places within the design time experience that depend on what the Entity key is (e.g. the screen designer, the built in singleton queries, the entity relationship dialog, etc.) A good example is that a Details screens will surface the key properties as screen parameters. If you want to programmatically open the screen, you will need to specify each of the key values of the entity you want to open the screen for. If you leave the default key defined which contains every required field, the number of parameters may become unwieldy.
Editable Views
As I eluded to earlier, LightSwitch has support for performing CUD operations on Entities that are backed by SQL Views. There are several conditions however that determine if a particular CUD operation is supported on a SQL view such as
- Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
- The columns being modified in the view must directly reference the underlying data in the table columns.
The full list of conditions are covered by the Updateable Views section of the Create View MSDN help topic. If your view definition or CUD operation fails these conditions your end users will get an error when saving. Because of these conditions, entity based views by default are not editable within LightSwitch. To enable editable views you must open the entity designer for the view based entity and toggle the Is Editable property. This will enable create, update, and delete for the entity.
If you want fine grained control over which CUD operations are permitted within your application or you want to require a certain set of permissions to perform CUD, you will need to enable the Is Editable property and then provide implementations for the appropriate CanInsert, CanUpdate, and CanDelete methods for your entity.
Summary
When attaching to a SQL View, you now have the ability to model the primary key and are able to perform CUD operations against the view. If you have any questions or comments, please let us know through the blog comments or by posting in our forum.
– Michael Simons, Software Development Engineer, LightSwitch Team

Yes! Updatable Views! LS Team is Awesome!
Very nice!
Comment/Question: This makes me wonder how I should/can add views to the intrinsic database ApplicationData?
Thanks,
Todd
Wow this is great. Very useful.
Hi Todd,
Glad to hear you like this functionality. LightSwitch does not have any support for views within the intrinsic data source.
-Michael
Seguindo uma linha de pensamento e a cada dia dependendo menos do SSMS
Great new feature – LightSwitch is really stepping up now!
One of the most anticipated features for database first designs (like our team).
It is also very important for me that Microsoft keeps staring at all directions and only Sharepoint or intrinsic db, which is crucial for the future of our investment.
Thank you
Another top new feature for LightSwitch! Please continue making my job easier to do! 🙂
Todd,
You can use views by adding your intrinsic database as external datasource.
Server name: (localdb)v11.0
windows auth
and then you will see the database. But you need first to start and stop your Lightswitch app so the DB gets attached 😉
Very nice!
Are the Editable view restrictions noted in the blog implemented on SQL Server or by Lightswitch/EF? For instance, can we use SQL Server INSTEAD OF triggers on the view to get around restrictions against updating columns from more than one base table? Or does Lightswitch/EF somehow prevent such updates even before SQL Server gets a chance to see the DML?
Andrew,
LightSwitch does not have any limits in place. The limitations are from SQL server therefore there are some ways you can mitigate them.
I bumped into what appears to be a show-stopper when using an INSTEAD OF INSERT or UPDATE trigger on a View. The thread is at social.msdn.microsoft.com/…/save-failed-on-sql-view if any of the LS Team would help out by taking a look at the problem…
Reading from the View is OK, but attempts to INSERT fail with a "data updated by another transaction" error and no data changes at all.
The ability to potentially use Views as a means to eventually fire off Stored Procedures was what lured me back to LS after major issues trying to use Views and SPs a couple of years back. I really hope there's a solution as LS is my last hope of being able to develop back-office admin tools for reference data without having to, unhappily, learn VS/ASP.Net/C# etc. from scratch.
Hi SAinCA,
I replied to your forum thread. Hopefully we can work through your particular issue. There are cases in which INSTEAD OF INSERT or UPDATE trigger are supported. We also acknowledge however that there are a lot of scenarios in which they are not going to be supported as well. CUD support is constrained. It is constrained by SQL in conjunction with EF and the CUD statements it issues.
I don't understand, I mean why create a view if you can only reference one table? The built in table functionality works fine, the only reason we create views is to join multiple tables. I don't understand the benefit of this.
Hi Joseph Stevens,
You can READ from any type of view no matter how many tables the columns come from. If you want to Create, Update, or Delete rows in a views, you are constrained by what SQL supports – msdn.microsoft.com/…/ms187956.aspx. This is where the one table constraint comes in – Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
-Michael
Hi Michael Simons,
Thanks, that is very helpful to know. Is this feature not available in the desktop version? Because I am looking at my view and I don't see any option for Is Key.
-Joseph
Hi Joseph Stevens,
This feature is available for both desktop and html client flavored LightSwitch projects. Please ensure you have the appropriate version of VS installed – Visual Studio 2013 Update 2 (as well as Office Developer Tools for Visual Studio 2013 – March 2014 Update).
-Michael
Hey Michael
I have Visual Studio 2013, Version 12.0.30501.00 Update 2 and Office Developer Tools May 2014 Update ENU. I do not see the Is Key, or Is Editable checkbox. Did the may update drop support or something? I just imported a new view, but I don't see either option.
Hey Michael,
Quick update on our problem, our project was upgraded from Visual Studio 2012, to Visual Studio 2013. If we create a new project in 2013, we see those "is key" and "is editable". Also lightswitch understands when we import a view which property is a primary key, and it functions as expected. How do go about… i am not sure what the right word would be… re-updating the migration process? Or perhaps, creating a new project and importing the old one into it? What would be the best way of going about that.
Thanks –
Joseph
Help me Michael-Wan Kenobi, your my only hope.
Hey Michael,
Issue was, you have to upgrade the project by right clicking on the solution and pressing upgrade. Even though visual studio does the upgrade process, we never actually were on update 2. Small little detail there, good to know.
Thanks for your help in getting me to the solution!
Hi
The updates are useful, but I did find a breaking change in using views on SQL Azure, when publishing to Azure and using the rest services. Notably that they don't work
I posted a comment to Connect here:
connect.microsoft.com/…/march-2014-lightswitch-update-breaks-sql-views-accessed-via-rest-on-azure
Description
When accessing Lightswitch data via OData views, SQL tables all work fine, views work fine on local but break when deployed to Azure. As an example, meerkat-rptmgr-test.azurewebsites.net/…/vwEachReports breaks, meerkat-rptmgr-test.azurewebsites.net/…/CustomReport_Calendars works. This behavior is new with the March 2014 update.
I'm using Visual Studio 2013 Update 3 and do not see "Is Key" as an option. How do I enable it?
"Is Key" is not an option, how do I enable it?
I'm running Visual Studio 2013 Update 3.
Hi. The editable view is what I am concerned with, but I cannot see the 'iseditable' property when I open the view from the entity list. I can see the 'Is Searchable', but not the editable. Do anyone know why that is happening? Thank you.
Hi Noel,
Are you having troubles with a new or existing project? If you are using a existing project, was it created with a previous version of LightSwitch? If so did you upgrade your project and then refresh you data source?
-Michael
Hi,
This was a very useful update. Thank you. We are using views for a variety of things, one of them being server computed values.
Let's say you have a Jogging Route table. This table just has a name of your Jogging route.
It then has a child table Jogs with a foreign key back to the Jogging Route and start and end times, and distance.
We create a view that then calculates the total distance and total time ran on that jogging route, which as you can see is calculated by summing the values of all the children. The sql stuff works great. Run the view before and after, and it returns the correct results.
But, initiate an async get of the view on the parent object, and the original value is always returned.
e.g.
screen.JoggingRoute.getVw_RouteTotals().then(function (result) {
//why is this never updating? its' always returning the original value
console.log(result);
})
How do we do/force an async get of the view, ensuring it recalculates?
Thanks for any advice. Please don't point me to ashx files 🙂
Much admiration,
TinkTM
HI guys,
I figured it out.
I had to refresh() the view, instead of performing a get().
screen.JoggingRoute.vw_RouteTotals.details.refresh();
Hope this helps someone.
TinkTM
Hey Michael,
I am using an existing project built using VS Pro 2012 and LS 2012. I have not upgraded, but refreshed my data source back then but still cannot see the 'is editable' property. Any other insight would be appreciated. Thank you.
In case anyone else is having an issue getting the "Is key" property to display on an imported view…..
The Lightswitch project I was working with was created with VS 2013 Update 3. I installed the latest version of Office Developers Tools (Office Developer Tools for Visual Studio 2013 – November 2014 Update) but still no love. Saw Joseph Stevens post about right-clicking on the project and choosing "Upgrade project". Ran through that but still no "Is Key" property on the view. As a last ditch attempt, I updated my datasource removing the view, then updated again, adding the view. The Is Key property showed up after that.
Sdracdliw,
Did you also see the 'is editable' property after that?
Noel.
I just created my first lightswitch html client project in VS 2013. I added a datasource and selected a view to include. Then I open the view. Strangely the checkboxes for "Required are disabled. The Properties window is disabled also. What do I need to do to edit this? Thanks
Hello
in some cases it is MANDATORY to use this feature, else the Auto generated keys will cause not all rows to be displayed. I had a Case where the Display of the view Content in lightswitch did NOT match the underlying query result in SQL when querying a view. This is quite bad. Without any warnig LS simply defines the keys which can cause not to see all data – really a big pitfall.
What if the view has a unique clustered index? Will that be used?
Hansjörg