Fixing CosmosDb spatial queries and current culture issue

A cool feature of CosmosDb is its spatial features. Spatial queries enables you to perform queries to measure distance between two points or similar queries.

By default I'm using linq whenever I can. Recently I ran into an issue with linq and spatial queries targeting CosmosDb. As I'm a danish developer my local developer machine has its culture set to danish. I've never had any issues with this until recently. I was trying to execute this query to measure the distance between two points.

var query = base.documentClient
.CreateDocumentQuery<Trip>(collectionLink,  new FeedOptions() {EnableScanInQuery = true })
.Select(y => y.MeetingPoint.Distance( point))
.AsDocumentQuery();

Out of the box this should return the distance as a double between the MeetingPoint and the point parameter. Point is from the namespace Microsoft.Azure.Documents.Spatial. After running some tests I didn't get the expected results, actually no results at all.

After some investigation I decided to try the SQL expression to query CosmosDb with a hard coded expression I knew should work with the data in the database.

var query = base.documentClient
.CreateDocumentQuery<Trip>(
base.collectionLink,
"SELECT value ST_DISTANCE(
f.MeetingPoint, {'type': 'Point', 'coordinates':[9.71758,55.529514]}) from trips f", new FeedOptions() { EnableScanInQuery = true })
.AsDocumentQuery();

Executing this query I started getting results as expected. During debugging I noticed the query that was being generated and executed. Here are the query from both queries, do you see the difference?

{{"query":"SELECT VALUE ST_DISTANCE(root["MeetingPoint"], {"type": "Point", "coordinates": [9,71758, 55,529514]}) FROM root "}}

{{"query":"SELECT value ST_DISTANCE(f.MeetingPoint, {'type': 'Point', 'coordinates':[9.71758,55.529514]}) from trips f"}}

If you pay close attention to latitude and longitude you'll notice the first query uses "," as separator and the latter uses "."
As I wrote in the beginning my developer machine has regional settings set to danish, meaning my current culture is danish. For display purpose this means using the "," separator in numbers. But for queries like this, it's a no-go. I would expect this to use InvariantCulture.

After some searching I found 2 solutions. A combination of C# string interpolation and SQL expressions or changing CurrentCulture og the thread or application.

Solution 1

Using string interpolation and SQL expressions  the final statement will be like this:

var query = base.documentClient
.CreateDocumentQuery<Trip>(
base.collectionLink,
Invariant($@"SELECT value ST_DISTANCE(
f.MeetingPoint, {{'type': 'Point', 'coordinates':[{longitude},{latitude}]}}) from trips f"), new FeedOptions() { EnableScanInQuery = true })
.AsDocumentQuery();

There are a few details to notice. First the Invariant which specifies formatting should use InvariantCulture. This shortcut is enabled with this using statement in the file, a really handy feature.

using static System.FormattableString;

Secondly the string interpolation $@ is used because the Point definition itself is using { } to define the point and we need to escape this using {{ }}. Finally because we need to use parameters for latitude and longitude.

Running this query makes everything behave as expected. It does include the SQL expression as a string as some might not like.

Solution 2

The above solution ensures that we are only changing culture around the specific area. Another option is to change Culture for the current thread or the whole application. This is an option if you have an API etc. With this option you can keep using linq with no modifications. Using ASPNET Core you can change the culture for the entire API like this.

CultureInfo.DefaultThreadCurrentCulture = CultureInfo.InvariantCulture;
CultureInfo.DefaultThreadCurrentUICulture = CultureInfo.InvariantCulture;

It's a a lot shorter than option 1, and if you don't need any specific culture, then this seems like the easy solution.