2

We use ASP.NET Core 5 backend with SQL Server v15 and Entity framework V5, and there's something intriguing that I was struggling with, consider this:

You have a table as below:

CREATE TABLE [dbo].[ContactDetails](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    ...
    [Geolocation] [geography] NULL
)

That table is used by other tables, eg. Stores., to store contact details.

Table ContactDetails is then automatically scaffolded as below:

public partial class ContactDetail
{
    public ContactDetail()
    {
        Stores = new HashSet<Store>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public Geometry Geolocation { get; set; }
}

Then you store Stores' longitude and latitude (double) from input let's say as:

Geometry Store.ContactDetails.Geolocation = new NetTopologySuite.Geometries.Point(store.longitude, store.latitude) { SRID = 4326 };

Now given a location from a user, which was created the same way

Geometry userLocation = new NetTopologySuite.Geometries.Point(user.longitude, user.latitude) { SRID = 4326 };

You want to know how far is each Store from the user. How would you do that?

I did this:

DbContext dbContext; //...somehow constructed
var dbSet = dbContext.Set<Store>();
var distances = dbSet.Where(...).Select(store => store.ContactDetails.Geolocation.Distance(userLocation));

But to my surprise the distances are completely off! I expected that the distances are returned in degrees and I'll will have to convert that to Kms. But these numbers are completely off (like 83130).

So I tried this:

var anotherDistances = dbSet.Where(...).AsEnumerable().Select(store => store.ContactDetails.Geolocation.Distance(userLocation));

And this time the distances are as expected (eg. 1.245 degrees).

What happened there? I need to keep the results as IQueryable so I can do efficiently further filtering and transformations. How can I get correct distances without having to convert IQueryable to IEnumerable?

I could not find the answer from other questions, I suspect that Linq could not translate the query into SQL correctly and that querying after AsEnumerable() was able to use the correct type inferring and use spatial correct methods when it loaded objects in memory. But this is just some hazy understanding. I'd really appreciate if you could help me understand this.

SonTung
  • 21
  • 4

2 Answers2

0

Can you use IQuery<>.ToString() to display the actual SQL query being created? Maybe this will point you in the right direction. Also, maybe this answer is interesting.

Additionally, you might want to take a look at the database you are using. According to docs:

If an operation is server-evaluated by EF Core via SQL, the result's unit will be determined by the database.

Dominik Berse
  • 161
  • 6
  • IQueryable.ToString() just prints out type information of the IQueryable object. But I did enable database logging and it produced something this: ``` SELECT [c].[Geolocation].STDistance(@__userLocation_0) AS [c] FROM [Stores] AS [s] INNER JOIN [ContactDetails] AS [c] ON [s].[ContactDetailsId] = [c].[Id] ``` – SonTung Jun 21 '21 at 20:46
  • That looks okay to me, but I am not familiar with SQL Server. However, is it correct that you used `[geography]` in the SQL statement, but `Geometry` in C#? I think both behave slightly different. – Dominik Berse Jun 21 '21 at 21:11
  • Dominik Berse, thank you for you suggestion! You help me lead to the right answer. The translated SQL is uses different function to measure distance, which can be semantically similar but differ in return values. Please see my comment above. Thanks a lot again! – SonTung Jun 21 '21 at 21:31
  • Perfect, glad I could help! – Dominik Berse Jun 21 '21 at 22:19
0

Ah alright, mystery resolved. Thanks to Dominik's @dominik-berse suggestions I dig in a bit further.

When the query is translated from LINQ to SQL, functions being mapped do not have to be equivalent. In this case NetTopologySuite.Geometries.Distance() is translated to geography::STDistance() - which depending on the type of SRID model returns distance in different units, in case of 4326 model (used by GPS) it is meters.

When the query is translated to objects, objects' methods are directly invoked, in this case NetTopologySuite.Geometries.Distance().

And these two values can be completely different.

SonTung
  • 21
  • 4