So we have the following MySQL table:
CREATE TABLE CLUB(
Id INT auto_increment PRIMARY KEY,
ClubName VARCHAR(256),
GeoLocation POINT SRID 4326
);
As you can see our table Club has a GeoLocation column of the spacial datatype Point.
Using the Point datatype in C# with the Entity Framework is possible by using NetTopologySuite.Geometries and our data annotations in the Club class look like this:
[Table("club")]
public class Club
{
[Key]
[NotNull]
[Column("Id", TypeName = "INT")]
public int Id { get; set; }
[MaxLength(256)]
[Column("ClubName", TypeName = "VARCHAR(256)")]
public string Name { get; set; }
[Column("GeoLocation", TypeName = "POINT")]
public Point GeoLocation { get; set; }
}
Now we want to only get clubs in the vicinity of the user. Easy:
...
if (isFilteredByDistance)
{
// TODO: EF is doing a switch on the SRID. But SRID is always 4326
Point userLocation = new Point(userLongitude, userLatitude)
{
SRID = 4326
};
filteredClubs = filteredClubs
.Where(club =>
club.GeoLocation.IsWithinDistance(userLocation, someRadius));
}
...
The above LINQ code is part of a larger query that will be executed server side. Here's the interesting part of the generated MySQL query resulting from it:
...
AND
(CASE
WHEN ST_SRID(`c`.`GeoLocation`) = 4326
THEN
ST_Distance_Sphere(`c`.`GeoLocation`, @__userLocation_5)
ELSE
ST_Distance(`c`.`GeoLocation`, @__userLocation_5)
END <= @__8__locals1_filter_Radius_6))
AND
...
As you can see the Entity Framework generated a switch case, checking if the SRID of the database column GeoLocation is 4326 (which it always will be).
Even though it's not really a big deal, because the code works we'd like to optimize the automatically generated queries as much as possible. Therefore:
How do we make the Entity Framework aware of the fact that the GeoLocation column in table club is in fact using SRID 4326 so that it doesn't generate this unnecessary switch case in MySQL?
I guess there are some data annotations for it? Or some Fluent API syntax? Any help would be greatly appreciated