Efcore.pg: Type reloading doesn't occur when creating database in async mode

[faryu]

Hello,

this is my moved issue from the EntityFrameworkCore repository about using spatial data with Postgresql.
https://github.com/aspnet/EntityFrameworkCore/issues/12762

In order to locate the issue I created a single file which contains all the relevant code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;
using Xunit;

namespace EF.Demo.Test
{
    public class GeometryTest
    {
        public class MyDbContext : DbContext
        {
            public DbSet<City> Cities { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder builder)
            {
                builder.UseNpgsql("Host=localhost;Database=test;Username=postgres;Password=mypassword",
                    o => o.UseNetTopologySuite());
            }

            protected override void OnModelCreating(ModelBuilder builder)
            {
                builder.HasPostgresExtension("postgis");
            }
        }

        public class City
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public Point Location { get; set; }
        }


        [Fact]
        public async Task Test()
        {
            var loc = new Point(4.15, 51.51, 0) {SRID = 4326};

            using (var ctx = new MyDbContext())
            {
                await ctx.Database.EnsureDeletedAsync();
                await ctx.Database.EnsureCreatedAsync();

                var city = new City()
                {
                    Id = 1,
                    Name = "MyCity",
                    Location = loc
                };

                await ctx.Cities.AddAsync(city);
                await ctx.SaveChangesAsync();
            }

            using (var ctx = new MyDbContext())
            {
                var city = await ctx.Cities.FindAsync(1);
                Assert.NotNull(city?.Location);

                Assert.True(loc.Distance(city.Location) < 0.000001);
            }
        }
    }
}

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFrameworks>net461;netcoreapp2.1</TargetFrameworks>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.3.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.1.1.1" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite" Version="2.1.1" />
    <PackageReference Include="xunit" Version="2.3.1" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.3.1" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="..\EF.Demo\EF.Demo.csproj" />
  </ItemGroup>
</Project>

I receive the following error:

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
---- Npgsql.NpgsqlException : The NpgsqlDbType 'Geometry' isn't present in your database. You may need to install an extension or upgrade to a newer version.

[austindrenski]

@faryu Could you also post the output of the following?

SELECT * FROM pg_extension;

If you're using psql, \dx will work too.

[faryu]

The result of the query is the following:

query-result.csv.txt

[austindrenski]

@YohDeadfall Does anything jump out to you here? Would this have anything to do with the Cartesian/geodetic defaults mentioned in the docs?

[roji]

This is most likely an issue with reloading the types after installing the extension... The test above deletes and recreates the database on every single run. Now, when the extension is first installed into a database (CREATE EXTENSION postgis), it creates new types but Npgsql has already loaded and cached the types (without the PostGIS types). NpgsqlConnection.ReloadTypes() needs to be called to reload everything, but the EF Core provider is actually supposed to do that when applying migrations that touch extensions.

A way to verify whether this is the case, is simply to run the test a second time but comment out the database delete/recreate lines...

[faryu]

The idea from @roji works.
If the delete/create line is removed for the second run, then it succeeds.
The first run, where it does create the database, still fails. It has to be run twice.

I also tried it without the EnsureCreated method and used migrations instead.
It had the same effect. The first run creates the database, but fails to pass the test.
The second run passes.

[roji]

This really shouldn't be happening, and I'm pretty sure we even have tests making sure that after creating new extensions the types are reloaded. Will need to be investigated...

[roji]

The following very similar code runs just fine - types are properly reloaded after the extension is installed. Not sure exactly what's going on above, will try to look after 2.1.2.

```c#
class Program
{
static void Main(string[] args)
{
using (var ctx = new BlogContext())
{
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
}

    Console.WriteLine("Done");
}

}

public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public Point Location { get; set; }
}

public class BlogContext : DbContext
{
public const string ConnectionString = "Host=localhost;Database=test;Username=npgsql_tests;Password=npgsql_tests";

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

protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
    builder.UseNpgsql(ConnectionString, o => o.UseNetTopologySuite());
}

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.HasPostgresExtension("postgis");
    builder.Entity<Blog>().ToTable("blogs");
}

}
```

[darionato]

No way to let it works in my case. I follow all the instructions but I got always 'Type 'Point' which is not supported by current database provider'.
I created the new Point column in a existing database. I'm not executing the ctx.Database.EnsureDeleted(); nor ctx.Database.EnsureCreated() but I call EnsureMigrated().
Any other suggestions?

[roji]

Sorry for letting this go for so long.

It's a very silly Npgsql bug... After adding a new extension, we have some special code in NpgsqlDatabaseCreator to detect that and call NpgsqlConnection.ReloadTypes() to reload the types. However, unfortunately this code has not been updated in the async version...! Just goes to show what maintaining of two different code paths can bring.

I'll fix this for 2.0.0 and backport to 2.1.3. As a workaround, call ctx.Database.EnsureDeleted() instead of ctx.Database.EnsureDeletedAsync().

[roji]

Backported to 2.1.3 in 5a6014380eb0993d3378ff7471353eaa08686a02

[ffagone]

I'm experiencing the same issue with 2.2.0, but with a synchronous call of EnsureDeleted() inside a WebApplicationFactory. When I comment out the deletion it works fine.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.NpgsqlException: The NpgsqlDbType 'Geometry' isn't present in your database. You may need to install an extension or upgrade to a newer version.

    public class PostgresWebApplicationFactory<TStartup> : WebApplicationFactory<TStartup> where TStartup : class
    {
        protected override void ConfigureWebHost(IWebHostBuilder builder)
        {
            builder.ConfigureServices(services =>
            {
                var configuration = new ConfigurationBuilder()
                    .AddEnvironmentVariables()
                    .AddJsonFile("appsettings.json")
                    .Build();

                string connectionString = configuration.GetConnectionString("Default");

                services.AddDbContext<IdentDbContext>(options =>
                {
                    options.UseNpgsql(connectionString);
                });

                services.AddDbContext<BusDbContext>(options =>
                {
                    options.UseNpgsql(connectionString, npgsqlOptions =>
                    {
                        npgsqlOptions.UseNetTopologySuite();
                    });
                });

                var sp = services.BuildServiceProvider();

                using (var scope = sp.CreateScope())
                {
                    var scopedServices = scope.ServiceProvider;

                    var identContext = scopedServices.GetRequiredService<IdentDbContext>();
                    identContext .Database.EnsureDeleted();
                    identContext .Database.Migrate();

                    var busContext = scopedServices.GetRequiredService<BusDbContext>();
                    busContext .Database.Migrate();
                }
            });
        }
    }
}

Any idea on this?

[roji]

@ffagone can you please open a new issue referencing this one, and with a more complete code sample (especially your full context definition)?

[Andrioden]

Ran into the same problem with 2.2.0 when trying to create an entity with a Point field. Had to "deploy" twice, which I assume correctly applied the changes the second time, as suggested above.

I acknowledge that you want us to create a new issue @roji , but this is where people end up when googling the error. So id rather add my info here.

My setup

DBContext class
``` c#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);

modelBuilder.HasPostgresExtension("postgis");
// I leave these in to indicate what is done as part of the build
ApplyIdentityTableNames(modelBuilder);
ApplyQueryFilters(modelBuilder);
ApplyConstraints(modelBuilder);
ApplyDeleteBehavior(modelBuilder);

}

Creating an address failed
``` c#
[Table(nameof(Address))]
public class Address : IRequireAuthorization
{
    // + Other stuff

    public Point Coordinate { get; set; }
}

Startup.cs
``` c#
public void ConfigureServices(IServiceCollection services)
{
//... Some other stuff

services.AddEntityFrameworkNpgsql().AddDbContext<DBContext>(options => options.UseNpgsql(GetPostgresConnectionString(), o => o.UseNetTopologySuite()));

//... Loads of other stuff

}

Program.cs
``` c#
public static async Task Main(string[] args)
{
    var host = CreateWebHostBuilder(args).Build();

    await InitializeDatabaseAsync(host);
    StartHangfireJobs(host);

    host.Run();
}

[roji]

@Andrioden the fix hasn't been released yet, so it makes sense for this error to still exist. We should try and get 2.1.4 out ASAP.

[aswanevelder]

I was missing this from @roji ... .Net 5

protected override void OnConfiguring(DbContextOptionsBuilder builder) { builder.UseNpgsql(ConnectionString, o => o.UseNetTopologySuite()); }