Entity Framework with Postgres results in "database is being accessed by other users"

Question

I have a Winforms .NET C# application in VS 2017, using Entity Framework 6, Postgres as the database, and EntityFramework6.Npgsql as the provider.

Here is my app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="entityFramework" 
                 type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </configSections>
    <connectionStrings>
        <add name="myapp.Properties.Settings.mydbConnectionString" 
             connectionString="Host=localhost;Username=postgres;Password=testdbpass;Database=testdb;Pooling=false;" 
            providerName="Npgsql" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
    <entityFramework>
        <defaultConnectionFactory 
            type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
        <providers>
            <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
        </providers>
    </entityFramework>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-4.0.5.0" newVersion="4.0.5.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

... and here is my code:

   class NpgSqlConfiguration : DbConfiguration
    {
        public NpgSqlConfiguration()
        {
            var name = "Npgsql";
            SetProviderFactory(providerInvariantName: name, providerFactory: NpgsqlFactory.Instance);
            SetProviderServices(providerInvariantName: name, provider: NpgsqlServices.Instance);
            SetDefaultConnectionFactory(connectionFactory: new NpgsqlConnectionFactory());
        }
    }
    [DbConfigurationType(typeof(NpgSqlConfiguration))]
    public class TestContext : DbContext
    {
        public TestContext() : base("myapp.Properties.Settings.mydbConnectionString")
        {
            Database.SetInitializer<TestContext>(new ERPInitializer());
        }
        public DbSet<User> Users { get; set; }
    }

    public class ERPInitializer : DropCreateDatabaseAlways<TestContext>
    //public class ERPInitializer : DropCreateDatabaseIfModelChanges<ERPContext>
    {
        protected override void Seed(TestContext context)
        {
            base.Seed(context);
        }
    }


    public static class DBController
    {
        public static TestContext ErpContext = new TestContext();
        public static void PopulateBaseData()
        {
            try
            {
                using (var ctx = new TestContext())
                {
                    if (true)
                    {
                        ctx.Users.Add(new User() { UserName = "testuser", Name = "Test", Password = "testpasswd" });
                        ctx.SaveChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.ToString());
            }
        }
    }

When I am in development mode and I repeatedly run the application, after a few runs, I keep getting exceptions when trying to add the new record to the database. The exception says:

{"55006: database \"mydb\" is being accessed by other users"}

... and as the exception detail:

Detail = "There is 1 other session using the database."

Any ideas why I am having these connection problems?

Answer 1

Turns out, the root cause of the problem is the connection that is maintained by VS2017 when you add a data source via "Project -> Add New Data Source" menu. When we add this data source to VS2017, and then run our application, we get the "There is 1 other session using the database" error. However, when we remove the data source from VS2017, we never see this error.