Reverse engineer a database with AspNetCore in Visual Studio

For some reason the .NETCore designers did not think, that developers want to follow best practices by separating the data layer from the presentation layer.

The Entity framework out of the box only works if the database is accessed from the main application project.

When we try to reverse engineer a PostgreSQL database from a class library with the command:

cd MY_CLASS_LIBRARY_DIRECTORY
dotnet ef dbcontext scaffold "Host=localhost;Database=MY_DATABASE_NAME;Username=MY_USERNAME;Password=MY_PASSWORD" Npgsql.EntityFrameworkCore.PostgreSQL

we get the error message:

The specified framework version ‘2.1’ could not be parsed
The specified framework ‘Microsoft.NETCore.App’, version ‘2.1’ was not found.
– Check application dependencies and target a framework version installed at:
/usr/local/share/dotnet/
– Installing .NET Core prerequisites might help resolve this problem:
http://go.microsoft.com/fwlink/?LinkID=798306&clcid=0x409
– The .NET Core framework and SDK can be installed from:
https://aka.ms/dotnet-download
– The following versions are installed:
2.0.0 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
2.1.2 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
2.1.3 at [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

The problem is, that the …runtimeconfig.json files are only automatically generated in the bin/netcoreapp2.1 directory of the main application project, those are missing from all class libraries.

To enable the automatic generation of the …runtimeconfig.json files, add a line to the <PropertyGroup> section of the data layer class library project (.csproj) file.

<PropertyGroup>
...
<GenerateRuntimeConfigurationFiles>True</GenerateRuntimeConfigurationFiles>
...
</PropertyGroup>

 

Procedure or function ‘`…_Insert`’ cannot be found in database …

When you use the Microsoft .NET Entity framework to access a MySql database, the autogenerated code throws an error when you try to insert a row into the database with

context.MY_TABLE.Add(MY_OBJECT);

Server Error in ‘/’ Application.
Procedure or function ‘`…_Insert`’ cannot be found in database ‘`…`’.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Procedure or function ‘`…_Insert`’ cannot be found in database ‘`…`’.

The auto-generated class tries to use stored procedures to update the database. To use the Entity Framework Linq commands, comment out the line that contains “MapToStoredProcedures”

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
     base.OnModelCreating(modelBuilder);
     // modelBuilder.Entity<twitter_keywords>().MapToStoredProcedures();
 }

 

 

No connection string named ‘…’ could be found in the application config file.

Two-tier applications separate the presentation layer and the data layer, and all database access related objects are located in the data-tier.

When the application runs, the config file of the main project is read. In web applications, it is the web.config, in console applications the app.config file.

When you get the following runtime exception

Exception thrown: ‘System.InvalidOperationException’ in EntityFramework.dll
An exception of type ‘System.InvalidOperationException’ occurred in EntityFramework.dll but was not handled in user code
No connection string named ‘…Entities’ could be found in the application config file.

 

  1. Copy the connection string from the app.config file of the data-tier to the main project’s config file.
 <add name="USC_SocialMediaMSSQLEntities" connectionString="metadata=res://*/USC_SocialMedia_MSSQL.csdl|res://*/USC_SocialMedia_MSSQL.ssdl|res://*/USC_SocialMedia_MSSQL.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MY_SERVER;initial catalog=MY_DATABASE;user id=MY_USER;password=MY_PASWORD;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

2. This is a sample data access class in the data-layer to read data from the aspNetRoles table.

using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SocialMedia_Data {
	public class SocialMediaData {

		private USC_SocialMediaMSSQLEntities _web;

		public SocialMediaData() {

			// Set up the database connections
			_web = new USC_SocialMediaMSSQLEntities();

		}

		/// Returns the list of Roles
		public List GetRoles() {

			IEnumerable foundRoles = from aspNetRoles in _web.AspNetRoles
						 orderby aspNetRoles.DisplayOrder
						 select aspNetRoles;

			if (null == foundRoles || 0 == foundRoles.Count()) {
				return null;
			}

			return foundRoles.ToList();

		}
	}
}

The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

When I made changes to the web.config file of an ASP.Net C# application, I have accidentally deleted a comma, and I started to get the runtime error message:

The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

Make sure all necessary commas are there in the config files, Visual Studio does not check the format of those lines. In my case, the error was in

<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.10.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

Generate Entity Framework Entity Data Model in Visual Studio 2017

Visual Studio 2017 has many project type options, and not all of them support the Entity Framework user interface to generate the Entity Framework Data Model (EDMX) file. If you create an ASP.NET Web Application(.NET Framework) you can add the EDMX file to it. The .NET Core projects do not support the Entity Framework Data Model creation.

For more complex web applications it is good practice to separate the data access layer from the presentation layer. You can even reference the data access project from another solution if that also needs access to the same data.

To be able to use the graphical user interface to generate the Entity Framework Entity Data Model

  1. Add a new Class Library (.NET Framework) type project to the solution,
  2. Right-click the new project and select Add -> New Item
  3. In the Data section select the ADO.NET Entity Data Model
  4. Select EF Designer from database
  5. Continue the process to create the database connection and generate the data model.

Generate the Entity Classes

  1. Open the EDMX file
  2. Right-click the background and select Add Code Generation Item
  3. Select the latest EF DbContext Generator
  4. When the classes have been generated, close the solution and open it again, otherwise, the newly created classes cannot be referenced.

{“error”:”Entity type ‘AspNetUserRoles’ has composite primary key defined with data annotations. To set composite primary key, use fluent API.”}

When a database table has composite keys (multiple columns are in the key) you cannot use the usual key definition

[Key]
[Display(Name = “UserId”)]
public string UserId { get; set; }

[Key]
[Display(Name = “RoleId”)]
public string RoleId { get; set; }

When you run the application you get the error message:

{“error”:”Entity type ‘AspNetUserRoles’ has composite primary key defined with data annotations. To set composite primary key, use fluent API.”}

To declare composite keys, in the fluent API format

In the model declare the keys

public class AspNetUserRoles {

    // Composite primary keys
    // Initialized in ApplicationDbContext.OnModelCreating()
    public object UserId { get; internal set; }
    public object RoleId { get; internal set; }
    ...

In the ApplicationDbContext file add the lines to the OnModelCreating(ModelBuilder builder) method

// Composite primary key
builder.Entity().HasKey(table => new {
   table.UserId,
   table.RoleId
});

 

 

Generate SQL script from Entity Framework migration script

It is not recommended to execute Entity Framework migration scripts in production, It is important that you execute all SQL steps manually on the production database section-by-section to immediately see the result and be able to recover in case a destructive action goes wrong.

The Visual Studio .NET application templates contain Entity Framework migration scripts to set up your local database to match the sample code. To generate the SQL script you can execute by hand on the production database

Install the EF Core .NET Command-line Tools

  1. Open a command window in the folder of the project that contains the database access code
  2. In the command line execute
    dotnet add package Microsoft.EntityFrameworkCore.Design
    dotnet restore
  3. Test the dotnet ef installation with
    dotnet ef

 

Script the .NET Entity Framework migrations

  1. Open a command window in the folder of the project that contains the database access code
  2. In the command line execute
    1. To generate the SQL script to bring the current development database to the scripted configuration state, use the –idempotent option.
      (Generating idempotent scripts for migration is not currently supported by MySql)

      dotnet ef migrations script --idempotent --output "script.sql" --context MY_DBCONTEXT
    2. To generate the SQL script based on the entire migration script to update the production database to match the template after you have updated the development database
      dotnet ef migrations script --output "script.sql" --context MY_DBCONTEXT

 

 

DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded.

The .NET Core Entity framework makes database access easy. When you auto-generate a Razor page in a .NET web application to edit a data row, the scaffolding places controls on the page for every column.

There are columns in most of the tables that we don’t want to display or edit, like keys, checksums, password hashes. If column values are missing during database update, the error is thrown:

A database operation failed while processing the request.
DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

When the edit page is displayed, all values of the row are passed to the page in the Model. When the user clicks the Save button the model is sent back to the server, and the OnPostAsync() method saves the values to the database. The values that we don’t display or store otherwise on the web page are not retained in the browser, and not passed back to the server. To keep the not displayed values, add hidden attributes to the page. The primary key of the row is automatically added, and based on that create hidden attributes for the rest of the not displayed columns.

 <form method="post">
 <div asp-validation-summary="ModelOnly" class="text-danger"></div>
 <input type="hidden" asp-for="ApplicationUser.Id" />
 <input type="hidden" asp-for="ApplicationUser.AccessFailedCount" />
 <input type="hidden" asp-for="ApplicationUser.ConcurrencyStamp" />
 <input type="hidden" asp-for="ApplicationUser.NormalizedEmail" />
 <input type="hidden" asp-for="ApplicationUser.NormalizedUserName" />
 <input type="hidden" asp-for="ApplicationUser.PasswordHash" />
 <input type="hidden" asp-for="ApplicationUser.SecurityStamp" />
 <input type="hidden" asp-for="ApplicationUser.UserName" />

 

 

 

No mapping specified for instances of the EntitySet and AssociationSet in the EntityContainer

When you move your Visual Studio solution to another workstation you may encounter the following error message:

System.Data.Entity.Core.MappingException was unhandled by user code
HResult=-2146232032
Message=Schema specified is not valid. Errors:
: error 2062: No mapping specified for instances of the EntitySet and AssociationSet in the EntityContainer …
Source=EntityFramework

 

In this case it may help if you re-import the database objects into the .edmx file.

To import all objects again:

  • Open the .edmx file,
  • Press the Ctrl-A keys to select all tables in the diagram,
  • Press the delete key on the keyboard to delete all tables,
  • Right click the white area and select Model Browser,
  • In the Model Browser expand the {model name}.Store, and the Stored Procedures / Functions element
  • Delete all functions and stored procedures
  • In the {model name} element (the element above the {model name}.Store) expand the Function Imports element
  • Delete all function imports
  • Save the .edmx file
  • Right click the white area of the .edmx file and select Update Model from Database
  • Select all necessary tables, stored procedures, and functions and import them.

If you re-import the tables, but do not delete the stored procedures, functions, and function imports you may get the following error message:

HResult=-2146233079
Message=The function import ‘Tobacco_WebEntities.spGetAgeGroupId’ cannot be executed because it is not mapped to a store function.
Source=EntityFramework

 

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’

When one of the projects in your solution is referencing the Entity Framework to access a database the Entity Framework NuGet package is added to that project. In the Visual Studio IDE your application may work fine, but when you deploy it to the test or production server the following error message may appear:

Exception Type: System.Data.Entity.Core.MetadataException
Exception: Schema specified is not valid.
Errors: [YOUR_DATABASE].ssdl(2,2) : error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

To resolve the problem add the Entity Framework NuGet package to the main project of your application:

  • In the Solution Explorer right click the main project,
  • Select Manage NuGet Packages… in the context menu,
  • On the left side of the NuGet manager select Online, Microsoft and .NET,
  • On the right side enter EntityFramework into the search field and hit Enter,
  • In the middle click Install in the EntityFramework box.