Boolean in primary key is causing update \delete issue.

[mansoor_khalid]

Boolean in primary key is causing update \delete issue.
The exception encountered is :

Code: Select all

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.

The stack trace is:

Code: Select all

   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at ConsoleTester.Program.Main(String[] args) in d:\WorkSpace\Projects\OracleDriver\ConsoleTester\Program.cs:line 70

The Code to reproduce the issue is:

Code: Select all

            #region Create
            using (var context = new Entities())
            {
                using (var dbContextTransaction = context.Database.BeginTransaction())
                {
                    try
                    {
                        context.TESTs.Add(new TEST() { PK1 = 1,PK2 = true,COMPLETED = "NO"});
                        context.SaveChanges();
                        dbContextTransaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        dbContextTransaction.Rollback();
                    }
                }
            }
            #endregion
            //----------------------------------------------------------
            #region Update
            using (var context = new Entities())
            {
                using (var dbContextTransaction = context.Database.BeginTransaction())
                {
                    try
                    {
                        var update = context.TESTs.First();
                        update.COMPLETED = "YES";
                        context.SaveChanges();
                        //---------------------------------
                        dbContextTransaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        dbContextTransaction.Rollback();
                    }
                }
            }
            #endregion

The edmx file to produce the issue is :

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="Model.Store" Provider="Devart.Data.Oracle" ProviderManifestToken="Oracle, 11.2.0.2" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
        <EntityType Name="TEST">
          <Key>
            <PropertyRef Name="PK1" />
            <PropertyRef Name="PK2" />
          </Key>
          <Property Name="PK1" Type="decimal" Precision="29" Scale="0" Nullable="false" />
          <Property Name="PK2" Type="bool" Nullable="false" />
          <Property Name="COMPLETED" Type="NVARCHAR2" MaxLength="50" Nullable="false" />
        </EntityType>
        <EntityContainer Name="ModelStoreContainer">
          <EntitySet Name="TEST" EntityType="Self.TEST" Schema="TESTING_DBO" store:Type="Tables" />
        </EntityContainer>
      </Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="Model" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
        <EntityType Name="TEST">
          <Key>
            <PropertyRef Name="PK1" />
            <PropertyRef Name="PK2" />
          </Key>
          <Property Name="PK1" Type="Decimal" Precision="29" Scale="0" Nullable="false" />
          <Property Name="COMPLETED" Type="String" MaxLength="50" FixedLength="false" Unicode="true" Nullable="false" />
          <Property Name="PK2" Type="Boolean" Nullable="false" />
        </EntityType>
        <EntityContainer Name="Entities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="TESTs" EntityType="Self.TEST" />
        </EntityContainer>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="ModelStoreContainer" CdmEntityContainer="Entities">
          <EntitySetMapping Name="TESTs">
            <EntityTypeMapping TypeName="Model.TEST">
              <MappingFragment StoreEntitySet="TEST">
                <ScalarProperty Name="PK2" ColumnName="PK2" />
                <ScalarProperty Name="PK1" ColumnName="PK1" />
                <ScalarProperty Name="COMPLETED" ColumnName="COMPLETED" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="true" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="true" />
        <DesignerProperty Name="UseLegacyProvider" Value="false" />
        <DesignerProperty Name="CodeGenerationStrategy" Value="None" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams></Diagrams>
  </Designer>
</edmx:Edmx>

And the Oracle database table script provided by "dbForge fusion for Oracle" used is:

Code: Select all

CREATE TABLE TESTING_DBO.TEST (
    PK1       NUMBER(*, 0),
    PK2       NUMBER(1, 0),
    COMPLETED NVARCHAR2(50) NOT NULL,
    CONSTRAINT CK_BOOL CHECK (PK2 >= 0 AND PK2 <= 1),
    CONSTRAINT PK_TEST PRIMARY KEY (PK1, PK2)
)
LOGGING;

The App.config is as follows

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <providers>
      <!--<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />-->
      <provider invariantName="Devart.Data.Oracle" type="Devart.Data.Oracle.Entity.OracleEntityProviderServices, Devart.Data.Oracle.Entity, Version=8.4.359.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="Devart.Data.Oracle" />
      <add name="dotConnect for Oracle" invariant="Devart.Data.Oracle" description="Devart dotConnect for Oracle" type="Devart.Data.Oracle.OracleProviderFactory, Devart.Data.Oracle, Version=8.4.359.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="Entities" connectionString="metadata=res://*/Test.csdl|res://*/Test.ssdl|res://*/Test.msl;provider=Devart.Data.Oracle;provider connection string="User Id=Test_dbo;Password=Testing_dbo;Server=localhost;Direct=True;Sid=xe;Persist Security Info=True"" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Let me know if you need more information.

[Shalex]

Thank you for your report. We have reproduced the error and are investigating the issue.

[Shalex]

The bug with generating SQL for the INSERT / UPDATE / DELETE operations when using bool property mapped to Number(1,0) is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New build of dotConnect for Oracle 8.4.379 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=31498.