MySql Entity Framework auto_increment error

Question

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.

MySql 5.6.27, EF6.

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.

Here is the table def:

create table tblCompanySequence (
    Id int auto_increment primary key not null default 1
);

Here is the corresponding c# def:

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; }
    }
}

and here is the code:

var newSeq = new EFCompanySequence();
var tableSeq = context.GetTable<EFCompanySequence>();
tableSeq.InsertOnSubmit(newSeq);
context.SubmitChanges();
var newId = newSeq.Id;

I get an error on the call to submit changes.

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

I've tried a number of permutations, e.g:

create table tblCompanySequence (
    Id int auto_increment not null,
    primary key (Id)
);

and using DbGenerated annotations on the EF table object, but still hitting the same wall.

Any suggestions greatly appreciated.

Cheers, Andy

UPDATE 1:

Here are my configuration settings (set up as per https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html)

<configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>

<system.data>
    <DbProviderFactories>
       <remove invariant="MySql.Data.MySqlClient" />
       <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" />
    </DbProviderFactories>
</system.data>

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>

UPDATE 2:

I read on another site that i may be able to use the following code to get overcome this.

var newId = context.ExecuteCommand("insert into tblCompanySequence values (null); select LAST_INSERT_ID();");

This code succeeds in inserting a new row with incremented id into the database, but the return value from the select is always 1.

I'm sure this must be something obvious I'm doing wrong.

scope_idntity() is a tsql function, not a mysql one. Did you configure EF to use mysql instead of ms sql? — Shadow
– Shadow, Commented Oct 7, 2015 at 11:13
I'm a relative noob when it comes to MySql. I set the Sql Server Mode switch on the connection string. Is this what you mean. <add name="DEV" connectionString="server=localhost;user id=andy;pwd=test;database=dev1mysql; sql server mode=true" providerName="MySql.Data.MySqlClient" /> — andy
– andy, Commented Oct 7, 2015 at 11:22

Bellash · Accepted Answer · 2015-10-07 11:24:13Z

0

You shoul configure your EntityFramework to work with MySql and then it will use LAST_INSERT_ID() instead of score_identity(). Have a look at the config file

  <connectionStrings>
<add name="DefaultConnection"
providerName="MySql.Data.MySqlClient"
connectionString="[Insert your ConnectionString to the mysql database here]"/>
</connectionStrings>

answered Oct 7, 2015 at 11:24

Bellash

8,2776 gold badges57 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andy Over a year ago

Here's mine - looks ok to me <add name="DEV" connectionString="server=localhost;user id=andy;pwd=test;database=dev1mysql; sql server mode=true" providerName="MySql.Data.MySqlClient" />

Umar Farooq · Accepted Answer · 2015-10-07 11:58:09Z

0

You shoul configure your EntityFramework to work with MySql and Modify the sode below according to your demand

Syntax for MySQL The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (ID) )

answered Oct 7, 2015 at 11:58

Umar Farooq

261 bronze badge

1 Comment

andy Over a year ago

That is what i have - please see my question above: create table tblCompanySequence ( Id int auto_increment not null, primary key (Id) );

consuela · Accepted Answer · 2015-10-07 12:12:45Z

0

If you really need this you can achieve your goal with help of this syntax. Try this and modify it according to your needs:

CREATE TABLE table1_seq
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE table1
(
id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

Try this. I hope you will understand,

edited Oct 7, 2015 at 12:12

consuela

1,7136 gold badges21 silver badges24 bronze badges

answered Oct 7, 2015 at 11:35

Umar Farooq

261 bronze badge

1 Comment

andy Over a year ago

Your first create statement looks the same as mine, but excludes the default 1 (which is another permutation i tried). I don't understand what the second create statement is supposed do in this context - can you explain a bit further?

andy · Accepted Answer · 2015-10-08 12:40:01Z

0

I think i may have found an answer (but no satisfactory solution yet).

I'm using Linq to SQL (not Linq to Entities), and it appears there is no out of the box support for this in MySql.Data etc.

I've started playing with the Depart linqExpress solution, but on initial glance it appears I would have to re-write substantial portions of code to make this work.

Unless anyone has another solution.

Cheers.

answered Oct 8, 2015 at 12:40

andy

11 silver badge3 bronze badges

Collectives™ on Stack Overflow

MySql Entity Framework auto_increment error

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related