CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sqlgeospatial
Dan G
Dan G

Reputation: 51

C# Return a Calculated Distance between two Lat/Long Coordinates within a Linq Query WHERE statement

I am trying to improve performance by moving a distance calculation into LINQ to SQL instead of performing the calculation on an enumerable. Specifically, I am trying to calculate distance in miles and filter for entities within a 50 miles radius.

Here is my code (apologies for the lack of readability):

List<Thing> thing = _context.Things
  .AsNoTracking()
  .Where(t => (DbGeography.PointFromText(
      string.Format(
        "POINT({0} {1}",
        t.Latitude.Value, 
        t.Longitude.Value)
    ,4326)
   .Distance(
      DbGeography.PointFromText(
        string.Format(
          "POINT({0} {1}",
          other.Latitude.Value, 
          other.Longitude.Value),
      ,4326)
      * 0.000621371) <=50)
  .ToList();

Ultimately I will need to refactor this to an alternative that works in LINQ. Here is the error:

 System.NotSupportedException : LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object, System.Object)' method, and this method cannot be translated into a store expression.

Stack Trace: 
DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
ExpressionConverter.TranslateExpression(Expression linq)
ExpressionConverter.TranslateIntoCanonicalFunction(String functionName, Expression Expression, Expression[] linqArguments)
SpatialMethodCallTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
ExpressionConverter.TranslateExpression(Expression linq)
ExpressionConverter.TranslateIntoCanonicalFunction(String functionName, Expression Expression, Expression[] linqArguments)
<27 more frames...>
ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
<>c__DisplayClass7.<GetResults>b__5()
DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
IEnumerable<T>.GetEnumerator>b__0()
LazyEnumerator`1.MoveNext()
List`1.ctor(IEnumerable`1 collection)
Enumerable.ToList[TSource](IEnumerable`1 source)

I have also tried this:

SqlGeography.Point(
  Convert.ToDouble(t.Latitude.Value),
  Convert.ToDouble(t.Longitude.Value),
  4326)
.STDistance(
  SqlGeography.Point(
    Convert.ToDouble(other.Latitude.Value), 
    Convert.ToDouble(other.Longitude.Value), 
    4326)) * 0.000621371) <= 50)

Same System.NotSupportedException for Linq to SQL translation.

Upvotes: 0

Views: 651

Answers (1)

Dan G
Dan G

Reputation: 51

I resolved this by just concatenating the strings in DbGeography.FromText() (changed from DbGeography.PointFromText()). Also did have to reverse lat long to long lat.

DbGeography.FromText("POINT(" + t.Longitude.Value + " " + t.Latitude.Value + ")"
,4326)

Upvotes: 1

Related Questions