The runtime is version 5.5.3 of SD.LLBLGen.Pro.DQE.SqlServer.
So I'm trying to achieve something close to this query.
Use NpiV2
GO
DECLARE @g geography = 'POINT(-86.607789 36.170040)';
Select [Name], [ProviderType], [Address1], [Address2], [City], [STATE], [PostalCode], [GeoLocation].STDistance(@g) FROM [provider].[tbl_NpiAddress]
JOIN [provider].[tbl_InfoNpiProvider] ON ([tbl_NpiAddress].[ProviderId] = [tbl_InfoNpiProvider].[Id])
WHERE GeoLocation.STDistance(@g) < 500
ORDER BY GeoLocation.STDistance(@g) ASC, [NAME];
Now I was able to create the where clause by implementing a custom IPredicate. However, I'm not sure how to sort by the function. My though was to do the same with ISortExpression as I did with IPredicate but they seem to be different. There may be a better way that I don't know of.
public class GeoLocationSortExpression : ISortExpression
{
public const string SQL_PARAMETER = "@order";
private List<ISortClause> _sortClauses = new List<ISortClause>();
public ISortClause this[int index] { get => _sortClauses[index]; set => _sortClauses[index] = value; }
public int Count { get => _sortClauses.Count; }
public IDbSpecificCreator DatabaseSpecificCreator { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
public List<DbParameter> Parameters { get; } = new List<DbParameter>();
public GeoLocationSortExpression(string latitude, string longitude)
{
Parameters.Add(new SqlParameter() { ParameterName = SQL_PARAMETER, Value = $"POINT({longitude} {latitude})" });
_sortClauses.Add(new SortClause());
}
public int Add(ISortClause sortClauseToAdd)
{
throw new NotImplementedException();
}
public IEnumerator GetEnumerator()
{
return _sortClauses.GetEnumerator();
}
public void Insert(int index, ISortClause sortClauseToAdd)
{
throw new NotImplementedException();
}
public void ReadXml(XmlReader reader)
{
throw new NotImplementedException();
}
public void Remove(ISortClause sortClauseToRemove)
{
throw new NotImplementedException();
}
public string ToQueryText()
{
return "GeoLocation.STDistance(@order)";
}
public string ToQueryText(bool aliasesForExpressionsAggregates)
{
return "GeoLocation.STDistance(@order)";
}
public void WriteXml(XmlWriter writer)
{
throw new NotImplementedException();
}
}
Called by
.OrderBy(new GeoLocationSortExpression(latitude, longitude))