Unable to scaffold “datetime” type columns from MySQL in .net core 3.1

user_6072224 0
Viewed 2179

A similar issue has been posted in the past about scaffolding in .net core 2 but that was on a date column and no solution has been posted to date.

There's no specific table as any table containing a datetime column type throws the following error from the console:

Could not find type mapping for column 'database.table.columnname' with data type 'datetime'. Skipping column.

This answer to a question about reading datetime values and a MySQL quirk with 0000-00-00 00:00:00 values made me wonder if changing the connection string format would help. So I added the ConvertZeroDateTime=true to the connection string in the scaffold command:

Scaffold-DbContext "server=localhost;port=3306;user=root;password=notherealpassword;database=sodb;ConvertZeroDateTime=true;" MySql.Data.EntityFrameworkCore -OutputDir Model -f

I still have the same error, does anyone have any suggestions? I have 380+ tables in this database so an automated solution would be most helpful.

.net-core-3.1 entity-framework-core datetime mysql
4 Answers

I had the same problem and now I solved it.

I can't be assured if my solution works to your case but I think this solution would work to your case.

  1. Remove all packages related to Entity Framework.
  2. Install the following packages the version is 5.0.4

enter image description here

  1. Open Package Manager Console window. Now we will install Pomelo packages it depends on .Net standard.
  2. Enter the command “Install-Package Pomelo.EntityFrameworkCore.MySql -Version 5.0.0-alpha.2”
  3. Enter the command "Install-Package Pomelo.EntityFrameworkCore.MySql.NetTopologySuite -Version 5.0.0-alpha.2"

The commands try to install the following packages.

enter image description here

  1. Now Enter to Package Manager Console window the following command. Scaffold-DbContext "server=localhost;port=3306;uid=root;pwd=notherealpassword;database=sodb; "Pomelo.EntityFrameworkCore.MySql" -OutputDir Models -f

  2. The result of command is as below. enter image description here

I hope this post helps you.

user_119223610

I was scaffolding off mySql 5.7 in .net core 2.2 using pomelo no problem until i tried upgrading to 3.1

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.2.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.2.6">
<PackageReference Include="MySql.Data" Version="8.0.21" />
<PackageReference Include="MySql.Data.EntityFrameworkCore.Design" Version="8.0.19" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.2.6" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Design" Version="1.1.2" />

This combination worked for me calling

PM> Scaffold-DbContext [ConnectionStringHere] MySql.Data.EntityFrameworkCore -OutputDir [NameHere] -Project "[NameHere]" -Force

With the below packages, scaffolding just skips datetime completely (also bytes are converted to bools)

So to workaround. I used my source control to keep the old datetime classes and just manually updated the bools. You could try downgrading to older versions to see if helps.

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.11"/>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.11" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.11">
<PackageReference Include="MySql.Data" Version="8.0.23" />
<PackageReference Include="MySql.Data.EntityFrameworkCore.Design" Version="8.0.19" />
<PackageReference Include="NonFactors.Grid.Mvc6" Version="6.1.0" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.2.4" />
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Design" Version="1.1.2" />
user_31234100

I have achieved a fix for this today with the following Nugets: - EFCore / Net 5.0 / MySql

And then in Package Manager Console of Visual Studio: -

Scaffold-DbContext "server=MYSERVER;port=3306;database=MYDB;user=MYUSER;password=MYPW;" MySql.EntityFrameworkCore -UseDatabaseNames -f

The "-UseDatabaseNames" and "-f" flags are not essential to the bare fix, but that's what I'm using.

The crucial detail is that MySql.EntityFrameworkCore v5.0.3 works where v5.0.0 does not.

user_15348670

I was getting datetime/timestamp erros like yours, I must had to use those packages to made to work mysql.entityframeworkcore isnt necessary

And the command to export is

dotnet ef dbcontext scaffold "Database=@dbname;Data Source=@ip:@port;User Id=@user;Password=@pass;Initial Catalog=@dbname;" pomelo.entityframeworkcore.mysql -o Models -f -c DBContext
user_38388050
Related