locked
Save Failed on SQL View RRS feed

  • Question

  • I started, with great enthusiasm, to use the 2013 Update2 release to (finally) use SQL Views as sources and targets for my lists and CRUDS. as described in the recent blog post: Attaching to SQL Views.

    Target instance is SQL Server 2012 Enterprise, SP1 CU10

    Existing View came up beautifully, correctly using the ID column as the key.  Made no changes other than to Sort by the "Location" column in the query.

    Wrote and tested an INSTEAD OF trigger on the view.

    Returned to LS and added the addEdit screen and Command button - opens a simple 3-field Dialog Box. (See background of attached image, below)

    Saved and Ctrl+F5

    Clicked the new "Add" button and filled in all three fields.

    Save then disappointed me with:

    What does one need to do now, please?

    Friday, June 6, 2014 11:30 PM

Answers

  • Hi Again,

    Sometimes I cannot give up... I think I've found a solution to the problem. 

    It seems EF will return the inserted row if the trigger selects the row.  This works for me INSTEAD OF INSERT:  (I have not yet tried update trigger)

    CREATE TRIGGER [dbo].[AccountsViewTrigger]
    ON [dbo].[AccountsView]
    INSTEAD OF INSERT
    AS
    BEGIN
    	INSERT INTO dbo.Accounts(CompanyName) 
    	SELECT CompanyName FROM inserted
    	select [Id], [RowVersion]
    	from [dbo].[AccountsView]
    	where @@ROWCOUNT > 0 and [Id] = @@IDENTITY
    END
    GO

    Apparently, if the trigger returns a row, then EF doesn't try to select it again using scope_identity().  That's my theory anyway, as I cannot profile Azure SQL.

    PS...It tried using OUTPUT [INTO] but couldn't get it to work in the trigger.

    More info:

    http://social.msdn.microsoft.com/Forums/en-US/c504d017-fce4-4875-a495-bba084d5246c/instead-of-insert-trigger-for-table-with-identity-column-bug?forum=adodotnetentityframework

    Plus there are a bunch of connect bugs in SQL Server and EF regarding scope_indentity()

    HTH,

    Josh






    • Proposed as answer by joshbooker Wednesday, June 11, 2014 2:10 PM
    • Marked as answer by SAinCA Wednesday, June 11, 2014 5:00 PM
    • Edited by joshbooker Wednesday, June 11, 2014 5:07 PM
    Wednesday, June 11, 2014 2:10 PM

All replies

  • Can you update the view irrespective of Lightswitch?  Does the trigger succeed irrespective of Lightswitch?

    If yes and yes, then let's narrow down what LS doesn't like.  Are you updating fields from only one table in the view?  Does save operation succeed if you remove the trigger?  Also, if it's an external db,  it's probably a good idea to try 'Update Datasource'

    HTH,

    Josh

    Saturday, June 7, 2014 1:06 PM
  • Yes and Yes.

    Only one table participates in the view.

    Saves succeed when editing the underlying table.

    There's no point in removing the trigger.  I inherited an attribute-value table that, for the purpose of recording outage remarks, comprises a key that is constructed from the business-key in the view, with a prepend value that is the "usage" demarcation.

    The only data source is an attached database, i.e., instead of the first action in the new project being "Add a table", I referenced an existing database.  As I am the only person developing anything on that server, an "Update Datasource" seems unnecessary, but having done it, I get the same message as shown above.


    • Edited by SAinCA Monday, June 9, 2014 5:10 PM
    Monday, June 9, 2014 4:58 PM
  • There would be a point in removing the trigger if, for example, the problem went away after doing so.;-)

    I wonder if LS doesn't like the instead of trigger since the LS DML operation is blocked and another operation changes the data in it's stead.  To LS, this may appear to be a concurrency issue as the error message implies.  I've successfully used AFTER UPDATE triggers with LS, but not tried INSTEAD OF.  I wonder if replacing yours with AFTER UPDATE would eliminate the error(?)

    Update datasource reads the external db schema and updates the LS model.  It's good practice after any schema changes are made in an external db.

    HTH,

    Josh

    Monday, June 9, 2014 5:27 PM
  • Thanks for contributing Josh.

    I think my scenario explanation is wanting, so, in an attempt to clarify, here goes:

    Underlying, inherited, table comprises four columns:

    1. MachineName, which may be "Global" or an actual DB Server name, e.g., "Dev1".
    2. Attribute, which is an nvarchar(50) "Key" column.  In many cases it contains a simple literal, e'g', "recapDecimals", but in the case of the view I'm using, it contains a literal, followed by an identifier of a remote data collector, e.g., "Gateway: PC456"
    3. Value, which is free-format, nvarchar(255), and in my case contains a default of "Needs Remarks", or it has a date, followed by the cause of the aoutage, "2014-06-09: DSL outage"
    4. ID, which is an IDENTITY(1,1) column, correctly interpreted by LS to be the Entity ID it can use.

    The view dissects the Attribute and Value columns into:

    1. Location, from the Attribute column, removing the prepend "Gateway: "
    2. TheDate, date from the Value column when it doesn't contain the default string.
    3. Remarks, either the default string, or, from the example above, "DSL outage"
    4. ID, as in the table.

    There are currently two INSTEAD OF triggers, one for INSERT and the other for UPDATE.  Each takes the incoming view columns and either inserts a row, having established that the Location doesn't exist, or updates an existing record, each constructing the Attribute and Value fields from what are supplied, effectively reversing what the code in the View definition does.

    A T-SQL script to INSERT INTO the view and then perform an UPDATE on a different record both work without issue from SQL Management Studio.

    I hope this helps you understand why removing the INSTEAD OF triggers is pointless and there is no AFTER capability because the View's columns, other than ID, do not resolve to any of the underlying table's columns.

    If you look at the blog post cited in the starter for this thread, you'll see that near the end of the responses, Andrew asks about using INSTEAD OF triggers, to which the author appears to say that LS is unaware of them and that they should be perfectly usable, which is why I jumped onto LS (again) to solve this simple data-recording issue.

    Monday, June 9, 2014 5:54 PM
  • Thanks for clarifying.  I did a test with a simple table, simple view, and simple trigger.

    CREATE TABLE [dbo].[Accounts] (
        [Id]          INT            IDENTITY (1, 1) NOT NULL,
        [RowVersion]  ROWVERSION     NOT NULL,
        [CompanyName] NVARCHAR (255) NOT NULL,
    );
    GO
    
    CREATE VIEW [dbo].[AccountsView]
    	AS SELECT * FROM [Accounts];
    GO
    
    CREATE TRIGGER [AccountsViewTrigger]
    ON [dbo].[AccountsView]
    INSTEAD OF INSERT
    AS
    BEGIN
    	SET NOCOUNT ON
    	 INSERT INTO dbo.Accounts(CompanyName)
    	  SELECT CompanyName FROM inserted
    END;
    GO

    Without the trigger, Add\Update works fine in LS.  With the trigger, I get the same concurrency error, even though the trigger works fine in SSMS.

    So I'd say triggers are less than 'perfectly usable', however this is not the first case in LS where concurrency exceptions need to be handled.

    Have a look at this code to see if you can catch and handle the error:

    how to handle database concurrency issues

    PS...I like the use of 'wanting'  I find it's use, but as a verb, wanting. ;-)

    HTH,

    Josh

     

    • Edited by joshbooker Monday, June 9, 2014 7:17 PM SP - PS
    Monday, June 9, 2014 6:31 PM
  • Happy to see it's not just me...

    Problem with the conflict resolution link is that it applies to the 2011 version of LS, so the Write Code methods don't match up as I'm using 2013 Update 2.

    There are several "Server" methods - any idea which one I should use, or ones, for that matter?

    Monday, June 9, 2014 7:19 PM
  • He is using the '_Saving' method of the Screen.  If you're using SL client, I believe that method should be in the Screen Code.  But you're right the concurrency handling may have changed when they went to OData between client-server.  Here's an article on the changes, but not on how to handle.

    http://blogs.msdn.com/b/lightswitch/archive/2012/07/10/concurrency-enhancements-in-visual-studio-lightswitch-2012-eric-erhardt.aspx

    For HTML Client it's different.  I cannot find an example. 

    • Edited by joshbooker Monday, June 9, 2014 7:41 PM link
    Monday, June 9, 2014 7:32 PM
  • I'm using the HTML Client as we don't want any Silverlight artifacts.

    Thanks for looking.

    Monday, June 9, 2014 7:40 PM
  • Okay I cannot find any examples specific to HTML Client, but I would say the server is obviously the place to do this.  I would try code very much like that in the 2011 article but do it in the _Updating & _Inserting methods of the entity code. 

    Here is more recent thread in which Justin simply says:

    "It's a ConcurrencyException (from the Microsoft.LightSwitch namespace). Catch it and enumerate the EntitiesWithConflicts property."

    This is what the 2011 code is doing.  Perhaps it would work in the _Updating & _Inserting methods as well.

    HTH,

    Josh

    Monday, June 9, 2014 7:53 PM
  • Sadly, Microsoft changed a lot and many of the objects in the 2011 code do not resolve to anything in 2013U2.  Even "handled = true;" doesn't know what "handled" is...

    Thanks for your responses.

    Monday, June 9, 2014 8:34 PM
  • Okay,  The place to do concurrency handling is in the SaveChanges_ExecuteFailed method of your data service code.

    The following code handles concurrency for me in VB:

    Imports Microsoft.LightSwitch
    Imports Microsoft.LightSwitch.Details
    
    Namespace LightSwitchApplication
    
        Public Class LS365_dbService
    
            Private Sub SaveChanges_ExecuteFailed(exception As Exception)
                Dim e As ConcurrencyException = exception
                For Each entityConflict As IEntityObject In e.EntitiesWithConflicts
                    entityConflict.Details.EntityConflict.ResolveConflicts(
                    ConflictResolution.ServerWins)
                    Me.SaveChanges()
                Next
            End Sub
    
        End Class
    
    End Namespace

    However, it doesn't fix this problem because the exception is reporting:

    EntityConflict.IsDeletedOnServer = True

    I presume this is because the trigger has changed the row and the ETAG doesn't, match that which was sent with update from the client.

    Hopefully someone from the team will pick this up and tell us if it's even possible to handle this situation.

    If it were me, I think I'd eliminate the view and use my own normalized table in LS.  Then use the triggers to sync changes with your inherited table, if required.

    Have a nice day.

    Josh



    • Edited by joshbooker Tuesday, June 10, 2014 10:35 AM
    Tuesday, June 10, 2014 1:09 AM
  • Thanks, Josh, you have certainly gone the extra mile on this one.

    It does appear that we need some Microsoft expertise on this one.

    I've posted on the original LS Blog Article comments section, so I hope that Michael Simons, the post's author, looks up this problem.

    Regards.

    Tuesday, June 10, 2014 5:28 PM
  • I took a look at Josh's repro.  Upon insert, EF issues the following SQL statement

    insert [dbo].[AccountsView]([CompanyName])
    values ('AcmeCompany')
    select [Id], [RowVersion]
    from [dbo].[AccountsView]
    where @@ROWCOUNT > 0 and [Id] = scope_identity()

    The select statement yields no results which is what triggers the concurrency exception.  This stems from the Insert Trigger which specifies 'SET NOCOUNT ON'.  Removing this fixed this particular scenario.

    I indicated in my blog post, CUD support is constrained.  It is constrained by SQL in conjunction with EF and the CUD statements it issues.

    Tuesday, June 10, 2014 8:21 PM
  • I took out the SET NOCOUNT ON; statements from INSERT and UPDATE triggers but the result was the same.

    I didn't apply Josh's code because I don't know what the equivalent C# code should be.

    Any further ideas, Michael?

    Tuesday, June 10, 2014 9:17 PM
  • It is difficult to say without seeing the triggers and having a repro in hand.  Have you profiled the SQL that is getting issued by your application?  If so what is the select statement returning that is part of the insert?
    Tuesday, June 10, 2014 9:24 PM
  • Profiler:

    exec sp_executesql N'insert [dbo].[xCPGatewayIssue]([Location], [TheDate], [Remarks])
    values (@0, @1, @2)
    select [ID]
    from [dbo].[xCPGatewayIssue]
    where @@ROWCOUNT > 0 and [ID] = scope_identity()',N'@0 nvarchar(255),@1 datetime2(7),@2 nvarchar(255)',@0=N'SLA',@1='2014-06-10 00:00:00',@2=N'test'

    INSTEAD OF INSERT trigger:

    USE xchange
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER dbo.IO_xgi_Insert 
       ON  dbo.xCPGatewayIssue 
       INSTEAD OF INSERT
    AS 
    /* =============================================================================
    ** Create date  : 2014-06-06
    ** Description  : Enables Addition of an XCP Gateway Issue remark for a new location
    **
    ** Notes  . . . : Only create with a date when the default remark is not used.
    **
    ** Modification Log:
    ** ================
    ** |   Date     | Who |  Tkt | Modification
    ** |------------+-----+------+--------------------------------------------------
    ** | 2014-06-06 | SLA |      | Initial version.
    ** |----------------------------------------------------------------------------
    ** ========================================================================== */
    BEGIN
    	--SET NOCOUNT ON;
       /* -----------------------------------------------------------------------------
       ** All or nothing - no prior existing Location
       ** -------------------------------------------------------------------------- */
       IF NOT EXISTS ( SELECT 1
                         FROM dbo.t_Attribute_Value av
                              INNER  JOIN Inserted i
                                       ON STUFF(av.Attribute,1,CHARINDEX(':',Attribute),'') = i.Location
                        WHERE av.Attribute LIKE 'xCPGatewayIssue:%'
                     )
          INSERT INTO dbo.t_Attribute_Value
               ( MachineName
               , Attribute
               , Value
               )
          SELECT N'GLOBAL'
               , CONCAT( N'xCPGatewayIssue:'
                       , i.Location
                       )
               , CASE
                    WHEN i.Remarks = N'Needs Remarks' THEN i.Remarks
                    ELSE CONCAT( FORMAT( GETDATE(), 'yyyy-MM-dd')
                               , N': '
                               , i.Remarks
                               )
                 END
            FROM Inserted i
           WHERE i.Location IS NOT NULL
    END
    GO
    

    Are these what you meant?  If not, I can supply what's lacking.

    Thanks again.

    Tuesday, June 10, 2014 9:38 PM
  • For what it's worth.  I also still get the same error after removing the dreaded NOCOUNT.  I also get this error with an INSTEAD OF trigger on a table - so it's not specific to a view.  Which makes sense now knowing the NOCOUNT issue.

    CREATE TABLE [dbo].[Accounts] (
        [Id]          INT            IDENTITY (1, 1) NOT NULL,
        [CompanyName] NVARCHAR (255) NOT NULL,
        [Address]     NVARCHAR (255) NULL,
        [City]        NVARCHAR (255) NULL,
        [State]       NVARCHAR (255) NULL,
        [Zip]         NVARCHAR (255) NULL,
        [Phone]       NVARCHAR (255) NULL,
        [Fax]         NVARCHAR (255) NULL,
        [Attachment]  NVARCHAR (255) NULL,
        [RowVersion]  ROWVERSION     NOT NULL,
    	PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    GO
    
    CREATE TRIGGER [Trigger]
    ON [dbo].[Accounts]
    INSTEAD OF INSERT
    AS
    BEGIN
    	INSERT INTO dbo.Accounts(CompanyName) SELECT CompanyName FROM inserted
    END
    GO
    
    CREATE VIEW [dbo].[AccountsView]
    	AS SELECT * FROM [Accounts]
    GO
    
    CREATE TRIGGER [AccountsViewTrigger]
    ON [dbo].[AccountsView]
    INSTEAD OF INSERT
    AS
    BEGIN
    	 INSERT INTO dbo.Accounts(CompanyName) SELECT CompanyName FROM inserted
    END

    Insert on both Accounts table and AccountView view both result in

    EntityConflict.IsDeletedOnServer = True

    PS...I have no clue how to profile an Azure db.

    Josh




    • Edited by joshbooker Tuesday, June 10, 2014 10:41 PM PK
    Tuesday, June 10, 2014 10:07 PM
  • Josh,

    One thing to mention was that I defined a PK on the Accounts table.  This may attribute the difference in behavior you are seeing.  EF/LS must infer a key when one is not specified.

    Tuesday, June 10, 2014 10:13 PM
  • Thanks, Josh.

    I checked the underlying table and it has a PK on the ID column, which is also an IDENTITY(1,1) column, NOT NULL.

    Tuesday, June 10, 2014 10:18 PM
  • Hi SAinCA,

    What does the select statement return that is part of the insert statement?

    Tuesday, June 10, 2014 10:21 PM
  • No data.

    There are 3 Messages (using SSMS Editor):


    (1 row(s) affected)

    (1 row(s) affected)

    (0 row(s) affected)

    Tuesday, June 10, 2014 10:27 PM
  • Thanks Michael.  My table also has PK defined.  Sorry, I inadvertently left that out of the T-SQL in my prior post.
    Tuesday, June 10, 2014 10:46 PM
  • For sanity I checked that the trigger had a good value for SCOPE_IDENTITY() and it does, so it seems that the INSTEAD OF trigger somehow blocks the value from being accessed by the SELECT that follows the INSERT, which is very bad news...
    Tuesday, June 10, 2014 11:27 PM
  • Yeah that's the ticket...bad news indeed...

    "since the INSERT occurs within the scope of the trigger instead of the scope of the insert statement that caused the trigger to execute, SCOPE_IDENTITY() does not return the value of the IDENTITY column"

    http://blog.falafel.com/blogs/adam-anderson/2008/11/10/t-sql_how_to_obtain_scope_identity_from_an_object_with_an_instead_of_insert_trigger

    This returns nothing:

    insert [dbo].[AccountsView]([CompanyName])
     values ('AcmeCompany')
     select [Id], [RowVersion], companyname
     from [dbo].[AccountsView]
     where @@ROWCOUNT > 0 and [Id] = scope_identity()

    While this returns the inserted row:
    insert [dbo].[AccountsView]([CompanyName])
     values ('AcmeCompany')
     select [Id], [RowVersion], companyname
     from [dbo].[AccountsView]
     where @@ROWCOUNT > 0 and [Id] = @@identity

    I don't suppose there's anyway to get EF to use @@identity(?)

    Sorry,

    Josh


    • Edited by joshbooker Tuesday, June 10, 2014 11:56 PM
    Tuesday, June 10, 2014 11:55 PM
  • Hi Again,

    Sometimes I cannot give up... I think I've found a solution to the problem. 

    It seems EF will return the inserted row if the trigger selects the row.  This works for me INSTEAD OF INSERT:  (I have not yet tried update trigger)

    CREATE TRIGGER [dbo].[AccountsViewTrigger]
    ON [dbo].[AccountsView]
    INSTEAD OF INSERT
    AS
    BEGIN
    	INSERT INTO dbo.Accounts(CompanyName) 
    	SELECT CompanyName FROM inserted
    	select [Id], [RowVersion]
    	from [dbo].[AccountsView]
    	where @@ROWCOUNT > 0 and [Id] = @@IDENTITY
    END
    GO

    Apparently, if the trigger returns a row, then EF doesn't try to select it again using scope_identity().  That's my theory anyway, as I cannot profile Azure SQL.

    PS...It tried using OUTPUT [INTO] but couldn't get it to work in the trigger.

    More info:

    http://social.msdn.microsoft.com/Forums/en-US/c504d017-fce4-4875-a495-bba084d5246c/instead-of-insert-trigger-for-table-with-identity-column-bug?forum=adodotnetentityframework

    Plus there are a bunch of connect bugs in SQL Server and EF regarding scope_indentity()

    HTH,

    Josh






    • Proposed as answer by joshbooker Wednesday, June 11, 2014 2:10 PM
    • Marked as answer by SAinCA Wednesday, June 11, 2014 5:00 PM
    • Edited by joshbooker Wednesday, June 11, 2014 5:07 PM
    Wednesday, June 11, 2014 2:10 PM
  • I used your example and replaced @@IDENTITY with the customary SCOPE_IDENTITY() and everything worked!

    I'll play around with multi-row INSERT, although it's highly likely that every insert into this View will be a singleton.  I may need to do some script-based bulk inserts one day, so it's a just-in-case precaution.

    I'll also update my UPDATE trigger to return the updated row and see if that cures all ills.

    Can't thank you enough for your perseverance - I can build Apps!!!  You have no idea how much of a relief this is...

    Will update this thread with good/bad as occurring.

    Cheers (I owe you a beer).

    Stephen.

    Wednesday, June 11, 2014 5:00 PM
  • This works fine in the INSERT trigger:
    BEGIN
       SET NOCOUNT ON;
       /* -----------------------------------------------------------------------------
       ** All or nothing - no prior existing Location
       ** -------------------------------------------------------------------------- */
       IF NOT EXISTS ( SELECT 1
                         FROM dbo.t_Attribute_Value av
                              INNER  JOIN Inserted i
                                       ON STUFF(av.Attribute,1,CHARINDEX(':',Attribute),'') = i.Location
                        WHERE av.Attribute LIKE 'xCPGatewayIssue:%'
                     )
          BEGIN
             DECLARE @ID TABLE
                   ( MachineName  nvarchar(128)  NOT NULL
                   , Attribute    nvarchar(50)   NOT NULL
                   , Value        nvarchar(255)  NOT NULL
                   , ID           int            NOT NULL
                   )
    
             INSERT INTO dbo.t_Attribute_Value
                  ( MachineName
                  , Attribute
                  , Value
                  )
             OUTPUT Inserted.MachineName
                  , Inserted.Attribute
                  , Inserted.Value
                  , Inserted.ID
               INTO @ID
             SELECT N'GLOBAL'
                  , CONCAT( N'xCPGatewayIssue:'
                          , i.Location
                          )
                  , CASE
                       WHEN i.Remarks = N'Needs Remarks' THEN i.Remarks
                       ELSE CONCAT( FORMAT( GETDATE(), 'yyyy-MM-dd')
                                  , N': '
                                  , i.Remarks
                                  )
                    END
               FROM Inserted i
              WHERE i.Location IS NOT NULL
       
             SELECT xgi.Location
                  , xgi.TheDate
                  , xgi.Remarks
                  , xgi.ID
               FROM dbo.xCPGatewayIssue xgi
                    INNER  JOIN @ID i
                             ON i.ID = xgi.ID
              WHERE @@ROWCOUNT > 0
          END
    END
    

    Wednesday, June 11, 2014 5:15 PM
  • Stephen,

    Very nice!  I'm glad it works!  I tried OUTPUT INTO using a table var, but didn't try the JOIN.  Again, nice job!

    My perseverance is not without self-interest.  I too have some databases with views and triggers and I too tried to work with them in LS a few years back. 

    Thanks to the LS team for enabling updatable views and thanks to your scenario for necessitating a trigger solution - now we can open many more LOB databases to the RAD & mobile UI enabled by LS.

    Keep us posted.

    Cheers,

    Josh

    Wednesday, June 11, 2014 5:29 PM
  • Turns out that I didn't need to touch the INSTEAD OF UPDATE trigger.

    However, the HTML Client behavior was odd:

    • Home screen is a tile list of Issues
    • Click an Issue and the viewSelected screen appears (which I'll change to the Edit, later)
    • Click the Edit button and it brings up the addEdit screen, which I made a few cosmetic changes to, i.e., change the Title in context of an Add or an Edit, and added a Delete button to the Command Bar.
    • When I change the date and/or the Remarks (Date Picker and TextBox, respectively), then "Save", the entity is updated and the viewSelected screen is shown.
    • If I then click Edit and on the Edit Screen change the Remarks, I sometimes get the original, "data has been changed by another transaction" message.   This appears to happen if I change the date field and leave the Remarks as-is.
    • Profiler captures an sp_executesql for the date update, but the next statement it captures is purely a SELECT, so it's losing track somewhere.  The date update was successful, BTW.
    • As the message says, if I return to the Home screen and edit the same Issue, all is well until I change the date.
    • I did see one oddity - the View defines TheDate as a date column but the sp_executesql for the UPDATE shows a time-value every time of 17:00:00.

    Very puzzling!  Should I be forcing a refresh after the edit, just to be sure?

    I also tested the DELETE and that totally surprised me:

    • There is no INSTEAD OF DELETE trigger, but the record is gone!

    Hardly consistent behavior.  As it's a single table and the DELETE features the ID, I guess SQL Server is OK with that, but I'd be very wary of multi-table Views and, once I get that far, needing to run a Stored Procedure to effect a Delete...

    Thursday, June 12, 2014 1:48 AM
  • Makes sense that the SCOPE_IDENTITY() issue doesn't affect updates. 

    >Profiler captures an sp_executesql for the date update, but the next statement it captures is purely a SELECT, so it's losing track somewhere.

    I think the select is normal EF behavior after every insert/update - it selects the new values to pass back to the client...could be wrong but that's my understanding anyway.

    Try adding a RowVersion Field to the table as suggested here:

    http://blogs.msdn.com/b/lightswitch/archive/2012/07/10/concurrency-enhancements-in-visual-studio-lightswitch-2012-eric-erhardt.aspx

    PS... If all else fails, could you not create your own normalized table for use in LS and have AFTER triggers sync changes to the legacy table?

    HTH,

    Josh




    • Edited by joshbooker Thursday, June 12, 2014 2:52 AM
    Thursday, June 12, 2014 2:44 AM