sqlite-net-pcl按坐标距离排序抛出NotSupportedException的解决方法

问题根因 #

sqlite-net-pcl的LINQ表达式解析器仅能转译有限的C#语法与内置函数映射到SQL语句，自定义Distance方法是纯C#侧逻辑，解析器无法将其转换为SQLite可执行的逻辑，因此抛出NotSupportedException。

最优实现方案（全计算下推到SQLite引擎，无全量数据拉取开销） #

SQLite原生内置所有恒向线距离计算需要的数学函数：PI()、COS()、TAN()、LOG()、SQRT()，完全可以直接在SQL语句中实现距离计算逻辑，排序、计算全在数据库侧执行，10万条数据规模下计算耗时在毫秒级。

前置要求 #

确保Locations表中经纬度是独立的REAL类型数值字段（示例中字段名为Latitude、Longitude，主键字段为Id），不要将坐标序列化为二进制/JSON格式存储，否则无法直接在SQL侧参与计算。

实现代码 #

1. 提前计算参考点的弧度值、地球半径常量，避免逐行重复计算固定值
2. 直接通过QueryAsync写原生SQL执行查询，绕开LINQ解析器的限制

// 参考点坐标
double refLat = 50d;
double refLon = 15d;
const double EarthRadiusKm = 6371d;

// 预计算参考点弧度常量
double refLatRad = refLat * Math.PI / 180d;
double refLonRad = refLon * Math.PI / 180d;

// 原生SQL执行排序，所有计算在SQLite侧完成
// 如需接收距离字段，可定义继承自Locations的类，添加public double Distance { get; set; }属性即可，无需修改原表结构
var sortedLocations = await _connection.QueryAsync<LocationWithDistance>(@"
    SELECT * FROM (
        SELECT 
            loc.*,
            SQRT(
                innerCalc.dLat * innerCalc.dLat + 
                innerCalc.q * innerCalc.q * innerCalc.adjustedDLon * innerCalc.adjustedDLon
            ) * @radius AS Distance
        FROM Locations loc
        JOIN (
            SELECT
                Id,
                dLat,
                CASE WHEN dLon > PI() THEN 2 * PI() - dLon ELSE dLon END AS adjustedDLon,
                CASE WHEN dPhi != 0 THEN dLat / dPhi ELSE COS(@refLatRad) END AS q
            FROM (
                SELECT
                    Id,
                    Latitude * PI()/180 - @refLatRad AS dLat,
                    Longitude * PI()/180 - @refLonRad AS dLon,
                    LOG(
                        TAN(Latitude * PI()/180 / 2 + PI()/4) / 
                        TAN(@refLatRad / 2 + PI()/4)
                    ) AS dPhi
                FROM Locations
            )
        ) innerCalc ON loc.Id = innerCalc.Id
    )
    ORDER BY Distance ASC
", new {
    @refLatRad = refLatRad,
    @refLonRad = refLonRad,
    @radius = EarthRadiusKm
});

上述SQL逻辑与提供的C#版Distance计算规则完全一致，计算结果无偏差。

性能优化建议 #

• 如果业务场景只需要参考点周边一定范围内的点，可以在SQL中增加前置矩形范围过滤，提前排除距离明显超出阈值的点，减少逐行计算的数据量。例如要查周边100km内的点，可以先加WHERE Latitude BETWEEN @minLat AND @maxLat AND Longitude BETWEEN @minLon AND @maxLon的条件，矩形边界可以提前通过参考点坐标换算得到。
• 如果查询频率极高，可以给经纬度字段建立联合索引，进一步加快范围过滤的速度。

不推荐的方案 #

不要尝试将全量10万条数据拉取到内存后再排序，序列化、IO传输、内存计算的总开销比SQLite侧直接计算高10~100倍，在移动端/低配置设备上还可能引发内存占用过高的问题。

内容的提问来源于stack exchange，提问作者Dokug