Entity Designer mishandles View inclusion re Key and Read-only

Entity Designer mishandles View inclusion re Key and Read-only by SAinCA

Closed

as By Design Help for as By Design

Type:	Bug
ID:	699163
Opened:	11/4/2011 1:27:09 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

LightSwitch is UNUSABLE with a SQL Server 2005 Enterprise Edition View that has an INSTEAD OF trigger to handle all INSERT/UPDATE/DELETE activity.

It attempts to apply "intelligence" to defining a key for the View, whether or not the View has a Unique Index. It takes _ALL_ mandatory fields from contributing tables and makes them ALL (SEVENTEEN OF THEM IN MY CASE!) the Key of the View - preposterous!

It then states it has made the View read-only! Crass decision when there exists an INSTEAD OF trigger as mentioned above.

There is NO mechanism for "correcting" the inappropriate actions set in concrete by the Designer.

This is clearly a design flaw that renders the very promising development tool completely unusable with schema that employ this perfectly normal and admissible table-view-trigger architecture.

Details (expand)

Version 1.0

English

Define table-A with ID-A, NN-A NOT NULL column and N-A NULL column. PK is ID-A
Define table-B with ID-B, NN-B NOT NULL column. PK is ID-B.
Define table-C with ID-A, ID-B, NN-C NOT NULL column, N1-C NULL column, N2-C NULL column. PK is ID-A plus ID-B.
Define View-AC as SELECT ID-A, ID-B, NN-A, NN-B, NN-C, N2-C, WHERE ID-A = ID-B. (WITH SCHEMABINDING or without makes NO difference.)
Bring all four objects into a LS Data Source.
Add an INSTEAD OF trigger to View-AC that handles INSERT/UPDATE/DELETE of rows in table-C ONLY, for the columns in View-AC that are from table-C.

Windows Server 2008 R2

English

Key of the View comprises, in my case, SEVENTEEN NOT NULL columns - the sum of the NOT NULL columns from the 3 contributing tables,

View is marked read-only, despite there being an INSTEAD OF trigger to handle editability.

The View-AC key should be ID-A plus ID-B (the criteria from the View Definition's WHERE clause).

The View's DATA should be completely editable because of the INSTEAD OF trigger.

0 attachments

Hi,

Your issues are a result of known limitations in the current release. We are logging these issues for possible inclusion in a future release.

Best regards,
Dan Leeaphon
VS LightSwitch