[Bug]: MSSQL - Cannot obtain Schema for entity - Geography data type

[Banchio]

What happened?

I have tried to use DAB to expose Sales.Customer table from World Wide Importers database. DAB fails to start with the exception reported below. If I try to publish a non SystemVersioned Table (Sales.Orders, Sales.Invoices, etc) it works perfectly so I think this is due to the fact that Sales.Customers is a System-Versioned table in SQL.

I was able to repro the error in Visual Studio and it seems that exception is raised from the FillSchema method of the DbDataAdapter in the System.Data.Common but not sure about the reason.

Thanks

Version

0.5.34

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST, GraphQL

Relevant log output

Information: Microsoft.DataApiBuilder 0.5.34
Information: Config not provided. Trying to get default config based on DAB_ENVIRONMENT...
Found config file: dab-config.json.
Information: Setting default minimum LogLevel: Debug for Development mode.
Starting the runtime engine...
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Using file dab-config.json to configure the runtime.
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Invoice is Invoice
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for InvoiceLine is InvoiceLine
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Order is Order
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for OrderLine is OrderLine
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Customer is Customer
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Runtime configuration has been successfully loaded.
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL path: /graphql
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      StaticWebApps
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Runtime config loaded from file.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\sbanchie\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Invoice path: /api/Invoice
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      InvoiceLine path: /api/InvoiceLine
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Order path: /api/Order
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      OrderLine path: /api/OrderLine
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Customer path: /api/Customer
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization operations due to:
Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity Customer with underlying database object source: Sales.Customers due to: DataReader.GetFieldType(24) returned null.
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.GetTableWithSchemaFromDataSetAsync(String entityName, String schemaName, String tableName)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateSourceDefinitionAsync(String entityName, String schemaName, String tableName, SourceDefinition sourceDefinition, String[] runtimeConfigKeyFields)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities()
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.InitializeAsync()
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app)
info: Azure.DataApiBuilder.Service.Startup[0]
      Loading config file: dab-config.json
fail: Azure.DataApiBuilder.Service.Startup[0]
      Exiting the runtime engine...
crit: Microsoft.AspNetCore.Hosting.Diagnostics[6]
      Application startup exception
      System.ApplicationException: Could not initialize the engine with the runtime config file: dab-config.json
         at Azure.DataApiBuilder.Service.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, RuntimeConfigProvider runtimeConfigProvider)
         at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)
         at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
         at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
Unable to launch the runtime due to: System.ApplicationException: Could not initialize the engine with the runtime config file: dab-config.json
   at Azure.DataApiBuilder.Service.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, RuntimeConfigProvider runtimeConfigProvider)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at Azure.DataApiBuilder.Service.Program.StartEngine(String[] args)
Error: Failed to start the engine.

Code of Conduct

• I agree to follow this project's Code of Conduct

[seantleonard]

I'm still trying to identify a cross platform solution, but the underlying cause is from:
Sales.Customers due to: DataReader.GetFieldType(24) returned null.
and since GetFieldType is zero index, it seems to be pointing to the column: DeliveryLocation of sql type Geography
On Windows there are a number of stackoverflow/github discussions which allude to using Microsoft.SqlServer.Types https://www.nuget.org/packages/Microsoft.SqlServer.Types/#readme-body-tab to allow proper handling of SQL spatial types like Geography.

[Banchio]

Thanks @seantleonard , removing that field from my sample database worked. I've renamed the issue to keep track, thanks

[seantleonard]

Need to test out preview version of sqlclient to see behavior difference, if any. https://github.com/dotnet/SqlClient/releases/tag/v5.2.0-preview1
with release note:

Added Microsoft.SqlServer.Types to verify support for SqlHierarchyId and Spatial for .NET Core. dotnet/SqlClient#1848

[seantleonard]

Will also need to see how/if SQL server returns the SqlDataType of the spatial/geography types , and add compatibility flag in https://github.com/Azure/data-api-builder/pull/1568/files/9e914ab71352cbd4b2d380f0bfba35fe4ed30615#diff-d76ea4e7b6d1e701ff517cfb7fc205c60de6c749348bb126f83e69dc6bbfcf4a