How to register SQL provider for Entity Framework 6 in runtime?

I'm working on dll that is using Entity Framework 6 (dll is for .Net Framework 4.8) and I'm trying to set SQL provider in runtime because dll has to work alone, without any config file. Reason is that it will be uploaded to BPM tool that will deploy dll when and where it is needed so I would have to modify multiple config files which I would like to avoid.

To make dll work with Entity Framework in this situation I'm using ILMerge to create single dll that contains my dll, EntityFramework.dll and EntityFramework.SqlServer.dll.

I have managed specify connection string in runtime by creating constructor for DbContext, that is calling base method that takes connection string as a parameter and it worked. I have tested from Visual Studio against local DB without issues.

Then I have uploaded dll to BPM tool and called test method which produced following error:

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

I have tried several variations on how to solve this and latest, that yielded some progress is modifying DbConfiguration. It resolved provider issues but introduced new one:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This issues rises 60 second after start so it seems like default timeout for connection.

Code for DbContext and DbConfiguration is following:

public class SqlProviderDbConfiguration : DbConfiguration
{
    public SqlProviderDbConfiguration()
    {
        // Register the provider programmatically
        SetDefaultConnectionFactory(new SqlConnectionFactory());
        SetProviderServices("System.Data.SqlClient", SqlProviderServices.Instance);
    }
}

[DbConfigurationType(typeof(SqlProviderDbConfiguration))]
public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    static BloggingContext()
    {
        // Set the configuration
        DbConfiguration.SetConfiguration(new SqlProviderDbConfiguration());
    }

    // Specify connection string by calling DbContext constructor that takes connection string
    public BloggingContext(string connectionString) : base(connectionString)
    {
    }

    // Default constructor, fails to compile without it, is not used in my code but still runs when EF is working
    public BloggingContext()
    {
    }
}

Beginning of method that is called from BPM or in tests:

public int TestConnection(string blogName, string connectionString)
{
    // update database schema in case it is not on latest version
    Database.SetInitializer(new MigrateDatabaseToLatestVersion<BloggingContext, Configuration>());

    int blogId = -1;
    using (var db = new BloggingContext(connectionString))
    {
    
        var blog = new Blog { Name = blogName };
        db.Blogs.Add(blog);
        db.SaveChanges();

Issues appears on line db.Blogs.Add(blog);

From Visual Studio I'm passing connection string Data Source=(localdb)\mssqllocaldb;Initial Catalog=EmbeddedEntityFrameworkTest.BloggingContext;Integrated Security=True;MultipleActiveResultSets=True which is just copy of default value and it worked before changes in DbConfiguration.

As a sanity check, I have tried to open connection using SqlConnection with same connection string that is passed to DbContext and it opens without any issue:

using(SqlConnection xx =  new SqlConnection(connectionString))
{
    xx.Open();
}

I have tried to find why server is not found but with no luck so far. I will continue in search but if anyone have idea what I'm doing wrong or missing, I would appreciate any help.

Thank you in advance.

Best regards,
Ondrej

I have managed to find working solution. I have limited DbConfiguration only to

> SetProviderServices("System.Data.SqlClient",
> SqlProviderServices.Instance);

and passed SqlConnection with custom connection string to DbContext in constructor.

This solution works from Visual Studio with both inbuild database and SQL server and it is also working from BPM tool with SQL server while only single dll is deployed without config file or modifications to BPM's config. Deployed dll contains my dll, EntityFramework.dll and EntityFramework.SqlServer.dll (merged using ILMerge).

Limitation is obviously specifying SqlConnection so other providers wont work but it might be possible to expand on this concept further.

public class SqlProviderDbConfiguration : DbConfiguration
{
    public SqlProviderDbConfiguration()
    {
        // Register the provider programmatically
        SetProviderServices("System.Data.SqlClient", SqlProviderServices.Instance);
    }
}

[DbConfigurationType(typeof(SqlProviderDbConfiguration))]
public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    static BloggingContext()
    {
        // Set the configuration
        DbConfiguration.SetConfiguration(new SqlProviderDbConfiguration());
    }

    // create my own SQL connection
    public BloggingContext(string connectionString)
    : base(new SqlConnection(connectionString), true)
    {
    }

    public BloggingContext()
    {
    }
}


public int TestConnection(string blogName, string connectionString)
{
    // use DbContext with custom connection string
    using (var db = new BloggingContext(connectionString))
    {
        // work with context as usual