Configuring EF and Npgsql for code first

Question

I'm trying to get EF to work with Postgresql. I would like code-first migrations if possible. So far, I got a simple ADO command to work, so I'm pretty sure at least my connection string is right. But I can't get EF code migrations to work for the life of me, and I can't find any recent guides that have helped.

I used NuGet to bring in Npgsql, EntityFramework and EntityFramework6.Npgsql. I created an empty database called blogsample in pgadmin.

Currently, When I run the app, it throws on line 29, when I try to add a blog to db:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll
Additional information: 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)

Here is my .cs file:

using Npgsql;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Entity;
using System.Linq;


namespace NpgSqlTest
{
    class Program
    {
        static void Main(string[] args)
        {
            EFTest();

        }



        private static void EFTest()
        {
            using (var db = new BloggingEntities())
            {
                // Create and save a new Blog
                Console.Write("Enter a name for a new Blog: ");
                var name = Console.ReadLine();

                var blog = new Blog { Name = name };
                db.Blogs.Add(blog);
                db.SaveChanges();

                // Display all Blogs from the database
                var query = from b in db.Blogs
                            orderby b.Name
                            select b;

                Console.WriteLine("All blogs in the database:");
                foreach (var item in query)
                {
                    Console.WriteLine(item.Name);
                }

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }
        }

        private static void ADOTest()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["PostgresDotNet"].ConnectionString;

            using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
            {
                conn.Open();
                const string sql = "SELECT * from sample;";
                NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);

                DataSet ds = new DataSet();
                using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }

                int i = 0;
            }
        }
    }




    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }

        public virtual List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public virtual Blog Blog { get; set; }
    }

    public class BloggingEntities : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // PostgreSQL uses the public schema by default - not dbo.
            modelBuilder.HasDefaultSchema("public");
            base.OnModelCreating(modelBuilder);
        }
    }    
}

And my App.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>

  <system.data>
    <DbProviderFactories>
      <!--for EF4.x and EF6.0.x -->
      <!--you may need this. if you don't modify machine.config-->
      <remove invariant="Npgsql" />
      <add name="Npgsql - .Net Data Provider for PostgreSQL" invariant="Npgsql" description=".Net Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.0.3.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
  </system.data>


  <entityFramework>
    <providers>
      <!--for EF6.0.x -->
      <!--you need this. add it manually-->
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
    </providers>
  </entityFramework>

  <connectionStrings>
    <add name="PostgresDotNet"
         connectionString="User ID=sava;Password=abc;Host=localhost;Port=5432;Database=blogsample;Pooling=true;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Solution

Your context doesn't appear to be using your connection string. Do that with the constructor on your context:

public class BloggingEntities : DbContext
{

    public BloggingEntities()
        : base("PostgresDotNet") { }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // PostgreSQL uses the public schema by default - not dbo.
        modelBuilder.HasDefaultSchema("public");
        base.OnModelCreating(modelBuilder);
    }
}   

http://fdevel.blogspot.com/2013/12/npgsql-with-entity-framework-6.html