Hello,

I'm trying to add this line in the MS SQL query order by.

ORDER BY GeoLocation.STDistance(@g) 

I thought I could create a custom ISortExpression and then add it to ToQueryText. However, it never calls that function. Any ideas?

Please provide more details. Which version of the runtime library are you using?

Could you please provide a code snippet of how you are adding the SortExpression to the IQuerytext?

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

ToQueryText() is not called because you are not adding any SortClause to the SortExpression. Please check the source code of the SortExpression Class under the ORMSupportClasses.