我想在实体框架核心中生成一个类似于以下的查询:
SELECT ID, Name, ..., (SQUARE(Color.STX - 0.1) + SQUARE(Color.STY - 0.2) + SQUARE(Color.Z - 0.3)) AS ColorVarianceSquared
FROM Point
WHERE ColorVarianceSquared < 10000.0 AND ...
ORDER BY ColorVarianceSquared
表的模式:
CREATE TABLE Point (
[ID] UNIQUEIDENTIFIER NOT NULL,
[Name] NVARCHAR (64) NULL,
...
[Color] [sys].[geometry] NULL,
);
(Color列从C#/EF初始化为NetTopologySuite.Geometries.Point
的实例)
我的问题的用例是根据颜色匹配的程度(使用CIELAB颜色空间)对结果进行排序。
有没有办法创建一个别名列,以便它可以在ORDER BY子句中使用,或者我必须重复距离公式计算?我知道,即使是裸SQL也不能在WHERE子句中使用别名列,因此派生表可能必须在某个地方发挥作用,或者在最坏的情况下,在SELECT、WHERE和ORDER BY中写入三次距离计算。从EF中,也许我可以将SELECT()插入另一个对象并在那里缓存距离值,然后根据它进行查询/排序?类似于:
NetTopologySuite.Geometries.Point ReferenceColor = ...
double MaximumVariance = ...
context.Point.Select(m => new {
ID = m.ID,
Name = m.Name,
...
ColorVariance = m.Color.Distance(ReferenceColor),
})
.Where(m => m.ColorVariance < MaximumVariance && ...)
.OrderBy(m => m.ColorVariance)
.ThenBy(...)
更复杂的是,颜色匹配和其他WHERE条件是使用基于Controller方法中绑定的参数的表达式动态添加的,因此理想的解决方案可能包括表达式和反射,以便仅在请求时将此功能添加到查询中。虽然严格来说不是我问题的一部分,但我也在想,添加所有这些是否会比简单地重复距离计算效率低,如果用户没有请求,它就不会被添加到WHERE子句中(同样,基于用户输入动态构建)。换言之,选择它但不在WHERE或ORDER BY中使用它,可能会比将这种复杂情况添加到web服务器造成的数据库服务器速度减慢更小。
将查询移动到subquery
,然后过滤结果。
SELECT t1.ColorVarianceSquared FROM
SELECT ID, Name, ..., (SQUARE(Color.STX - 0.1) + SQUARE(Color.STY - 0.2) + SQUARE(Color.Z - 0.3)) AS ColorVarianceSquared
FROM Point) as t1
WHERE t1.ColorVarianceSquared < 10000.0
ORDER BY t1.ColorVarianceSquared
为了更快的查询。
SELECT t1.ColorVarianceSquared FROM
SELECT ID, Name, ..., (SQUARE(Color.STX - 0.1) + SQUARE(Color.STY - 0.2) + SQUARE(Color.Z - 0.3)) AS ColorVarianceSquared
FROM Point
WHERE (SQUARE(Color.STX - 0.1) + SQUARE(Color.STY - 0.2) + SQUARE(Color.Z - 0.3)) < 10000.0) as t1
ORDER BY t1.ColorVarianceSquared