Entity Framework: Getting exception while inserting row in MySQL Table

Question

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

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

Are you sure this error relates to uuid thing ? may be some other part of code is throwing this exception can you add some more details related to this object reference error? — M Khalid Junaid
– M Khalid Junaid, Commented May 6, 2022 at 7:24
Hi @MKhalidJunaid, I have added Stack Trace in the post. Hope it is helpful. Please let me know if you require anything else. — simple user
– simple user, Commented May 6, 2022 at 8:29
Can you check GenderBLL.cs:line 33 part what it says may be the main object is null and you are trying to use its property — M Khalid Junaid
– M Khalid Junaid, Commented May 6, 2022 at 9:29
My initial guess is that vmGender is not properly set or its null — M Khalid Junaid
– M Khalid Junaid, Commented May 6, 2022 at 9:32
I checked that vmGender is not null. This is failing even when I am assigning hardcoded values to the column instead of reading from vmGender. It also didnt work when I am setting hardcoded value explicitly to Primary Key column org_genderid as well. Is it like Identity dont work for primary Key column having string data type in MySQL? — simple user
– simple user, Commented May 6, 2022 at 10:51

Kieran Foot · Accepted Answer · 2022-05-12 15:07:11Z

4

+50

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().

answered May 12, 2022 at 15:07

Kieran Foot

7555 silver badges15 bronze badges

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

3 Comments

simple user Over a year ago

If I set value explicitly like you have mentioned then it will work. I know this. My only concern is that if suppose Guid.NewGuid generates same key twice for same table then exception will occur as the column is primary key. It would be much better if database generates automatically but it seems it will not generate automatically if I keep varchar for primary key.

Kieran Foot Over a year ago

I see your concern, but exactly how many guids are you planning to create? There is an very low chance of generating the same guid, even across different machines. See: learn.microsoft.com/en-us/dotnet/api/system.guid?view=net-6.0

Kieran Foot Over a year ago

Not sure why allowing your sql server to generate the guid is ok, but letting C# do the same isn't, their uniqueness should be equivelant. I use this as does Microsoft in their code without incident. It is the default for string based Id's.

David Jiboye · Accepted Answer · 2022-05-12 09:37:20Z

-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.

answered May 12, 2022 at 9:37

David Jiboye

5098 silver badges19 bronze badges

1 Comment

Kieran Foot Over a year ago

I fail to see how any of your comments offer any help. There is no AUTO_INCREMENT in his table schema.

Collectives™ on Stack Overflow

Entity Framework: Getting exception while inserting row in MySQL Table

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related