I finally found out. I have a website project and my API in my solution. The problem was that the website was set as startup project. So that entity framework searched the connection string in the website's web.config and not in the api's web.config. Changing the startup project to my API solved the problem.

If you want to have the website project as startup project. Just copy the connection string into the websides web.config.

Nevertheless thanks for everyones help.

Also for migrations you can use OnConfiguring EntityFramework will check it by default. Add this code to your DbContext.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(@"connection_string_here");
}

This is a late response, but I determined that the cause of this error for me was that I had tried to eliminate an extra web.config connection string without making a necessary change to my DbContext class.

I had one connection string for my DefaultConnection and another generated by EF Migrations.

<connectionStrings>
    <!-- PRODUCTION-->
    <add name="DefaultConnection" connectionString="…" providerName="System.Data.SqlClient"/>
    <add name="Clients.Link.Models.ClientsLinkDB" connectionString="…" providerName="System.Data.SqlClient"/>
</connectionStrings>

I decided to eliminate the second connection string thinking that EF would fall back or was already using my DefaultConnection.

<connectionStrings>
    <!-- PRODUCTION-->
    <add name="DefaultConnection" connectionString="…" providerName="System.Data.SqlClient"/>
</connectionStrings>

When I attempted to run update-database, I got the error message reported above:

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)

The fix turned out to be extremely simple. I simply needed to instruct EF to use my DefaultConnection connection string instead of the one generated by migrations.

To do that, you just need to create or edit the constructor of your DbContext and pass the name of the DefaultConnection as a string to the base class constructor:

public class ClientsLinkDB : DbContext
{
    public ClientsLinkDB() : base("DefaultConnection")
    {
        
    }

    // The rest of your implementation follows
}