npgsql / doc

Documentation site for npgsql
http://npgsql.org/
26 stars 75 forks source link

Update documentation for using NetTopologySuite/NodaTime with NpgsqlDataSource #291

Closed sidec15 closed 1 year ago

sidec15 commented 1 year ago

I was trying to upgrade Npgsql.EntityFrameworkCore.PostgreSQL and Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite in my Asp.Net Core application from verison 6.x to version 7.x. I tried to use the Datasource as described in the Release notes of Npgsql 7.0. So I implemented something like the following:

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connStr);
await using var dataSource = dataSourceBuilder.Build();

services.AddDbContext<DbAppContext>(
  options => options
  .UseNpgsql(dataSource, o =>
  {
    o.UseNetTopologySuite();
  })
  .UseSnakeCaseNamingConvention()
);

In this application I have also Hangfire for the background jobs:

// Add Hangfire services.
services.AddHangfire(configuration => configuration
    .SetDataCompatibilityLevel(CompatibilityLevel.Version_180)
    .UseSimpleAssemblyNameTypeSerializer()
    .UseRecommendedSerializerSettings()
    .UsePostgreSqlStorage(connStr)
    );

// Add the processing server as IHostedService
services.AddHangfireServer();

Finally I initialize the database:

using (var scope = app.Services.CreateScope())
{
  var serviceProvider = scope.ServiceProvider;

  var context = serviceProvider.GetRequiredService<DbAppContext>();
  context.Database.IsRelational();
  DbInitializer.Initialize(context);
}

When I tried to read data from database that include geometry point type, an exception occurs:

Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'WebAppNpgsql.Context.DbAppContext'.
      System.InvalidCastException: Can't cast database type .<unknown> to Point
         at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.ReadCustom[TAny](NpgsqlReadBuffer buf, Int32 len, Boolean async, FieldDescription fieldDescription)
         at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
         at lambda_method13(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
      System.InvalidCastException: Can't cast database type .<unknown> to Point
         at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.ReadCustom[TAny](NpgsqlReadBuffer buf, Int32 len, Boolean async, FieldDescription fieldDescription)
         at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
         at lambda_method13(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

I found a solution after two days that is calling the extension method "UseNetTopologySuite" of NpgsqlDataSourceBuilder:

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connStr);
dataSourceBuilder.UseNetTopologySuite();
await using var dataSource = dataSourceBuilder.Build();

So basically the method UseNetTopologySuite has to be called two times because if I remove the call "o.UseNetTopologySuite();" in UseNpgsql method, another exception occurs when I initialize the database:

Unhandled exception. System.InvalidOperationException: The property 'Point.UserData' could not be mapped because it is of type 'object', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure.Internal.NpgsqlModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass2_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_ContextServices()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.IsRelational(DatabaseFacade databaseFacade)
   at Program.<Main>$(String[] args) in C:\Users\Simone\source\repos\web-app-npgsql\WebAppNpgsql\WebAppNpgsql\Program.cs:line 73
   at Program.<Main>$(String[] args) in C:\Users\Simone\source\repos\web-app-npgsql\WebAppNpgsql\WebAppNpgsql\Program.cs:line 84

I also tried to avoid using the NpgsqlDataSourceBuilder using the old way:

services.AddDbContext<DbAppContext>(
  options => options
  .UseNpgsql(connStr, o =>
  {
    o.UseNetTopologySuite();
  })
  .UseSnakeCaseNamingConvention()
);

In this case the error "Can't cast database type . to Point" occurs only if I comment the line of code used to retrieve the DB context:

var context = serviceProvider.GetRequiredService<DbAppContext>();

that is used to initialize the database.

Debugging the exception I found that the error "Can't cast database type . to Point" is thrown when the Npgsql library tries to obtain the TypeMapper for the point column. When the method ResolveHandler of the RowDescriptionMessage class is called, the _typeMapper._resolvers contains only Npgsql.TypeMapping.BuiltInTypeHandlerResolver and not Npgsql.NetTopologySuite.Internal.NetTopologySuiteTypeHandlerResolver.

I think that at least an improvement of the documentation about the usage of UseNetTopologySuite with the NpgsqlDataSourceBuilder must be provided.

I created a simple project to reproduce this behaviour: https://github.com/sidec15/web-app-npgsql.

Steps to reproduce

Using NpgsqlDataSourceBuilder:

  1. Download the Asp.Net Core project: https://github.com/sidec15/web-app-npgsql
  2. Run it
  3. Open the swagger at http://localhost:30000/swagger
  4. Run the api /User

    Without NpgsqlDataSourceBuilder:

  5. Download the Asp.Net Core project: https://github.com/sidec15/web-app-npgsql
  6. In Program.cs file comment lines:
    
    var dataSourceBuilder = new NpgsqlDataSourceBuilder(connStr);
    dataSourceBuilder.UseNetTopologySuite();
    await using var dataSource = dataSourceBuilder.Build();

services.AddDbContext( options => options .UseNpgsql(dataSource, o => { o.UseNetTopologySuite(); }) .UseSnakeCaseNamingConvention() );

 3. In Program.cs file uncomment lines:
```csharp 
services.AddDbContext<DbAppContext>(
  options => options
  .UseNpgsql(connStr, o =>
  {
    o.UseNetTopologySuite();
  })
  .UseSnakeCaseNamingConvention()
);
  1. In Program.cs file comment lines:

    using (var scope = app.Services.CreateScope())
    {
    var serviceProvider = scope.ServiceProvider;
    
    var context = serviceProvider.GetRequiredService<DbAppContext>();
    context.Database.IsRelational();
    DbInitializer.Initialize(context);
    }
    1. Run it
    2. Open the swagger at http://localhost:30000/swagger
    3. Run the api /User

Further technical details

roji commented 1 year ago

@sidec15 all of the above is correct, I'm sorry you lost time figuring it out... I never got around to updating the EF docs for using NpgsqlDataSource with the plugins...

I've pushed #292 live to fix that, please take a look and let me know what you think.

sidec15 commented 1 year ago

@roji now the documentation is perfectly clear. Thank you for replying me in a short time.