MySQL ADO.NET Developer Guide - Entity Framework ASP.NET App

This article provides a walkthrough for using Entity Framework (EF) Core in an ASP.NET Core Razor Pages app. With the help of the CData ADO.NET Provider for MySQL, this application will create pages to view, add, delete, and update data from MySQL

NOTE: While this article refers to MySQL data, the principles can be applied to any of the 270+ data sources CData supports.

Prerequisites

This guide requires the following parameters:

Guide

  1. Create the application
    1. Open Visual Studio and create a new “ASP.NET Core Web App (Razor Pages)” project.
    2. Name your project “WebAppMySQL”, so that the namespaces will match if you copy and paste code from this guide, and then select “Next”.
    3. On the next page, select “.NET 8.0 (Long-term support)” and then click “Create”.
  2. Install Entity Framework and add references to the required assemblies
    1. Use the NuGet Package Manager to add the packages listed below to your project:
      1. Microsoft.EntityFrameworkCore
      2. Microsoft.EntityFrameworkCore.Design
      3. Microsoft.EntityFrameworkCore.SqlServer
      4. Microsoft.EntityFrameworkCore.Tools
    2. Add a reference to System.Data.CData.MySQL.dll, located in the lib -> net8.0 subfolder in the installation directory (ex. "C:\Program Files\CData\CData ADO.NET Provider for MySQL 2024\").
    3. Add a reference to CData.EntityFrameworkCore.MySQL.dll, located in the lib -> net8.0 -> EFCORE80 subfolder in the installation directory.
    4. Add the included licensing file (.lic) to your project. You can do this by right-clicking the project in the Solution Explorer and then navigating to Add -> Existing Item -> System.Data.CData.MySQL.lic (located in the lib -> netstandard2.0 subfolder in your installation directory). Finally, right-click the licensing file, and set the Copy to Output Directory property to Copy if newer.
    5. Build the project to complete the setup for using EF Core.
  3. Reverse Engineering (Scaffolding) the data model

    Scaffolding is performed using the Package Manager Console (PMC), so open the PMC and use the following command to scaffold the schema into your Models folder. This command automatically constructs classes for all tables/views available. It also creates a context class that extends DbContext and exposes the DbSet properties that represent the tables in the data source:

    Scaffold-DbContext "Authscheme=Password;Server=YourServer;Port=YourPort;User=YourUser;Password=YourPassword;Database=YourDatabase;Tables=customers;" CData.EntityFrameworkCore.MySQL -OutputDir Models -Context MySQLContext

    Note: We set the Tables property to restrict the number of tables we connect to and model. This will only model the Customer table from the MySQL connection instead of every available table and view.

    Read more about the CData MySQL ADO.NET Provider connection properties on the CData Documentation page: CData ADO.NET Provider for MySQL Documentation

  4. Add a Razor Page for the Customers
    1. Create a new "Customers" subfolder in the “Pages” folder of your project.
    2. In the Solution Explorer, right-click the new “Customers” folder and select “Add” > “Razor Page…”.
    3. In the Razor Page dialog, select “Razor Page using Entity Framework (CRUD)” and click "Add".
    4. In the “Razor Page using Entity Framework (CRUD)” dialog:
      1. Set “Model class” to the Customer entity, "Customer"
      2. Set “DbContext class” to the database context, “MySQLContext”.
    5. Click “Add” to create the new pages. This will create Razor pages in the Pages/Customers folder:
  5. Add Connection String in the appsettings.json file

    In the appsettings.json file, you will need to add the connection string for the MySQL connection. Below is an example of the code:

    { "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "AllowedHosts": "*", "ConnectionStrings": {"MySQLContext": "Authscheme=Password;Server=YourServer;Port=YourPort;User=YourUser;Password=YourPassword;Database=YourDatabase;Tables=customers;" } }
  6. Register the context in Program.cs

    In the Program.cs file, you will need to register the context class with the dependency injection container. Below is an example of the code:

    using WebAppMySQL.Models; using Microsoft.EntityFrameworkCore; var builder = WebApplication.CreateBuilder(args); // Add services to the container. builder.Services.AddRazorPages(); builder.Services.AddDbContext(options => options.UseMySQL(builder.Configuration.GetConnectionString("MySQLContext")));
  7. Adjust the layout of the Customers list

    For ease of use, we will adjust the layout of the Customers page so that the controls to Edit or Delete a Customer are easily accessible. By default, the column with these controls is listed last, so we will adjust the code in the Index.cshtml file to make this column the first one displayed. In this code, we added an empty header for the column and moved the Edit and Delete controls to the first column listed. Below is an example of the adjustments made:

    @page @model WebAppMySQL.Pages.Customer.IndexModel @{ ViewData["Title"] = "Index"; }

    Index

    Create New

    … … @foreach (var item in Model.Customer) { … … }
    Edit | Delete
  8. Add the Razor page to your layout

    With the Razor page fully set up, we can remove the generic pages that were available when the project was created and replace them with a link to the new page in the _Layout.cshtml file. To do this, delete the current code and paste the following code into the Pages/Shared/_Layout.cshtml file. Below is an example of the adjustments made:

    <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>@ViewData["Title"] - WebAppMySQL</title> <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" /> <link rel="stylesheet" href="~/css/site.css" asp-append-version="true" /> <link rel="stylesheet" href="~/WebAppMySQL.styles.css" asp-append-version="true" /> </head> <body> <header> <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3"> <div class="container"> <a class="navbar-brand" asp-area="" asp-page="/Index">WebAppMySQL</a> <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target=".navbar-collapse" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="navbar-collapse collapse d-sm-inline-flex justify-content-between"> <ul class="navbar-nav flex-grow-1"> <li class="nav-item"> <a class="nav-link text-dark" asp-area="" asp-page="/Index">Home</a> </li> <li class="nav-item"> <a class="nav-link text-dark" asp-area="" asp-page="/Customers/Index">Customers</a> </li> </ul> </div> </div> </nav> </header> <div class="container"> <main role="main" class="pb-3"> @RenderBody() </main> </div> <footer class="border-top footer text-muted"> <div class="container"> &copy; 2025 - WebAppMySQL - <a asp-area="" asp-page="/Privacy">Privacy</a> </div> </footer> <script src="~/lib/jquery/dist/jquery.min.js"></script> <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script> <script src="~/js/site.js" asp-append-version="true"></script> @await RenderSectionAsync("Scripts", required: false) </body> </html>
  9. Run the application

    With everything set up, you can now run the completed MySQL-connected application. To do this, first, build the solution then use the green play button to start the application. This should open the home screen for the application, and you can click the “Customers” tab to view your list of Customers, as shown below:

    On this same page, select the Create New button to open the page to create a new Customer in your MySQL table:

    Additionally, on the same page, select the Edit or Delete buttons next to a Customer to open the pages that allow you to edit or delete the Customer selected:

Unlock seamless data connectivity in your .NET applications

Are you looking to unlock the full potential of your MySQL data? The CData MySQL ADO.NET Provider can seamlessly integrate your .NET applications with your MySQL data. With the CData ADO.NET Provider for MySQL Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building MySQL-connected apps today!