Pomelo.entityframeworkcore.mysql: An exception occurred while reading a database value. The expected type was 'System.Byte[]' but the actual value was of type 'System.DateTime'.

[tokaika]

Prevents concurrent fields from being automatically cannot identified

```c#
namespace Sky.Model.Domain
{
public class Category
{
public Guid Id { get; set; }

    public Guid ParentId { get; set; }


    public string CategoryName { get; set; }


    public Int64 ChildCount { get; set; }



    public DateTime DateCreated { get; set; }


    public byte[] RowVersion { get; set; }


}

namespace Sky.Core.Mapping
{
public class CategoryMap
{
public static void Map(EntityTypeBuilder builder)
{
builder.ToTable("Category");
builder.HasKey(o => o.Id);
builder.Property(o => o.Id).IsRequired().HasMaxLength(36);
builder.Property(o => o.ParentId).IsRequired().HasMaxLength(36).IsUnicode(false);
builder.Property(o => o.CategoryName).HasMaxLength(64).IsRequired();
builder.HasIndex(o => o.CategoryName);
builder.Property(o => o.ChildCount).IsRequired();
builder.Property(o => o.DateCreated).IsRequired();
builder.Property(o => o.RowVersion).IsRequired().IsRowVersion().ValueGeneratedOnAddOrUpdate();
}
}
}
}

when  a  insert data。。。
1.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'System.Byte[]' but the actual value was of type 'System.DateTime'.

2.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.Byte[]'.

```c#
 var category1 = new Category
                {
                    CategoryName = "News",
                    ChildCount = 0,
                    ParentId = Guid.Empty,
                    DateCreated = DateTime.UtcNow
                };
 _context.Categories.Add(category1);
  _context.SaveChanges();

logger ：sql

2017-08-12 21:39:28.1219|Microsoft.EntityFrameworkCore.Database.Command|INFO|Executed DbCommand (21ms) [Parameters=[@p0='?', @p2='?' (Size = 64), @p4='?', @p6='?', @p8='?', @p12='?', @p14='?' (Size = 64), @p16='?', @p18='?', @p20='?', @p24='?', @p26='?' (Size = 64), @p28='?', @p30='?', @p32='?', @p36='?', @p38='?' (Size = 64), @p40='?', @p42='?', @p44='?', @p48='?', @p50='?' (Size = 64), @p52='?', @p54='?', @p56='?', @p60='?', @p62='?' (Size = 64), @p64='?', @p66='?', @p68='?', @p72='?', @p74='?' (Size = 64), @p76='?', @p78='?', @p80='?', @p84='?', @p86='?' (Size = 64), @p88='?', @p90='?', @p92='?', @p96='?', @p98='?' (Size = 64), @p100='?', @p102='?', @p104='?', @p108='?', @p110='?' (Size = 64), @p112='?', @p114='?', @p116='?', @p120='?', @p122='?' (Size = 64), @p124='?', @p126='?', @p128='?', @p132='?', @p134='?' (Size = 64), @p136='?', @p138='?', @p140='?', @p144='?', @p146='?' (Size = 64), @p148='?', @p150='?', @p152='?', @p156='?', @p158='?' (Size = 64), @p160='?', @p162='?', @p164='?', @p168='?', @p170='?' (Size = 64), @p172='?', @p174='?', @p176='?', @p180='?', @p182='?' (Size = 64), @p184='?', @p186='?', @p188='?', @p192='?', @p194='?' (Size = 64), @p196='?', @p198='?', @p200='?', @p204='?', @p206='?' (Size = 64), @p208='?', @p210='?', @p212='?', @p216='?', @p218='?' (Size = 64), @p220='?', @p222='?', @p224='?', @p228='?', @p230='?' (Size = 64), @p232='?', @p234='?', @p236='?', @p240='?', @p242='?' (Size = 64), @p244='?', @p246='?', @p248='?', @p252='?', @p254='?' (Size = 64), @p256='?', @p258='?', @p260='?', @p264='?', @p266='?' (Size = 64), @p268='?', @p270='?', @p272='?', @p276='?', @p278='?' (Size = 64), @p280='?', @p282='?', @p284='?', @p288='?', @p290='?' (Size = 64), @p292='?', @p294='?', @p296='?'], CommandType='Text', CommandTimeout='0']
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p0, @p2, @p4, @p6, @p8);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p0;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p12, @p14, @p16, @p18, @p20);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p12;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p24, @p26, @p28, @p30, @p32);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p24;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p36, @p38, @p40, @p42, @p44);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p36;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p48, @p50, @p52, @p54, @p56);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p48;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p60, @p62, @p64, @p66, @p68);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p60;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p72, @p74, @p76, @p78, @p80);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p72;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p84, @p86, @p88, @p90, @p92);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p84;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p96, @p98, @p100, @p102, @p104);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p96;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p108, @p110, @p112, @p114, @p116);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p108;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p120, @p122, @p124, @p126, @p128);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p120;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p132, @p134, @p136, @p138, @p140);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p132;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p144, @p146, @p148, @p150, @p152);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p144;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p156, @p158, @p160, @p162, @p164);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p156;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p168, @p170, @p172, @p174, @p176);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p168;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p180, @p182, @p184, @p186, @p188);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p180;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p192, @p194, @p196, @p198, @p200);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p192;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p204, @p206, @p208, @p210, @p212);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p204;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p216, @p218, @p220, @p222, @p224);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p216;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p228, @p230, @p232, @p234, @p236);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p228;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p240, @p242, @p244, @p246, @p248);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p240;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p252, @p254, @p256, @p258, @p260);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p252;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p264, @p266, @p268, @p270, @p272);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p264;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p276, @p278, @p280, @p282, @p284);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p276;
INSERT INTO `Category` (`Id`, `CategoryName`, `ChildCount`, `DateCreated`, `ParentId`)
VALUES (@p288, @p290, @p292, @p294, @p296);
SELECT `RowVersion` FROM `Category` WHERE `Id` = @p288; 
2017-08-12 21:39:28.3123|Microsoft.EntityFrameworkCore.Update|ERROR|An exception occurred in the database while saving changes for context type 'Sky.Core.SkyDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'System.Byte[]' but the actual value was of type 'System.DateTime'. ---> System.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.Byte[]'.
   at Microsoft.EntityFrameworkCore.Storage.Internal.WrappedMySqlDataReader.ConvertWithReflection[T](Int32 ordinal, InvalidCastException e)
   at Microsoft.EntityFrameworkCore.Storage.Internal.WrappedMySqlDataReader.GetFieldValue[T](Int32 ordinal)
   at lambda_method(Closure , DbDataReader )
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagation(Int32 commandIndex, DbDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(DbDataReader reader)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(DbDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess) An error occurred while updating the entries. See the inner exception for details.

[souchprod]

Hi, does it still fail if you add the [Timestamp] attribute to the rowversion property ?

    public class Category 
    {
        public Guid Id { get; set; }   

        public Guid ParentId { get; set; }  

        public string CategoryName { get; set; }   

        public Int64 ChildCount { get; set; }   

        public DateTime DateCreated { get; set; }

        [Timestamp]
        public byte[] RowVersion { get; set; }
    }

[tokaika]

@souchprod i donnot know 。。i donnot give a try。。i just delete this field（rowversion）

[souchprod]

No, finally I tried and reproduced your issue, the attributes doesn't help. So far I was not able to locate the issue, seems to be a deep one..

[souchprod]

For the record, i also reproduce this bug with SByte (generated from scaffoding, with a column type: tinyint(1)). In this case the error raised is

An exception occurred while reading a database value. The expected type was 'System.Nullable1[System.SByte]' but the actual value was of type 'System.Boolean'.
InvalidCastException: Unable to cast object of type 'System.Boolean' to type 'System.SByte
'

with the same stacktrace.

I am working on it.
`

[caleblloyd]

@souchprod that's because TreatTinyAsBoolean defaults to true in the MySQL Driver: https://mysql-net.github.io/MySqlConnector/connection-options/

Change your column to tinyint(3) or set TreatTinyAsBoolean=false in your connection string.

[souchprod]

True thanks, i figured it out as the exact moment of your comment :)
I took the time to read the discussion history about this flag, indeed there is no perfect default value for it..

[pealmeid]

I'm having the same problem.

I added a byte[] RowVersion field to my table (with the [Timestamp] annotation) in order to handle concurrency conflicts (as per instructions on https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/concurrency), and the migration and database update occured without issues.

However, when I tried to update the dabatase I got the error:

An exception occurred while reading a database value. The expected type was 'System.Byte[]'
but the actual value was of type 'System.DateTime'.

Any pointers on how to proceed are greatly appreciated. Thanks in advance.

[caleblloyd]

@pealmeid use DateTime RowVersion not byte[] RowVersion

[tokaika]

Waiting for solve this problem @-@.

发自我的 iPhone

在 2017年8月30日，下午10:55，Caleb Lloyd <[email protected][e-mail]> 写道：

@pealmeidhttps://github.com/pealmeid use DateTime RowVersion not byte[] RowVersion

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/351#issuecomment-326016598, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ANi0KhiWTNStSyqqrqNs1755ezr7Flg7ks5sdXfKgaJpZM4O1bUr.

[pealmeid]

@caleblloyd Thanks for the reply. I tried using DateTime and the error disappeared.

However, the field is supposed to auto-update every time there is a change in some other field in the row (in order to do the concurrency check), but this is not happening - the field keeps the same value and EF does not check for concurrency issues.

Any help on how to implement this? Once again, thanks in advance.

[caleblloyd]

There was an issue with Concurrency Tokens, I just submitted a PR for the fix. It will be in 2.0.0.

Here is a sample model:

    public class GeneratedConcurrencyToken
    {
        public int Id { get; set; }

        public int Gen { get; set; }

        [Timestamp]
        public DateTime Updated { get; set; }
    }

This will work after #375 lands

[pealmeid]

I was able to make it work (somewhat, see below) using the following:

public class Item
{
    public int Id { get; set; }
    public string SomeData { get; set; }


    [Timestamp]
    [Column(TypeName = "TIMESTAMP(6)")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime RowVersion { get; set; }
}

This code creates a RowVersion field that gets updated automatically every time the record is edited. However, EF does not raise a DbConcurrencyException if you try to update the record with a different RowVersion from the one recorded last.

If I understand EF's concurrency model correctly, RowVersion is used to make sure there were no updates to the record between the reading of the original data and the submitting of the edited data: if the RowVersion when you submit the new data is different from the one when you first read it, it means someone else updated the record in the meantime.

When I use SQL Server, EF raises a DbConcurrencyException automatically under this condition. However, with MySql, this didn't happen. I had to code the RowVersion check and raise the exception explicitly.

Is this behavior by design, or is there a way to configure EF to raise the exception automatically with MySql as it does with SQL Server?

Thanks in advance.

[caleblloyd]

@pealmeid you need the changes from #375. I just published 2.0.0-rtm-10061, that will work.

The [Timestamp] column will now automatically add the [DatabaseGenerated(DatabaseGeneratedOption.Computed)] option, so you can just specify [Timestamp],

[pealmeid]

@caleblloyd thanks for the fast reply! I installed 2.0.0-rtm-10061 and can confirm the [DatabaseGenerated(DatabaseGeneratedOption.Computed)] annotation is no longer necessary.

However, the behavior has remained the same, i.e. EF does not automatically raise a DbConcurrencyException when the Timestamp field value is different on an update operation. I have to make an explicit check and throw the exception if needed.

This behavior is different in the SQL Server EF provider and I'm wondering if it is so by design on MySql. Can you clarify?

Once again, thanks a lot for you help!

[caleblloyd]

I wrote a test case to confirm that DbConcurrencyException is thrown:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/e358308ed628f2c15ad181b5651f1d779b7d4578/test/EFCore.MySql.FunctionalTests/Tests/Models/GeneratedTypesTest.cs#L102-L123

If you have a test case where it doesn't work properly could you please submit the code.

[pealmeid]

@caleblloyd once again, thank you for your help. It turns out we were using MariaDb instead of MySQL. We changed the database to MySQL and now it's working as expected.

I tried to write a test for the MariaDb case but I wasn't able to run the test setup script. Apparently the SQL generated for the migrations use some JSON functions not yet implemented in MariaDb.

Anyway, it is my understanding that MariaDb is not a supported platform for the Pomelo EF provider, so we will use MySQL from now on. Thanks once more!

[caleblloyd]

We aim to try and support MariaDB but don't explicitly test against it. Always happy to accept a PR that improves functionality for MariaDB!

[csutorasr]

How to make it work? I tried the following:

        [Timestamp]
        [Column(TypeName = "DATETIME(6)")]
        public DateTime RowVersion { get; set; }

But the problem is, when migrations are created I need to change to byte[] the type. After the migrations I must change back to work.

If DateTime is the type during migrations, the default and onupdate value is not set.
If byte[] is the type, the code throws cannot convert between types error.

It also won't work with [Column(TypeName = "TIMESTAMP(6)")] or without ColumnAttribute.

EDIT:
I also tried with [Column(TypeName = "DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")] but the migration deletes the default value afterwards. If I add defaultSqlValue, the migration fails.

[mguinness]

@csutorasr You didn't specify your database or version, are you using MariaDB? Also you must use DateTime RowVersion not byte[] RowVersion as stated in an earlier comment.

[csutorasr]

I use MySQL 5.7.19.

I solved it with changing type to byte[] while the migration generating files. Afterwards I wrote it back to DateTime. This is a workaround not how it supposed to work.

EDIT:
The tables are in MyISAM format.