Zac Gross

Code & More

Postgres With Entity Framework Code First

| Comments

Npgsql is the most popular Postgres data provider for .net. None of the integration examples involving entity framework I found online used the db first paradigm. After a lot of testing I posted a working example of EF code first with Npgsql below.

Note: this example requires the database to already exist. EF automatic creation/migrations will not work.

Db configuration class, this ensures the correct connection factory is used. My testing found this was the only way to get it set, various connection string formats were ignored/overridden at runtime.

DB Config 
1
2
3
4
5
6
7
8
9
10
11

   
  public class NpgsqlConfiguration
      : System.Data.Entity.DbConfiguration
    {
        public NpgsqlConfiguration()
        {
            SetProviderServices("Npgsql", Npgsql.NpgsqlServices.Instance);
            SetProviderFactory("Npgsql", Npgsql.NpgsqlFactory.Instance);
            SetDefaultConnectionFactory(new Npgsql.NpgsqlConnectionFactory());
        }
    }

Next define a context class decorated with the custom db config attribute. Ensure the default schema is set to “public” (or the relevant schema name).

Key Points:

  • Ensure context decorated with Npgsql config class
  • Ensure correct schema name is set in OnMOdelCreating method
  • Don’t use an initializer
  • Apply any case/naming conversions needed

Some sort of case conversion will likely need to made in the OnModelCreating method, in my case I made all column names lowercase and did the necessary column name transformation there. For table names I used decorator attributes on the entity classes.

Context 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

  
  [DbConfigurationType(typeof(NpgsqlConfiguration))]
    public class ExampleContext
        : DbContext
    {

        public DbSet<Example> Examples { get; set; }


        public ExampleContext(string connectionString)
            : base(connectionString)
        {
            this.Configuration.LazyLoadingEnabled = false;
            this.Configuration.ProxyCreationEnabled = false;

            //Helpful for debugging            
            this.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
        }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            Database.SetInitializer<ExampleContext>(null);
            modelBuilder.HasDefaultSchema("public");
            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

            modelBuilder.Properties().Configure(c =>
            {
                var name = c.ClrPropertyInfo.Name;
                var newName = name.ToLower();
                c.HasColumnName(newName);
            });

        }

    }

Comments