Spatial Queries using Entity Framework without Microsoft.SqlServer.Types.dll

You can use entity framework to make spatial queries against your Microsoft SQL Server database, which is great when the majority of the ORM code is using EF. What isn't great is that for this to work you have to add some native DLLs into your project, namely the "Microsoft.SqlServer.Types.dll" - this comes in 32 bit and 64 bit variants. Seemingly this also needs to appear in any top level application container, which is usually a different project to the one housing your ORM code. In the end these DLLs seem to get everywhere, except the deployed environment and cause a lot of problems at runtime if they're missing. For my purposes though, I didn't need full in-memory spatial support, I simply wanted to add spatial predicates to my where clauses and return normal "non-spatial" objects back from SQL Server. This is really easy to do in isolation, just write the SQL text and execute it directly using EF. However, most of the time you actually want to combine this with another set of undeterminate predicates which have been applied to the IQueryable in other parts of the code. To solve this issue, I wrote a helper which allows me tag on my spatial query as the last execution step of the IQueryable (e.g. replacing the ToList()) as follows:

internal static class EFSpatialHelper
{
        public static Task<List<T>> QueryByProximityAsync<T>(DbContext context, IQueryable<T> query, Proximity proximity)
        {
            string sqlPointGeogPreamble = $@"
Declare @GeogPoint as geography
Declare @GeogShape as geography

SET @GeogPoint = geography::STGeomFromText('POINT (' + Cast(@longitude as varchar) + ' ' + Cast(@latitude as varchar) + ')', 4326)
SET @GeogShape = @GeogPoint.STBuffer(@buffer)

";
            string sqlGeogPredicate = "@GeogShape.STIntersects(Point) = 1";

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@longitude", proximity.Longitude));
            sqlParams.Add(new SqlParameter("@latitude", proximity.Latitude));
            sqlParams.Add(new SqlParameter("@buffer", proximity.RadiusInMeters));

            return InjectAndExecuteSql(context, query, sqlPointGeogPreamble, sqlGeogPredicate, sqlParams.ToArray());
        }

        internal static Task<List<T>> GetByWellKnownTextAsync<T>(DbContext context, IQueryable<T> query, string wellKnownText)
        {
            string sqlWktGeogPreamble = @"
Declare @GeogPolygon as geography
Declare @GeomPolygon as geometry

Set @GeomPolygon = Geometry::STGeomFromText(@WKT, 4326)
Set @GeogPolygon = Geography::STGeomFromWKB(@GeomPolygon.MakeValid().STUnion(@GeomPolygon.STStartPoint()).STAsBinary(), 4326)

";
            string sqlGeogPredicate = "Point.STIntersects(@GeogPolygon)=1";

            return InjectAndExecuteSql(context, query, sqlWktGeogPreamble, sqlGeogPredicate, new SqlParameter("@WKT", wellKnownText));
        }

        private static Task<List<T>> InjectAndExecuteSql<T>(DbContext context, IQueryable<T> query, string spatialPreamble, string spatialPredicate, params SqlParameter[] spatialParams)
        {
            ObjectQuery<T> objectQuery = GetQueryFromQueryable(query);
            string sqlQryText = $"{spatialPreamble}{objectQuery.ToTraceString()}";

            // the query text in here is quite simple, we know it doesnt have an order by or a group by, so just stick predicates on the end.
            if (!sqlQryText.ToUpper().Contains("WHERE"))
            {
                sqlQryText = $"{sqlQryText} WHERE {spatialPredicate}";
            }
            else
            {
                sqlQryText = $"{Regex.Replace(sqlQryText, "WHERE", "WHERE (", RegexOptions.IgnoreCase)}) AND {spatialPredicate}";
            }

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            if (spatialParams != null)
            {
                sqlParams.AddRange(spatialParams);
            }

            foreach (var p in objectQuery.Parameters)
            {
                sqlParams.Add(new SqlParameter(p.Name, p.Value));
            }

            return context.Database.SqlQuery<T>(
                sqlQryText,
                sqlParams.ToArray()).ToListAsync();
        }

        private static ObjectQuery<T> GetQueryFromQueryable<T>(IQueryable<T> query)
        {
            var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();
            var internalQuery = internalQueryField.GetValue(query);
            var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();
            return objectQueryField.GetValue(internalQuery) as ObjectQuery<T>;
        }
}

Here is an example of the modified logic for executing a query:

if (wellKnownText != string.Empty)
{
    return await this.GetItemsInWellKnownTextAsync(context, fullQuery, wellKnownText).ConfigureAwait(false);
}
else if (proximity != null)
{
    return await this.GetItemsInProximityAsync(context, fullQuery, proximity).ConfigureAwait(false);
}
else
{
    return await fullQuery.ToListAsync().ConfigureAwait(false);
}