locked
Using updatable views directly for insert RRS feed

  • Question

  • Hi

    I'm building a new data layer for a database (with some legacy) using entity framework.

    The database uses updatable views with "instead of insert" and "instead of update" triggers.

    I've seen tutorials and threads (like this one ) and it works fine except for one thing: inserts without stored procedures.

    The

    StoreGeneratedPattern="Identity"
    
    

     

    property which generates a select scope_identity() to get the ID back from the database fail (as expected).

    I can remove this parameter and insert without failure (like explained in this thread ) but of course I don't get the ID back.

    The only solution I see is to write "stored procedure wrappers" that insert and get the ID back but this seems cumbersome...

    Do you think of something else to do to insert easily in those views and get my IDs back ?

     

     

    Thursday, March 10, 2011 11:17 AM

Answers

All replies

  • Hello magicnico,

     

    Welcome to the MSDN Forum and thanks for posting here.

    According to your description, I think what you would like to do is to insert into views using stored procedure in Entity Framework and get the ID back, right?

    If so, I think the following thread might be helpful to you. It talks about Entity Framework View Model Insert in MVC2. Here it is:

    http://stackoverflow.com/questions/2550231/mvc-2-entity-framework-view-model-insert

    Please feel free to tell me if I misunderstood you.

     

    Have a nice weekend!


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, March 13, 2011 7:22 AM
  • Hi,

    Views in EF are readonly due to DefiningQuery element. There are two approaches to make a view editable:

    1. Create stored procedures for Insert/Update/Delete. This is described in this document, this walkthrough and this article.
    2. Make Entity Framework treat views as tables by doing some manual modifications in the generated edmx file. This is described in this blog post: Entity Framework: Creating a model using views instead of tables

     

    Regards,

     


    Syed Mehroz Alam
    My Blog | My Articles
    • Proposed as answer by Alan_chen Tuesday, March 15, 2011 1:32 AM
    • Edited by Syed Mehroz Alam Tuesday, March 15, 2011 10:12 AM typo
    • Unproposed as answer by ncornet Wednesday, March 16, 2011 2:44 PM
    Monday, March 14, 2011 7:54 AM
  • Well actually no, that's exactly what I try to avoid, I'll try to be more understandable.

    I already know how to insert/update in my views using stored procedure (I don't need to delete), and I know how to modify the edmx to treat views as tables.

    My views already have "insert of insert" and "instead of update" triggers so I want to use them without plugging some stored procedures.

    I can already update my views as if I used a table (i.e. without stored procedure, just with an update) because of the "instead of update" trigger and because I already know the primary key of the row I want to edit.

    My issue comes from the insert, I wanted to know if there was a way to get the ID back without using a stored procedure as if I used a table .

    When entity framework do an insert on tables it can get the ID back using scope_identity(), but in views with "instead of insert" you can't because the insert is not happening in the same scope.

     

    Hope I was more clear this time, thanks for the answer

    Wednesday, March 16, 2011 2:38 PM
  • Hi magicnico,

    I think you couldn't get ID back with trigger, you can refer here: https://connect.microsoft.com/SQLServer/feedback/details/125318/give-us-a-way-to-use-scope-identity-and-an-instead-of-insert-trigger

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by ncornet Monday, April 11, 2011 12:08 PM
    Monday, April 4, 2011 2:34 AM
  • Hi

    Thanks for the answer, I have understood that there is no "easy answer" for this question so I built an alternative solution. scope_identity and instead of insert are not compatible (yet?), I voted up on the related microsoft connect suggestion and I'll mark your response as answer.

    Cheers

    Monday, April 11, 2011 12:08 PM
  • Hi,
    I was having the same problem and I think I have an easy workaround. You can use the OUTPUT clause in your instead of trigger to return the identity value. This will generate a result when the insert is called. This result will occur BEFORE any results of select statements after the insert. So the following trigger:

    CREATE TRIGGER dbo.VWOneWerkgever_IO_INSERT ON  dbo.VWOneWerkgever
    INSTEAD OF INSERT
    AS
    BEGIN
    	INSERT INTO Werkgever (Naam, Adres, MutatieDatum)
    	OUTPUT Inserted.WerkgeverID
    	SELECT Naam, Adres, GETDATE() FROM INSERTED
    END
    

    Will return one result with one cell with the WerkgeverID (it has the name WerkgeverID).
    The method executed by the EntityFramework when inserting is:

    exec sp_executesql N'insert [dbo].[VWOneWerkgever]([Naam], [Adres], [MutatieDatum])
    
    values (@0, @1, @2)
    
    select [WerkgeverID]
    
    from [dbo].[VWOneWerkgever]
    
    where @@ROWCOUNT > 0 and [WerkgeverID] = scope_identity()',N'@0 nvarchar(50),@1 nvarchar(50),@2 datetime2(7)',
    @0=N'Insert Test',@1=N'Insert straat',@2='2011-05-24 09:28:45.2679714'


    This will generate 2 results now. The first one contains our own WerkgeverID, the second one (executed via the second SELECT) contains no WerkgeverID. The EntityFramework only checks the first result and this is exactly what it needed. You can also add any computed columns when needed.
     
    Kind regards,
     
    Schuuuring
    Tuesday, May 24, 2011 7:29 AM
  • That look promising, I'll try that and get back to you.

     

    Thanks a lot!

     

    Wednesday, May 25, 2011 9:24 AM