Error accessing Geometry column from Azure SQL in Azure Function

Question

I have an Azure Function that accesses an Azure SQL database. The 42nd column in the database is of the Geography datatype (this is where the problem lies).

Here is the code I am running:

// var datatable = DataTable();
// Filled 'datatable' from a database query

string json = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable, Newtonsoft.Json.Formatting.Indented);
return new HttpResponseMessage(HttpStatusCode.OK) 
{
    Content = new StringContent(json, Encoding.UTF8, "application/json")
}; 

And I am getting this error message:

Exception while executing function: Functions.GetRepeaterDetails. Microsoft.Azure.WebJobs.Script: One or more errors occurred. System.Data: DataReader.GetFieldType(42) returned null.

I've done several hours' worth of searching and trying what others have claimed to work, but none have fixed my scenario.

I read this on another forum:

Be aware that your sample will work, but what won’t work is a web application that retrieves geometry from SQL Azure, because SQL Azure is currently running Denali (SQL Server 2012) at compatibility level 100; hence it will return instances of the 10.0 spatial types and the web role will throw an exception when trying to cast them to 11.0.

Some things I've tried:

1. Added Microsoft.SqlServer.Types v14.0.314.76 to project.json (also tried several other versions.
2. Added HashFoo.SqlServer.SpatialTypes v10.0.0 to project.json
3. Changed the Azure SQL Server compatibility level to 110.

Even though I can see that the server is installing the appropriate references via Nuget, the error message has remained the same.

Answer 1

I don't know exactly which assembly to load to make that work, but you can sidestep the issue by converting that column to WKT using the .ToString() function in your query.

Since you're formatting the results a JSON, that's probably what you want anyway.

eg

select ...., geo_location.ToString() geo_location, . . .
from some_table

You can also just have SQL Server Return Query Results as JSON