Entity framework with autoincrement column

Question

I have read several questions on this already, but I have tried their solution (basically the same with each questions) without success.

I have created a model and used it to update a local database (.sdf) file. Now, I'm trying to add even a single entity and it fails. Meaning that it won't even save any entity. I have set the identity field for my PKs, configured the StoredGeneratedPattern properties, looked at manually setting it directly in the XML (it's there). I still can't save anything. What am I missing?

The entity relation model

enter image description here

The model XML config (TestDbEntities.edmx)

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="TestDbModel.Store" Alias="Self" Provider="System.Data.SqlServerCe.3.5" ProviderManifestToken="3.5" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
  <EntityContainer Name="TestDbModelStoreContainer">
    <EntitySet Name="UserMessages" EntityType="TestDbModel.Store.UserMessages" store:Type="Tables" Schema="dbo" />
    <EntitySet Name="Users" EntityType="TestDbModel.Store.Users" store:Type="Tables" Schema="dbo" />
    <AssociationSet Name="Rel_User_Message" Association="TestDbModel.Store.Rel_User_Message">
      <End Role="users" EntitySet="Users" />
      <End Role="messages" EntitySet="UserMessages" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="UserMessages">
    <Key>
      <PropertyRef Name="ID" />
    </Key>
    <Property Name="ID" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
    <Property Name="Message" Type="nvarchar" Nullable="true" MaxLength="1000" />
    <Property Name="Author_ID" Type="int" Nullable="false" />
  </EntityType>
  <EntityType Name="Users">
    <Key>
      <PropertyRef Name="ID" />
    </Key>
    <Property Name="ID" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
    <Property Name="Identity" Type="nvarchar" Nullable="false" MaxLength="100" />
    <Property Name="Password" Type="nvarchar" Nullable="false" MaxLength="100" />
    <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="100" />
    <Property Name="LastName" Type="nvarchar" Nullable="false" MaxLength="100" />
    <Property Name="Active" Type="bit" Nullable="false" DefaultValue="false" />
  </EntityType>
  <Association Name="Rel_User_Message">
    <End Role="users" Type="TestDbModel.Store.Users" Multiplicity="1" />
    <End Role="messages" Type="TestDbModel.Store.UserMessages" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="users">
        <PropertyRef Name="ID" />
      </Principal>
      <Dependent Role="messages">
        <PropertyRef Name="Author_ID" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="TestDbModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityContainer Name="TestDbEntities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="UserMessages" EntityType="TestDbModel.UserMessage" />
          <EntitySet Name="Users" EntityType="TestDbModel.User" />
          <AssociationSet Name="Rel_User_Message" Association="TestDbModel.Rel_User_Message">
            <End Role="users" EntitySet="Users" />
            <End Role="messages" EntitySet="UserMessages" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="UserMessage">
          <Key>
            <PropertyRef Name="ID" />
          </Key>
          <Property Type="Int32" Name="ID" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Type="String" Name="Message" MaxLength="1000" FixedLength="false" Unicode="true" />
          <NavigationProperty Name="Author" Relationship="TestDbModel.Rel_User_Message" FromRole="messages" ToRole="users" />
        </EntityType>
        <EntityType Name="User">
          <Key>
            <PropertyRef Name="ID" />
          </Key>
          <Property Type="Int32" Name="ID" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Type="String" Name="Identity" MaxLength="100" FixedLength="false" Unicode="true" Nullable="false" />
          <Property Type="String" Name="Password" MaxLength="100" FixedLength="false" Unicode="true" Nullable="false" />
          <Property Type="String" Name="Name" MaxLength="100" FixedLength="false" Unicode="true" Nullable="false" />
          <Property Type="String" Name="LastName" MaxLength="100" FixedLength="false" Unicode="true" Nullable="false" />
          <Property Type="Boolean" Name="Active" Nullable="false" DefaultValue="False" />
          <NavigationProperty Name="AuthoredMessages" Relationship="TestDbModel.Rel_User_Message" FromRole="users" ToRole="messages" />
        </EntityType>
        <Association Name="Rel_User_Message">
          <End Type="TestDbModel.User" Role="users" Multiplicity="1" />
          <End Type="TestDbModel.UserMessage" Role="messages" Multiplicity="*" />
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
    <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
  <EntityContainerMapping StorageEntityContainer="TestDbModelStoreContainer" CdmEntityContainer="TestDbEntities">
    <EntitySetMapping Name="UserMessages">
      <EntityTypeMapping TypeName="IsTypeOf(TestDbModel.UserMessage)">
        <MappingFragment StoreEntitySet="UserMessages">
          <ScalarProperty Name="ID" ColumnName="ID" />
          <ScalarProperty Name="Message" ColumnName="Message" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="Users">
      <EntityTypeMapping TypeName="IsTypeOf(TestDbModel.User)">
        <MappingFragment StoreEntitySet="Users">
          <ScalarProperty Name="ID" ColumnName="ID" />
          <ScalarProperty Name="Identity" ColumnName="Identity" />
          <ScalarProperty Name="Password" ColumnName="Password" />
          <ScalarProperty Name="Name" ColumnName="Name" />
          <ScalarProperty Name="LastName" ColumnName="LastName" />
          <ScalarProperty Name="Active" ColumnName="Active" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <AssociationSetMapping Name="Rel_User_Message" TypeName="TestDbModel.Rel_User_Message" StoreEntitySet="UserMessages">
      <EndProperty Name="users">
        <ScalarProperty Name="ID" ColumnName="Author_ID" />
      </EndProperty>
      <EndProperty Name="messages">
        <ScalarProperty Name="ID" ColumnName="ID" />
      </EndProperty>
    </AssociationSetMapping>
  </EntityContainerMapping>
</Mapping></edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="False" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="False" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams>
      <Diagram Name="Model1">
        <EntityTypeShape EntityType="TestDbModel.UserMessage" Width="1.5" PointX="2.625" PointY="0.5" Height="1.5956835937499996" />
        <EntityTypeShape EntityType="TestDbModel.User" Width="1.5" PointX="0.5" PointY="0.5" Height="2.3648893229166665" />
        <AssociationConnector Association="TestDbModel.Rel_User_Message" >
          <ConnectorPoint PointX="2" PointY="1.2978417968749998" />
          <ConnectorPoint PointX="2.625" PointY="1.2978417968749998" />
        </AssociationConnector>
      </Diagram>
    </Diagrams>
  </Designer>
</edmx:Edmx>

C# (example code)

User user = new User()
{
    Identity = name + "." + lastName,   // generated value
    Password = Convert.ToBase64String(password),  // generated value
    Name = name,                        // ...
    LastName = lastName,                // ...
    Active = rand.NextDouble() > 0.7   // 70% de chance d'être actif
};
ctx.AddToUsers(user);

try
{
    ctx.SaveChanges();

    Console.WriteLine("Created user #" + (i + 1) + " : " + user.Identity);
}
catch (Exception e)
{
    Console.WriteLine(e.InnerException);
    Console.WriteLine("Collision for name " + user.Identity);
}

Error with stack trace

L'exception System.Data.UpdateException n'a pas été gérée
  Message=Une erreur s'est produite lors de la mise à jour des entrées. Pour plus d'informations, consultez l'exception interne.
  Source=System.Data.Entity
  StackTrace:
       à System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
       à System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
       à System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
       à System.Data.Objects.ObjectContext.SaveChanges()
       à TestApplication.entity.EntitiesTest.CreateTestDbEntities(TestDbEntities ctx) dans C:\Users\yrochon\Documents\Visual Studio 2010\Projects\TestApplication\TestApplication\entity\Entities.cs:ligne 119
       à TestApplication.entity.EntitiesTest.Test() dans C:\Users\yrochon\Documents\Visual Studio 2010\Projects\TestApplication\TestApplication\entity\Entities.cs:ligne 26
       à TestApplication.Program.Main(String[] args) dans C:\Users\yrochon\Documents\Visual Studio 2010\Projects\TestApplication\TestApplication\Program.cs:ligne 18
       à System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       à System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       à Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       à System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       à System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.EntityCommandCompilationException
       Message=Une erreur s'est produite lors de la préparation de la définition de la commande. Pour plus de détails, consultez l'exception interne.
       Source=System.Data.Entity
       StackTrace:
            à System.Data.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
            à System.Data.Mapping.Update.Internal.DynamicUpdateCommand.CreateCommand(UpdateTranslator translator, Dictionary`2 identifierValues)
            à System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
            à System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
       InnerException: System.NotSupportedException
            Message=Les clés et les valeurs générées par le serveur ne sont pas prises en charge par SQL Server Compact.
            Source=System.Data.SqlServerCe.Entity
            StackTrace:
                 à System.Data.SqlServerCe.SqlGen.DmlSqlGenerator.GenerateReturningSql(StringBuilder commandText, DbModificationCommandTree tree, ExpressionTranslator translator, DbExpression returning)
                 à System.Data.SqlServerCe.SqlGen.DmlSqlGenerator.GenerateInsertSql(DbInsertCommandTree tree, List`1& parameters, Boolean isLocalProvider)
                 à System.Data.SqlServerCe.SqlGen.SqlGenerator.GenerateSql(DbCommandTree tree, List`1& parameters, CommandType& commandType, Boolean isLocalProvider)
                 à System.Data.SqlServerCe.SqlCeProviderServices.CreateCommand(DbProviderManifest providerManifest, DbCommandTree commandTree)
                 à System.Data.SqlServerCe.SqlCeProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
                 à System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
                 à System.Data.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree)
                 à System.Data.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
            InnerException:

Is this the only solution? Manually generating identities (even if they are supposed to be auto incremented by the database)?

I updated the question. Sorry, VS is installed in french and this is what it is. Basically, it can't save the entity because it's identity field is set to `0`. — Yanick Rochon, Oct 01 '12 at 20:21
@SidharthMudgal, I originally designed the db first, but then found it easier to use the model designer and genrating my SQL script to update (create) my db schema, since you can't modify table columns other than by manually edit SQL strings anyway, whereas with the model designer, it is much easier and graphical. The original db schema (done by creating tables from the properties windows) had a second key (sequence?) that I cannot reproduce with the visual model designer. — Yanick Rochon, Oct 01 '12 at 20:25

score 1 · Accepted Answer · answered Oct 01 '12 at 20:24

1

Upgrade the SQL Compact Edition to Version 4 and will auto increment the id.

answered Oct 01 '12 at 20:24

Stefan P.

9,489
6
29
43

1

I had to also install the VS 2010 SP1 (http://www.microsoft.com/en-us/download/details.aspx?id=23691) and some "mysterious tool" here : http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/8c46681f-5a64-4eeb-8c2b-ae9bf29057bd (Way to go Microsoft!) But now it works. – Yanick Rochon Oct 01 '12 at 21:59

Entity framework with autoincrement column

1 Answers1