npgsql schema "dbo" does not exist

Question

i have a problem similar to Entity Framework 5.0 PostgreSQL (Npgsql) default connection factory

I have Npgsql declared in app.config :

<connectionStrings>
    <add name="monDbContexte" 
         connectionString="Server=127.0.0.1;Port=5432;Database=ma_datab_db;User Id=postgres;Password=root;" 
     providerName="Npgsql" />
  </connectionStrings>
  <!-- le factory provider -->
  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" 
           invariant="Npgsql" 
           support="FF" 
           description=".Net Framework Data Provider for Postgresql Server" 
           type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>
</configuration>

my context provider class:

public class ContextDB: DbContext
{
    public DbSet<Personne> Personnes { get; set; }

    public ContextDB()
        : base("monDbContexte")
    {
    }
}

my "Personne" Class:

[Table("personnes", Schema = "public")] public class Personne { [Key] [Column("id_personne")] [Display(Name="Identifiant")] public int id { get; set; }

    [Column("nom")]
    [Display(Name = "Nom")]
    [Required(ErrorMessage = "Merci de saisir le nom.")]
    public string Nom { get; set; }

    [Column("prenom")]
    [Display(Name = "Prénom")]
    [Required(ErrorMessage = "Merci de saisir le prénom.")]
    public string Prenom { get; set; }
...
...
}

and in my controller action i do something like this:

            using (var context = new ContextDB())
            {
                // *** here i have an exception...
                var personnes = from p in context.Personnes
                              where p.Nom.StartsWith("m")
                              orderby p.Nom
                              select new { p.Nom, p.Prenom };

                foreach (var une_personne in personnes)
                {
                    Console.WriteLine(une_personne.Nom + " " + une_personne.Prenom);
                }

after this i have an exception like this :

ERROR: schema "dbo" does not exist

this is an NpgsqlException

[french]
ERREUR: 3F000: le schéma « dbo » n'existe pas

i dont undersand what's wrong in this

EDIT:

this is the exception :
(sorry but it is in french)

PS : i have no projects in "C:\projects\Npgsql2" and i have nothing like this path in my current project, so what is this??

L'exception Npgsql.NpgsqlException n'a pas été gérée par le code utilisateur
  HResult=-2147467259
  Message=ERREUR: 3F000: le schéma « dbo » n'existe pas
  Source=Npgsql
  ErrorCode=-2147467259
  BaseMessage=le schéma « dbo » n'existe pas
  Code=3F000
  Detail=""
  ErrorSql=SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT CAST (count(1) AS int4) AS "A1" FROM "dbo"."__MigrationHistory" AS "Extent1Group") AS "GroupBy1"
  File=src\backend\catalog\namespace.c
  Hint=""
  Line=2826
  Position=82
  Routine=get_namespace_oid
  Severity=ERREUR
  Where=""
  StackTrace:
       à Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() dans C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:ligne 850
       à Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() dans C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:ligne 1173
       à Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() dans C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:ligne 1191
       à Npgsql.ForwardsOnlyDataReader.NextResult() dans C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:ligne 1377
       à Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) dans C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:ligne 1040
       à Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) dans C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:ligne 611
       à Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) dans C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:ligne 588
       à Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) dans C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:ligne 538
       à System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
       à System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
  InnerException: 

Answer 1

It seems EF uses dbo as the default schema for database. You need to change it to "public" which is the default schema for Postgresql.

You use that by importing the namespace:

using System.ComponentModel.DataAnnotations;

And annotate your classes to use a different schema:

[Table("mytable", Schema = "public")]
Class test {...}

For more information, check out my post about EF and Npgsql: http://fxjr.blogspot.com/2013/06/npgsql-code-first-entity-framework-431.html

I hope it helps.

Answer 2

I used EntitiyFramework6 6.2.0, EntityFramework6.Npgsql v3.2.0 and Npgsql 4.0.2

on your Context class add modelBuilder.HasDefaultSchema("public"); in OnModelCreating function.

For example;

public class ContextDB: DbContext
{
    public DbSet<Personne> Personnes { get; set; }

    public ContextDB()
        : base("monDbContexte")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("public");
        base.OnModelCreating(modelBuilder);
    }
}