'Entity Framework: Getting exception while inserting row in MySQL Table

I recently switched my database from SQL Server to MySQL. I am still using Entity Framework 6 to do CRUD operation in database. When I was using SQL Server as backend then I was setting "StoreGeneratedPattern" as "Identity" to Primary Column of Table so that it could generate unique GUID. I am following the same approach in MySQL but I am receiving below error. The only difference is that data type of Primary Key in SQL Server was GUID and it is varchar in MySQL with UUID() as default. Can you please help in identifying the issue?

Whenever I set "StoreGeneratedPattern" to None and give Primary Key an arbitary value from code then it is working fine. However I dont want to generate Primary Key value from code.

Object Reference not set to an instance.

MySQL Table

CREATE TABLE Gender
(
    org_genderid VARCHAR(40) NOT NULL PRIMARY KEY DEFAULT(uuid()),  
    org_gendername varchar(100) NOT NULL,   
    org_ispublished bit NOT NULL,       
    org_createdby varchar(40) NOT NULL,
    org_createdon datetime NOT NULL,
    org_modifiedby varchar(40) NOT NULL,
    org_modifiedon datetime NOT NULL
);

Entity Framework to insert the data

Please note that every property used here contain value

using (var ctxAddGender = new STREAM_EMPLOYEEDBEntities())
            {
                var entGender = new gender()
                {
                    org_gendername = vmGender.Name,
                    org_ispublished = true,
                    org_createdby = vmGender.CreatedBy,
                    org_createdon = DateTime.Now.ToUniversalTime(),
                    org_modifiedby = vmGender.ModifiedBy,
                    org_modifiedon = DateTime.Now.ToUniversalTime(),

                };

                ctxAddGender.genders.Add(entGender);

                int success = ctxAddGender.SaveChanges();
}

Identity as StoreGeneratedPattern

enter image description here

Stack Trace

   at MySql.Data.EntityFramework.ListFragment.WriteSql(StringBuilder sql)
   at MySql.Data.EntityFramework.SelectStatement.WriteSql(StringBuilder sql)
   at MySql.Data.EntityFramework.InsertStatement.WriteSql(StringBuilder sql)
   at MySql.Data.EntityFramework.SqlFragment.ToString()
   at MySql.Data.EntityFramework.InsertGenerator.GenerateSQL(DbCommandTree tree)
   at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.CreateCommand(Dictionary`2 identifierValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<>c.<Update>b__21_0(UpdateTranslator ut)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
   at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__153_0()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass148_0.<SaveChangesInternal>b__0()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at Application.Business.BLL.BusinessLayer.GenderBLL.AddGender(GenderViewModel vmGender) in G:\onMyTune\IP\MySQL Projects\Project Acceleration - Employee Web API\Application.Business\BLL\BusinessLayer\GenderBLL.cs:line 33
   at Application.WebAPI.Controllers.GenderController.AddGender(GenderViewModel vmGender) in G:\onMyTune\IP\MySQL Projects\Project Acceleration - Employee Web API\Project Acceleration - Employee Web API\Controllers\GenderController.cs:line 36
c++sharpmysqlentity-frameworkentity-framework-6linq-to-entities


Solution 1:[1]

The problem may be that EF is validating your primary key as being NULL before it attempts to perform the update/insert. If this is the case;

In your entity class set the genderid value to Guid.NewGuid().ToString().

Solution 2:[2]

Try some of the following:

  • Reset the AUTO_INCREMENT using ALTER TABLE Gender AUTO_INCREMENT = 0 if table is empty.
  • You could just recreate the table.
  • Delete the mapped entity from your project then re-import it from the database.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kieran Foot
Solution 2 David Jiboye

Related Questions