locked
EF4 - OptimisticConcurrencyException - Insert table with IDENTITY column - no triggers, no misuse of settings RRS feed

  • Question

  • Hi
    I'm creating a 3-tier app, and I have a simple log table, as you can see below

    CREATE TABLE DBO.TBMOC002_LOG_EXEO (
      IDT_LOG       INT         IDENTITY,
      DAT_EXEO_PRSO    DATETIME       NOT NULL,
      NOM_PRSO_EXTD    VARCHAR(20)     NOT NULL,
      COD_RETO_APLI    TINYINT       NOT NULL,
      TXT_MENS_EXEO    VARCHAR(1024)    NOT NULL,
      CONSTRAINT PMOC002 PRIMARY KEY (IDT_LOG, DAT_EXEO_PRSO)
    )
    go

    This is my .edmx file definition

    SSDL content
        <EntityType Name="TBMOC002_LOG_EXEO">
         <Key>
          <PropertyRef Name="IDT_LOG" />
          <PropertyRef Name="DAT_EXEO_PRSO" />
         </Key>
         <Property Name="IDT_LOG" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
         <Property Name="DAT_EXEO_PRSO" Type="datetime" Nullable="false" />
         <Property Name="NOM_PRSO_EXTD" Type="varchar" Nullable="false" MaxLength="20" />
         <Property Name="COD_RETO_APLI" Type="tinyint" Nullable="false" />
         <Property Name="TXT_MENS_EXEO" Type="varchar" Nullable="false" MaxLength="1024" />
        </EntityType>
    And CSDL content
        <EntityType Name="TBMOC002_LOG_EXEO">
         <Key>
          <PropertyRef Name="IDT_LOG" />
          <PropertyRef Name="DAT_EXEO_PRSO" />
         </Key>
         <Property Name="IDT_LOG" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" ConcurrencyMode="None" />
         <Property Name="DAT_EXEO_PRSO" Type="DateTime" Nullable="false" ConcurrencyMode="None" />
         <Property Name="NOM_PRSO_EXTD" Type="String" Nullable="false" MaxLength="20" Unicode="false" FixedLength="false" />
         <Property Name="COD_RETO_APLI" Type="Byte" Nullable="false" />
         <Property Name="TXT_MENS_EXEO" Type="String" Nullable="false" MaxLength="1024" Unicode="false" FixedLength="false" />
        </EntityType>

    I have the method below that use a detached entity. I've tried both options, with AddObject and Attach + ChangeObjectState

    public void Incluir(TBMOC002_LOG_EXEO logExeo) 
    EntityConnection EConnectionMO = null; 
    try { 
    EConnectionMO =DConexao.EConexaoMO(); F5Context =new F5Entities(EConnectionMO); 
    //F5Context.TBMOC002_LOG_EXEO.AddObject(logExeo); 
     F5Context.ObjectStateManager.ChangeObjectState(logExeo, System.Data.
    EntityState.Added); F5Context.TBMOC002_LOG_EXEO.Attach(logExeo); 
    F5Context.SaveChanges();
    }
    catch (Exception Exc) 
    { 
    throw new Exception("Erro DlogExecucao.Incluir", Exc);
    F5Entities F5Context = null; 
    }

    I've receiving the error below:
    System.Data.OptimisticConcurrencyException was caught
      Message=Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
      Source=System.Data.Entity
      StackTrace:
           at System.Data.Mapping.Update.Internal.UpdateTranslator.ValidateRowsAffected(Int64 rowsAffected, UpdateCommand source)
           at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
           at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
           at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
           at System.Data.Objects.ObjectContext.SaveChanges()
           at BBA.PrazoMedioAquisicao.Data.DLogExecucao.Incluir(TBMOC002_LOG_EXEO logExeo) in D:\F5\BBA.PrazoMedioAquisicao.Data\DLogExecucao.cs:line 34
      InnerException:

    I have other 5 tables with 5 methods and all works fine. But only this table have an IDENTITY column. No triggers, no Concurrency.

    Someone could help me ?

    • Edited by Larcolais Gong Wednesday, July 27, 2011 8:52 AM unreadable
    Thursday, July 21, 2011 11:58 AM

Answers

  • Finally I solve the problem by myself.... \o/

    I simply removed the field DAT_EXEO_PRSO from PK.

    And the code works fine !

    But I'm curious about this wrong behavior of EF4

    Thanks


    • Marked as answer by Fabio Venuto Friday, August 5, 2011 2:24 PM
    • Edited by Fabio Venuto Friday, August 5, 2011 5:49 PM comment
    Friday, August 5, 2011 2:24 PM

All replies

  •  

    I think that you have the same issue :

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/b2d01f56-d7a2-4322-9936-1018e0bfa811#63b224de-b97b-4730-84af-89394bffa0ef

     

    To solve the problem :

     

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/b2d01f56-d7a2-4322-9936-1018e0bfa811

     

     

    Regards,
    Alexnaldo Santos 

     

    I'm not shure about the solution is right for me.

    All fiels of all tables have ConcurrencyMode=none. And Microsoft KB recommends install of hotfix ONLY if the situation is the same (ConcurrencyMode=Fixed).

    And even it works, how can I guarantee this fix works in our production environment ?

    Maybe a safer solution than that is to put out the IDENTITY columns, and generate the field manually.

    What do you think about this ? 


    Friday, July 22, 2011 2:02 PM
  • Hello Fabio,

    From your error information, it sounds like for some reason the insert/update/delete store procedure wasn't returning the right number of rows affected. I suggest you can use SQL Server profiler to trace to see what is happening first.

    Please feel free to let us know if you have any finding.

    Best Regards,


    Larcolais Gong[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.

    Wednesday, July 27, 2011 8:43 AM
  • Hello

    Below the result of trace. It's a little bit weird..rsrs

    BEGIN TRANSACTION

    insert [dbo].[TBMOC002_LOG_EXEO]([DAT_EXEO_PRSO], [NOM_PRSO_EXTD], [COD_RETO_APLI], [TXT_MENS_EXEO])
    values (@0, @1, @2, @3)

    select [IDT_LOG]
    from [dbo].[TBMOC002_LOG_EXEO]
    where @@ROWCOUNT > 0 and [IDT_LOG] = scope_identity() and [DAT_EXEO_PRSO] = @0

    exec sp_executesql N'insert [dbo].[TBMOC002_LOG_EXEO]([DAT_EXEO_PRSO], [NOM_PRSO_EXTD], [COD_RETO_APLI], [TXT_MENS_EXEO])
    values (@0, @1, @2, @3)
    select [IDT_LOG]
    from [dbo].[TBMOC002_LOG_EXEO]
    where @@ROWCOUNT > 0 and [IDT_LOG] = scope_identity() and [DAT_EXEO_PRSO] = @0',N'@0 datetime2(7),@1 varchar(20),@2 tinyint,@3 varchar(1024)',
    @0='2011-07-27 10:35:52.0822854',@1='CARGAAQUISICOES',@2=0,@3='EXECUTADO COM SUCESSO'

    Do you have any suggestions to me ?

    I have just ran the SQL above. I receive 1 error (first insert), and 2 messages : 1 row(s) affected and 0 row(s) affected

    And the record has been inserted correctly

    Thanks a lot

    Fabio


    • Edited by Fabio Venuto Thursday, August 4, 2011 2:11 PM more information
    Wednesday, July 27, 2011 1:50 PM
  • Hi guys !

    Anyone have an idea of what's happening ?

    I don't want to remove Identity column and generate key by myself...

     

    Thursday, August 4, 2011 4:07 PM
  • Finally I solve the problem by myself.... \o/

    I simply removed the field DAT_EXEO_PRSO from PK.

    And the code works fine !

    But I'm curious about this wrong behavior of EF4

    Thanks


    • Marked as answer by Fabio Venuto Friday, August 5, 2011 2:24 PM
    • Edited by Fabio Venuto Friday, August 5, 2011 5:49 PM comment
    Friday, August 5, 2011 2:24 PM
  • Hello,

    I resolved my problem by passing in sql server, the column datetime to datetime2 and regenerated the edmx model.

    datetime : 31/12/12 23:59:59.997

    datetime2 : 31/12/12 23.59.59 correspond to datetime type of c#

    ;)

    Wednesday, November 21, 2012 9:45 AM