Permit user-defined "key" for Views instead of the awful/useless inferred "key"
When including a SQL View in the Data Source, LS "infers" the "key" - horribly, uselessly, and incapable of being editing to the correct key. Besides this being, IMO, a bug, it renders the inclusion of a View for practical purposes, useless. I don't want the View Schema Bound just so I can define an Index that "looks" like a PK but cannot be designated as such.
PLEASE CHANGE LIGHTSWITCH SO THAT:
When the SQL objects are initially added to the data source and a View is featured, or for that matter a table that lacks a PK, it presents as many additional dialogs as are needed to enable the _designer_ to check the checkboxes of the PK columns and sequence them as the actual PK.
In my case, I have an underlying "Usage" table containing generalized fields for equipment characterisitcs, e.g., operating pressures, temperatures, HP, etc., which vary according to whether the equipent is a chiller or a compressor or simply a glycol pump. The catalog tables are "real" SQL tables, e.g., Compressor, Chiller. The View takes the catalog's characteristics, 95% of which are required fields, and "instantiates" for want of a better descriptive, the Usage fields of interest from the Usage Table as real-world named columns so we can use an INSTEAD OF trigger under the view to populate the source fields in the Usage Table whenever, for example, a Compressor of a certain Make and Model is used at a particular site.
The "key" chosen by LightSwitch for the CompressorUsage View comprises ALL the required fields from the Compressor Catalog table, plus ALL the required fields from the generalized Usage table - SEVENTEEN COLUMNS!
This is clearly UNUSABLE and a design flaw in the product that sorely needs to be addressed, not just for Views but for non-PK tables, too.
Stephen Anslow
shared this idea
7 comments
-
Mindy Burkett
commented
I do use EF5, code first so it could be a version thing or specifically a code first thing. If you update the datasource then it does convert back to the default keys. You would have to fix it every time you update the associated datasource from your database.
-
Yann
commented
@Mindy - maybe defining your own keys manually is new in EF5, I'm almost certain you can't do it in EF4. Interesting technique you suggest, modifying the lsml file. Are you sure that updating the data source (assuming an external database) doesn't overwrite your changes? It was always a problem in EF4, when you made manual modifications, they always got overwritten when the model was updated.
-
Mindy Burkett
commented
I was able to define my own keys on my view by manually modifying the XML file, Common.lsml. Close VS and make a backup of your project first because you are not supposed to manually modify this file. Then change any undesired 'KeyProperty' into 'EntityProperty' throughout the file.
-
Mindy Burkett
commented
LightSwitch does use Entity Framework but EF allows you to manually define your own keys. I use EF with views in other applications and I have no problem defining whatever keys I want. It seems like it is actually a limitation of the LightSwitch entity designer and not EF itself.
-
Simon Jones
commented
When designing Views for use in LightSwitch use the ISNULL() function to ensure a column is included in the inferred key and the CAST() function to ensure a column is excluded from the inferred key. Remember to alias all columns on which you use these functions using the AS clause.
If you can't get a combination of columns that give you a unique key the records with duplicate keys will be invisible in your LightSwitch application. If that happens, use the ROW_NUMBER() function to create a new column that can act as the primary key. (Use ISNULL() on this column to ensure it is in the inferred key and CAST() on all the other columns to exclude them.) -
Yann
commented
While I agree with what you're saying, this is a Entity Framework restriction, not a LightSwitch one.
-
@_Nige commented
This really is a big issue. If I have a view that I want to display, I want to be able to choose the key I use to retrieve data from the view. The related table I am using to access the view does not contain all the items that Lightswitch has (apparently randomly) chosen to set as the key. Therefore I can't use the view!!! Since this is a key feature of the system, it is turning into a deal breaker for management.
