title: MySql Entity Framework auto_increment error
body: <p>I'm have a system that uses Entity Framework against a SQL Server db that runs fine. Recently I decided to move to MySql as the backend. The error is caused by some code that runs perfectly well against SQLServer, but fails against MySql.</p>
  
  <p>MySql 5.6.27, EF6.</p>
  
  <p>I have a table with 1 column (called Id), which i want to use as a sequence counter. I achieve this by making Id a primary key and auto_generated.</p>
  
  <p>Here is the table def:</p>
  
  <pre><code>create table tblCompanySequence (
      Id int auto_increment primary key not null default 1
  );
  </code></pre>
  
  <p>Here is the corresponding c# def:</p>
  
  <pre><code>using System.Data.Linq.Mapping;
  
  namespace Foo.DataAccess.EF.Entity
  {
      [Table(Name = "tblCompanySequence")]
      public class EFCompanySequence
      {   
          [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
          public int Id { get; set; }
      }
  }
  </code></pre>
  
  <p>and here is the code:</p>
  
  <pre><code>var newSeq = new EFCompanySequence();
  var tableSeq = context.GetTable&lt;EFCompanySequence&gt;();
  tableSeq.InsertOnSubmit(newSeq);
  context.SubmitChanges();
  var newId = newSeq.Id;
  </code></pre>
  
  <p>I get an error on the call to submit changes.</p>
  
  <pre><code>A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in System.Data.Linq.dll
  
  Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES
  
  SELECT CONVERT(Int,SCOPE_IDENTITY()) AS `value`' at line 1
  </code></pre>
  
  <p>I've tried a number of permutations, e.g:</p>
  
  <pre><code>create table tblCompanySequence (
      Id int auto_increment not null,
      primary key (Id)
  );
  </code></pre>
  
  <p>and using DbGenerated annotations on the EF table object, but still hitting the same wall.</p>
  
  <p>Any suggestions greatly appreciated.</p>
  
  <p>Cheers,
  Andy</p>
  
  <p><strong>UPDATE 1:</strong> </p>
  
  <p>Here are my configuration settings (set up as per <a href="https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html" rel="nofollow">https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html</a>)</p>
  
  <pre><code>&lt;configSections&gt;
      &lt;section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /&gt;
  &lt;/configSections&gt;
  
  &lt;system.data&gt;
      &lt;DbProviderFactories&gt;
         &lt;remove invariant="MySql.Data.MySqlClient" /&gt;
         &lt;add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /&gt;
      &lt;/DbProviderFactories&gt;
  &lt;/system.data&gt;
  
  &lt;entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6"&gt;
      &lt;defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" /&gt;
      &lt;providers&gt;
          &lt;provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/&gt;
          &lt;provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /&gt;
      &lt;/providers&gt;
  &lt;/entityFramework&gt;
  </code></pre>
  
  <p><strong>UPDATE 2:</strong></p>
  
  <p>I read on another site that i may be able to use the following code to get overcome this.</p>
  
  <pre><code>var newId = context.ExecuteCommand("insert into tblCompanySequence values (null); select LAST_INSERT_ID();");
  </code></pre>
  
  <p>This code succeeds in inserting a new row with incremented id into the database, but the return value from the select is always 1.</p>
  
  <p>I'm sure this must be something obvious I'm doing wrong.</p>