在Linq Query WHERE语句中返回两个Lat/Long坐标之间的计算距离

Question

我试图通过将距离计算移动到LINQ到SQL而不是在枚举上执行计算来提高性能。具体来说，我试图以英里为单位计算距离，并过滤半径为50英里内的实体。

下面是我的代码(为缺乏可读性而道歉):

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();

最终，我需要将其重构为可在LINQ中工作的替代方案。下面是错误:

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)

我也试过这个:

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)

相同的系统。NotSupportedException for Linq to SQL translation.

Answer 1

我通过连接DbGeography.FromText()中的字符串来解决这个问题(从DbGeography.PointFromText()更改)。还得把长尾倒转到长尾。

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