none
How does Entity Framework know whether the update/insert/delete affected any rows? RRS feed

  • Question

  •  

    hi

    I've mapped an Insert/Update/Delete functions to an Entity Employee. I've then modified an entity with EmployeeID=100 and called SaveChanges, and in turn EF called Update Store Procedure ( USP ) for that entity:

    a)

    CREATE PROCEDURE UpdateEmployee
    ...   
    
    AS
    
    UPDATE Employee
    SET ...
    WHERE EmployeeID=1000 /*EF throws an exception, since there aren't any rows with EmployeeID=1000 and thus no rows were affected*/
    
    SELECT ...

    At first I thought that only information that DB will return back to the caller ( caller being EF ) is the number of rows affected by the last statement in USP. Thus even if USP didn't manage to update any rows, EF would still think that update was successfull, since DB would report that one row was affected (due to SELECT statement). But it appears that information returned to the caller also specifies whether rows were affected by the SELECT statement or by the INSERT/UPDATE/DELETE statement?! Correct?


    b)

    CREATE PROCEDURE UpdateEmployee
    ...
    
    AS
    
    INSERT INTO Employee ...

    I assume that information returned back to the caller doesn't specify whether rows were affected by INSERT or by UPDATE or by DELETE statement? Is that the reason why EF considers an update successful even if USP inserted a row instead of updating a row ( with EmployeeID=100 )?

    c) But why does EF consider an update successful even when USP doesn't execute any INSERT/UPDATE/DELETE statements? As noted in my above questions, doesn't information returned back to the caller specify whether any rows were affected by INSERT/UPDATE/DELETE? If yes, then EF should throw an exception when calling the following USP, but it doesn't. Why?

    CREATE PROCEDURE UpdateEmployee
    ...
    
    AS
    
    /*no statements*/

    Thank you

    • Edited by KlemS100 Tuesday, November 29, 2011 7:07 PM
    Tuesday, November 29, 2011 6:57 PM

All replies

  • Hi KlemS100,

    I don't think we need to add select or Insert in Update Procedure. For SP mapping, the Primary key couldn't change.

    ALTER proc [dbo].[UpdateEmployee]
    (@p1 int, 
    @p2 varchar(20),
    @p3 datetime,
    @p4 varchar(20),
    @p5 varchar(20)
    )
    as
    update Employees set Name=@p2 , ModifyTime=@p3 , Phone=@p4 , Email=@p5 
    where EmployeeId=@p1
    

    @P1 is primary, we select out it from database. I'm not sure why your scenario happened?

    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.

    Thursday, December 1, 2011 7:10 AM
    Moderator
  • You've misunderstood my questions
    Monday, December 5, 2011 7:27 PM