Select Page

[:de].NET Framework, MySQL: DataSets parallel mit EntityFrameworkCore-Scaffolding nutzen[:en].NET Framework, MySQL: Using DataSets in parallel with EntityFrameworkCore scaffolding[:]

by | Dec 6, 2018 | C# .Net, Software | 0 comments

This entry has been published on 2018-12-06 and may be out of date.

Last Updated on 2018-12-06.

[:en]Keeping .NET projects up to date is simple in general, but when it comes to details of huge (and old) projects, it can be challenging.

In this case, I created a .NET project with MySQL DB about 10 years ago, using mainly DataSets. They were quite easy to handle with VS GUI tools.

Nowadays, MySQL VS and Connector Add-Ins seem buggy and error-prone. DataSets are not recommended any more at all, MS came up with platform-independent .NET Core and .NET Standard, and the EntityFramework scaffolding works quite well in .NET Core. So basically, it would be a good idea to migrate DataSets to Core/Standard DB entity scaffolding.

Situation

The main problem: DataSet/TableAdapter methods, commands and properties must be replaced manually because it’s a completely different approach to access data. In large projects, this can take weeks or even months. So I was looking for a way to be able to use both options in parallel within the same VS solution, for a simpler and smoother transition which does not block other project feature developments.

Basically, we will add a .NET Standard project to our existing .NET Framework project. The new one will contain the DB classes generated by .NET Core scaffolding. Running it in parallel with DataSets will be a bit tricky.

Steps

First, install the latest version of Visual Studio and .NET Framework and Core SDKs.

Make sure your existing .NET Framework project(s) version is at least 4.6.1 to be compatible with Standard and Core.

Add a new .NET Core class library project to your solution, we will call it “DB”. Edit DB.csproj, change TargetFramework to “netstandard2.0” to be make it most compatible, then restart VS.

Install packages via NuGet for DB:

  • MySql.Data.EntityFrameworkCore
  • MySql.Data.EntityFrameworkCore.Design
  • (NETStandard.Library)

Create an empty project folder “Models”.

Because we created a library, to run the scaffolding .NET Core also needs an application project (see notes below for details).

Add a new .NET Core console application named “DbBuildTmp” and install NugetPackes

  • MySql.Data.EntityFrameworkCore.Design
  • (Microsoft.NETCore.App)

In DbBuildTmp, reference project DB.

Open developer command prompt and navigate to the DB folder.

Run the scaffolding command to generate classes of your existing database:

dotnet ef dbcontext scaffold "server=myserver;port=3306;user=myuser;password=mypw;database=mydb" MySql.Data.EntityFrameworkCore -o Models -f --startup-project ..\DbBuildTmp

You should now have one C# class file per DB table within the Models folder, and an additional mydbContext.cs file.

Accessibility from .NET Framework project

To be able to simply access the new EF class structure from your existing project, e.g. via “new mydbContext().mytable.First().id”, we need a few more steps.

Important: Ensure your previously installed MySQL Connector (and therefore MySql.Data reference) version matches the NuGet MySql.* package versions exactly (e.g. 8.0.13)!

Add a reference to your new DB project.

Add in both existingProject.csproj and DB.csproj (within <PropertyGroup> element):

<AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>
<GenerateBindingRedirectsOutputType>true</GenerateBindingRedirectsOutputType>
<CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>

With these properties set, the compiler seems to solve versioning issues better itself, and also copies the .NET Standard/Core output with (most, not all?) needed references.

For your existing Framework project, install NuGet package

  • Microsoft.EntityFrameworkCore
  • and all suggested dependencies.

Note: the packages’ version must match the version which is referenced to by MySql.Data.Entity* packages in the DB project exactly!

Now you should be able to get some test data e.g. via your original Program.cs:

var testdb = new DB.Models.myContext();
Console.WriteLine(testdb.myTable.Find("myId").SomeColValue);

If versioning conflicts occur, empty your projects’ bin/obj folders and double-check the (sub)-references of all your NuGet packages compared to non-Nuget references like maybe MySql.data.

One error which also might occur is “unknown: providers” – on runtime, when accessing the DbSet property the first time. Have a look at existingProject.csproj. The EntityFramework element might contain a “providers” tag which is useless for MySQL; delete the full providers tag and recompile.

Using original connection string

The class generator sets the connection data within the mydbContext’s “OnConfiguring” method, which is fine for testing, but we want to use the same connection string for DataSet and EF entity access, set in the Framework project’s Properties.Settings file.

At the moment, there seems to be no way to prevent the CLI scaffolding command from generating this method.

In my opinion, the least inconvenient way is to create a separate “partial class” which stays out of the Models folder, but uses the same namespace. So you can simply move this method out from the mydbContext file to the a place where it cannot be overwritten. Re-Running DB scaffolding will recreate the method but at least you get a compiler error message (“duplicate”) as a reminder to delete it again.

In Framework project’s Program.cs file, you can set e.g. some static variable like

DB.MyStaticCommonFile.connstr = Properties.Settings.Default.OriginalConnStr;

Then modify DB project’s DbContext OnConfiguring method like

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseMySQL(MyStaticCommonFile.connstr);
    }
}

 

Some notes

Usually, it should also be possible to use the existing Framework 4.x project as “startup project” for DB scaffolding. In fact, this might end with an MSBuild error “MSB4006 / GetEFProjectMetadata”. So we have to use the additional .NET core console application project until this is fixed.

Usually a good alternative to the original MySQL EF packages for Core is Pomelo, it was way less buggy than the original packages months ago. Anyway, we have to use original MySQL EF here to be compatible with the existing MySql.Data in the Framework project. Pomelo contains a dependency to MySqlClient which causes a huge load of version and naming conflicts if included.

 

References

https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db

https://stackoverflow.com/questions/43837638/how-to-get-net-core-projects-to-copy-nuget-references-to-build-output/43841481

 

 

 [:]

Submit a Comment