Convert .Net 4.8 EF6 SQL Server webapi project to AWS RDS Aurora MySQL

Question

I developed locally using SQL Server and am attempting to get it working in AWS.

I installed the "AWS Toolkit for VS2019" and published to AWS Elastic Beanstalk.

I modified the code to grab the connection properties as explained in https://docs.aws.amazon.com/elasticbeanstalk/latest/dg/create_deploy_NET.rds.html

I also created the database and populated it with data.

I loaded the Pomelo.EntityFrameworkCore.MySql NuGet package.

When I call a test "Hello World" api function it returns Hello World.

When I attempt to call a function that accesses the database the first error I get is

Keyword Data Source not expected in the connection string.

If I refresh the browser I get this next error

The operation cannot be completed because the DbContext has been disposed.

I'm not sure what the provider and providerName sections should be or how it should be set using AppSettings to determine the connection string.

public static class RDS_Helper
{
    public static string GetRDSConnectionString()
    {
        var appConfig = ConfigurationManager.AppSettings;

        string dbname = appConfig["RDS_DB_NAME"];

        if (string.IsNullOrEmpty(dbname)) return null;

        string username = appConfig["RDS_USERNAME"];
        string password = appConfig["RDS_PASSWORD"];
        string hostname = appConfig["RDS_HOSTNAME"];
        string port = appConfig["RDS_PORT"];
        //return $"data source={hostname}:{port};initial catalog={dbname};persist security info=True;user id={username};password={password};MultipleActiveResultSets=True;App=EntityFramework;";
        return $"metadata=res://*/MyContext.csdl|res://*/MyContext.ssdl|res://*/MyContext.msl;provider=System.Data.SqlClient;provider connection string="data source={hostname}:{port};initial catalog={dbname};persist security info=True;user id={username};password={password};MultipleActiveResultSets=True;App=EntityFramework"";
    }
}

and here's where I'm creating the context:

public static class Conn
{
    private static MyData.MyContext _db;
    public static MyData.MyContext db
    {
        get
        {
            if (_db == null) _db = new MyContext(RDS_Helper.GetRDSConnectionString());
            // providerName="System.Data.EntityClient"
            return _db;
        }
    }
}

Can anyone direct me to a sample project or instructions regarding creation of a .Net project with Entity Framework using an AWS RDS datasource? Or maybe some direction on what to try next? and/or what values should be used for Provider and ProviderName?

Thanks in advance for your assistance.

Answer 1

<sarcasm>
I'm amazed at how many people stepped up to the plate to try to help me with this problem.
</sarcasm>

Here's how to convert a SQL Server Entity Framework project to MySQL -- Don't.

Instead follow these steps:

1. Install MySQL for Visual Studio
2. Create a new .Net Class library project
3. Install NuGet package of "MySql.Data.EntityFramework"
4. Create a new "ADO.NET Entity Data Model" to this project using "MySql Database" as a data-source (which should appear when you create a connection to your database