API to modify the machine.config file - 'DbProviderFactories' section can only appear once per config file

Question

I've recently encountered the following error on a client machine:

The 'DbProviderFactories' section can only appear once per config file.

It appears that the machine config contains a duplicate DbProviderFactories element.

<system.data>
    <DbProviderFactories>
        <add name="IBM DB2 for i .NET Provider" invariant="IBM.Data.DB2.iSeries" description=".NET Framework Data Provider for IBM i" type="IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26" />
    </DbProviderFactories>
    <DbProviderFactories />
</system.data>

Manually removing this extra element fixes the problem, and our software can run. However, it has been requested that we try and work around this by perhaps ignoring the duplicate entry inside our own app.config. This is because many clients might have the same issue, and we can't modify everyone's config file.

I've tried adding a <clear/> element inside the system.data section, to hopefully override what's there already in the machine.config. However, this does not work.

For example

<system.data>
    <clear />
    <DbProviderFactories>
      <add name="Microsoft SQL Server Compact Data Provider 4.0" 
           invariant="System.Data.SqlServerCe.4.0" 
           description=".NET Framework Data Provider for Microsoft SQL Server Compact" 
           type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

Is there a way to programmatically ignore the duplicate DbProviderFactories element?

Does an API exist to allow you to modify the machine config?

Can anyone help, or recommend a solution?

Kind regards

Answer 1

I have recently come across this same problem whilst trying to use SqlServerCe with Entity Framework.

I have managed to work around this issue by using the Code-Based configuration features available in Entity Framework 6.

All you need to do is remove the <entityframeowrk> and <system.data> tags from your app.config and include a class similar to the following in your project:

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServerCompact;
using System.Data.SqlServerCe;

namespace Data
{
    public class DatabaseConfiguration : DbConfiguration
    {
        public DatabaseConfiguration()
        {
            SetExecutionStrategy("System.Data.SqlServerCe.4.0", () => new DefaultExecutionStrategy());
            SetProviderFactory("System.Data.SqlServerCe.4.0", new SqlCeProviderFactory());
            SetProviderServices("System.Data.SqlServerCe.4.0", SqlCeProviderServices.Instance);
        }
    }
}

Entity framework will then automatically pick this up for you and use it. A note to watch out for is that configuration in the app.config will overwrite anything that appears in this class.

Also if you do not want to include this class in your data layer, see the Moving DbConfiguration section here.