Developer Center
 


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 hundreds of 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<MySQLContext>(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:

    Formatter [ "Title" ] failed in the evaluation of @page @model WebAppMySQL.Pages.Customer.IndexModel @{ ViewData\\["Title"] = "Index"; }

    Index

    Create New

    � � @foreach (var item in Model.Customer) { � � }
    Edit | Delete
    . The error was: Attempt to reference the value of an attribute using an invalid name "Title"