Connecting C# to MySQL and MariaDB with Entity Framework Core

Why dotConnect for MySQL?

dotConnect for MySQL is the data provider that suits ideally for all MySQL and MariaDB-related operations. Its numerous features enhance functionality, performance, and ease of development for .NET developers working with MySQL databases.

Prerequisites

Before starting, ensure you have the following prerequisites installed:

• Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
• dotConnect for MySQL: A high-performance ADO.NET data provider for MySQL with enhanced ORM support and database connectivity features.
• Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation (more on this later).
• MySQL test database: A sample database provided by MySQL for learning and testing.

The Benefits of Using EF Core With MySQL

Entity Framework Core simplifies data access and manipulation tasks thanks to its versatility, enhancing productivity and maintainability in .NET applications. It is important to understand how ADO.NET and EF Core interacts with MySQL databases, including versions 6, 7 or 8, especially for developers aiming to leverage the capabilities of both technologies effectively.

1. Developers define their application's conceptual model by creating an Entity Data Model. This model represents the structure of the data entities and their relationships within the application domain.

2. EF generates a mapping layer that establishes the correspondence between the conceptual model defined in the EDM and the physical schema of the underlying database. This mapping layer ensures seamless communication between the application and the database, abstracting away the intricacies of relational database management.

3. Developers can construct LINQ or Entity SQL queries to retrieve data from the MySQL database. EF translates these queries into SQL statements, optimizing them for execution against the MySQL database server.

4. EF provides a robust set of APIs for performing CRUD operations on data entities. Developers can interact with MySQL databases using familiar C# syntax, abstracting away the complexities of SQL commands.

5. EF automatically tracks changes made to entity objects within the application's context. This change-tracking mechanism facilitates efficient updates to the database, ensuring data consistency and integrity.

Key features of Entity Framework Core 8

Entity Framework Core continues to evolve with each new version, introducing enhancements and features aimed at improving developer productivity, performance, and flexibility.

• Performance Enhancement
• Enhanced LINQ Support
• Schema Evolution and Migration
• Concurrency Control
• Security Enhancements
• Tooling Improvements
• Cross-Platform and Containerization
• Performance Monitoring and Diagnostics

Download and activate dotConnect for MySQL

Visit the dotConnect for MySQL download page. You can either get it for a free trial or purchase a full version at once. The free trial gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require a license key for activation.

If you're ready to purchase the full version, select Buy Now to add it to your cart.

Download and install the trial version

For the trial version, simply download and install dotConnect for MySQL on your machine. No license key is required for the trial, and you can start exploring the product immediately.

Purchase and access the full version

After purchasing the full version, go to your profile's Licenses page. You'll find a list of your licenses. Find dotConnect for MySQL and click Details. Here, you'll find your license details, including the activation key.

Download the full version

Click Download Product Versions to access the list of available versions. Select the most recent version and download the installer.

Run the downloaded file and follow the on-screen instructions to install dotConnect for MySQL on your machine. This process will install the software and activate all features associated with your license key.

Install packages

Please ensure compatibility with projects targeting MySQL databases.

1. Launch Visual Studio, ensuring that you have administrative privileges if necessary.

2. Create a new project or open an existing project in Visual Studio that you intend to integrate with MySQL using EF Core.

3. Right-click on the project in the Solution Explorer, then select Manage NuGet Packages.

4. Install the MySQL provider for EF Core by searching Devart.Data.MySql.EFCore package.

5. Check that the connection string includes the necessary information, such as server address, port, database name, username, and password.

6. Build the project to ensure that EF Core and the MySQL provider are installed successfully without any errors.

Optionally, test the connection to the MySQL database from your .NET application to verify that EF Core can establish a connection and communicate with the database.

Generating Model from MySQL Database

Generating models from an existing MySQL database into EF Core models sets consistency between your application's data structures and the database schema. Here's how to perform this reverse process:

1. Ensure that you have the EF Core Tools installed in your development environment. You can install them globally using the .NET CLI by running the command:

dotnet tool install --global dotnet-ef

2. Open the command-line interface or terminal and navigate to the project directory containing your .NET application. Then, execute the command below followed by the connection string and the provider name.

dotnet ef dbcontext scaffold

In addition to the standard method via dotnet-ef, there is another popular option, such as scaffold, which allows for quick and efficient creation of models from databases.

Installing Scaffold-DbContext via Package Manager:

Firstly, you can install the Scaffold-DbContext command via the Package Manager Console in Visual Studio. To do this, follow these steps:

• Open Visual Studio.
• Navigate to Tools > NuGet Package Manager > Package Manager Console.
• In the Package Manager Console, execute the following command to install Scaffold-DbContext:

Install-Package Microsoft.EntityFrameworkCore.Tools

This command installs all the necessary tools, including Scaffold-DbContext, to generate a DbContext and entity classes based on an existing database schema.

Once you've installed the necessary tools, you can use Scaffold-DbContext to generate a DbContext and entity classes for your MySQL database. Here's how to do it:

• Open the Package Manager Console again.
• Run the following command, replacing the connection string and provider with your MySQL connection details:

Scaffold-DbContext "Server=127.0.0.1;Port=3306;UserId=TestUser;Password=TestPassword;Database=Test;" 
Devart.Data.MySql.Entity.EFCore

This command instructs Entity Framework Core to scaffold the DbContext and entity classes based on the schema of the MySQL database specified in the connection string.

3. After executing the command, EF Core generates entity classes corresponding to the tables in your MySQL database. Review the generated models to ensure accuracy and make any necessary adjustments or customizations.

4. EF Core also generates a DbContext class that represents the database context for your application. This class includes DbSet properties for each entity, allowing you to query and manipulate data using LINQ queries.

5. Organize the generated models and DbContext class within your project's namespace and folder structure for better maintainability and organization.

6. If necessary, update the generated DbContext class to include additional configurations, such as specifying table names, column mappings, or relationships between entities.

While Entity Framework Core provides the capability to automatically create database models from existing schemas using command-line tools, you can utilize Entity Developer, a powerful ORM design tool offered by Devart. Entity Developer allows you to create data models in a visual editor, simplifying the development process and enabling you to define relationships between entities, attributes, and other parameters. After creating the model, you can generate source code for your Entity Framework Core project, including data context, entity classes (with multiple customizations like support of multiple validation and serialization libraries), Data Transfer Object classes, repositories, and so on. Utilizing Entity Developer can be convenient in cases where you need to quickly and efficiently create complex data models or when you prefer to have more control over the ORM design process.

Querying MySQL Data

Querying data from a MySQL database in .NET applications using Entity Framework Core involves utilizing LINQ or executing raw SQL queries. Here's a detailed description of how to perform data querying with both approaches:

Using LINQ Queries:

1. First, ensure that you have defined a DbContext class that represents your database context and includes DbSet properties for the entities you want to query. This DbContext class should be configured to connect to your MySQL database.

2. Use LINQ to construct your query. LINQ provides a fluent syntax for querying data in a strongly typed manner. You can use LINQ methods such as Where, OrderBy, Select, Include, and others to filter, sort, project, and include related entities in your query.

3. Once you have constructed your LINQ query, execute it by calling methods such as ToList, FirstOrDefault, SingleOrDefault, Count, or Any. These methods will translate your LINQ query into SQL and execute it against the MySQL database, returning the result as objects or scalar values.

Example LINQ Query:

using (var context = new MyDbContext())
{
var query = from entity in context.MyEntities
where entity.SomeProperty == "Value"
select entity;

var result = query.ToList();
}

Executing Raw SQL Queries:

1. Alternatively, you can execute raw SQL queries against your MySQL database using Entity Framework Core's FromSqlRaw or FromSqlInterpolated methods. Construct your SQL query as a string, including any parameters or placeholders, as needed.

2. Call the FromSqlRaw or FromSqlInterpolated method on your DbSet with the SQL query string as an argument. Optionally, provide any parameters or interpolations required by the query.

3. Process the SQL query results as needed. Entity Framework Core will execute the SQL query against the MySQL database and materialize the results into objects or scalar values.

Example Raw SQL Query:

using (var context = new MyDbContext())
{
  var query = context.MyEntities.FromSqlRaw("SELECT * FROM
  MyEntities WHERE SomeProperty = {0}", "Value");

  var result = query.ToList();
}

Inserting New Data into MySQL

Here is how you can efficiently insert new data into a MySQL database using Entity Framework Core in your .NET application:

1. Ensure that you have defined a DbContext class that represents your database context and includes DbSet properties for the entities you want to work with. This DbContext class should be configured to connect to your MySQL database.

2. Instantiate a new instance of the entity class you want to insert data into. Set the properties of the entity instance with the values you want to insert into the database. Add the entity instance to the appropriate DbSet property of your DbContext using the Add method.

3. Call the SaveChanges method on your DbContext instance to persist the changes to the database. Entity Framework Core will generate and execute the necessary SQL INSERT statement against the MySQL database.

Updating MySQL Data

To update MySQL data using Entity Framework Core in a .NET application, follow these steps:

1. Query the database to retrieve the entity you want to update. You can use methods like Find, FirstOrDefault, or SingleOrDefault to retrieve the entity based on its primary key or other criteria.

2. Modify the properties of the retrieved entity with the new values you want to update.

3. Invoke the SaveChanges method on your DbContext instance to store the modifications in the database. Entity Framework Core will automatically generate the essential SQL UPDATE statement and execute it on the MySQL database.

Here's an example of how to update MySQL data using Entity Framework Core:

using (var context = new MyDbContext())
{
 // Retrieve the entity you want to update
 var entityToUpdate = context.MyEntities.Find(id);
 if (entityToUpdate != null)
 {
 // Modify the properties of the entity
 entityToUpdate.Property1 = "NewValue1";
 entityToUpdate.Property2 = "NewValue2";

 // Set other properties as needed
 // Save changes to persist the updated data to the database
 context.SaveChanges();
 }
}

In this example, MyDbContext is your DbContext class, MyEntities is the DbSet representing the table in your MySQL database, and id is the primary key of the entity you want to update. Replace "NewValue1", "NewValue2", and other property values with the new data you want to update.

Deleting Data from MySQL

Deleting data from a MySQL database using Entity Framework Core involves several easy steps:

• Query the database context to retrieve the entity you want to delete. Use methods such as Find, FirstOrDefault, or LINQ queries to retrieve the entity based on specific criteria.
• Once the entity is retrieved, remove it from the database context using the Remove method. This marks the entity for deletion from the database upon calling the SaveChanges method.
• Call the SaveChanges method on the database context to persist the deletion to the MySQL database. This triggers the removal of the entity's corresponding record from the database table.

Best Practices for Deploying an Entity Framework Core Project

Deploying applications that use Entity Framework Core with MySQL requires careful consideration of version-specific nuances. Here are some best practices for deploying such applications:

Version Compatibility

Ensure that the versions of Entity Framework Core, MySQL server, and MySQL provider are compatible with each other. Check the documentation of Entity Framework Core and the MySQL provider to identify any version-specific requirements or compatibility issues.

Database Migrations

Use EF Core's migration feature to manage database schema changes across different environments. Before deploying your application, ensure that all pending migrations are applied to the target MySQL database. You can use tools like dotnet ef migrations to generate and apply migrations as needed.

Connection String Configuration

Use environment-specific configuration files or environment variables to manage connection strings. Avoid hardcoding connection strings in your application code to ensure flexibility and security. Consider using encrypted configuration providers or secrets management solutions to protect sensitive connection information.

Connection Pooling

Configure connection pooling settings appropriately to optimize database connection management. Adjust settings such as connection pool size, connection timeout, and connection lifetime based on the deployment environment's requirements and workload characteristics.

Database Performance Optimization

Optimize database performance by analyzing query execution plans, indexing strategies, and database configuration settings. Monitor database performance metrics regularly and fine-tune configurations as needed to ensure optimal performance.

Error Handling and Logging

In addition to the above, perform robust error handling and logging mechanisms to capture and diagnose issues that may occur during application deployment and runtime. Log relevant information such as database connection errors, query execution failures, and application exceptions to facilitate troubleshooting and debugging.

Security Considerations

Implement security best practices to protect sensitive data and prevent unauthorized access to the MySQL database. Secure database connections using SSL/TLS encryption and configure user authentication and access control policies appropriately. Regularly update and patch MySQL server and client libraries to address security vulnerabilities.

Testing and Validation

Thoroughly test and validate the deployed application to ensure that it behaves as expected in the target environment. Conduct integration tests, performance tests, and user acceptance tests to verify functionality, performance, and reliability across different deployment scenarios.

Backup and Disaster Recovery

Implement backup and disaster recovery measures to safeguard critical data and ensure business continuity. Regularly back up the MySQL database and store backups in secure offsite locations. Establish procedures for restoring backups in the event of data loss or database corruption.

Conclusion

Integrating MySQL with Entity Framework and Entity Framework Core allows for efficient data access and manipulation in .NET applications. By following the steps outlined in this guide, you can seamlessly connect your .NET projects to MySQL databases, enabling powerful database interactions while leveraging the capabilities of EF and EF Core. For more advanced usage scenarios and optimization techniques, refer to the official documentation of dotConnect for MySQL, Entity Framework, and Entity Framework Core. Additionally, we advise exploring community resources and tutorials for further insights into optimizing performance and handling complex database interactions.