c# - 获取相邻实体

Question

我正在编写一个函数，它根据日期从数据库中获取相邻(上一个和下一个)实体。我已经想出了如何在 2 个查询中返回邻居，但如果我可以一次拉两个实体，我更愿意。

public interface IHasDateRange
{
    DateTime StartDate { get; set; }
    DateTime EndDate { get; set; }
}

public static (TEntity Previous, TEntity Next) GetNeighborsOrDefault<TEntity>(
    this IQueryable<TEntity> query, JustDate startDate)
        where TEntity : class, IHasDateRange
{
    var previous = query.Where(x => x.StartDate < startDate)
            .OrderByDescending(x => x.StartDate)
            .FirstOrDefault();

    var next = query.Where(x => x.StartDate > startDate)
        .OrderBy(x => x.StartDate)
        .FirstOrDefault();

    return (previous, next);
}

我想在单个查询中提取上一个和下一个，最好以一种不会通过翻译过于复杂的表达式生成 sql 庞然大物的方式。

编辑
我想有一种方法可以做到，如果我删除开始日期过滤器的位置并计算距离，我仍然卡住了，但我觉得有些东西应该起作用。

var previous = query
    .Select(x => new { 
        Entity = x,  
        Distance = DbFunctions.DiffDays(x.StartDate, startDate)
   })
    .Where(x => x.Distance != 0); 

注意:假设每个实体都有一个唯一的开始日期。

有没有一种简单的方法可以在单个查询中提取上一个和下一个实体？

Answer 1

这与让两个实体离开(并包括)startDate 之前的第一个日期相同。 .

query.Where(e => e.StartDate != startDate
    && e.StartDate >= query.OrderByDescending(e1 => e1.StartDate)
        .Where(e1 => e1.StartDate < startDate).Select(e1 => e1.StartDate).FirstOrDefault())
    .OrderBy(e => e.StartDate)
    .Take(2)

如您所见，您无法避免运行两个查询，尽管现在第二个查询是一个主查询中的子查询。

在 EF6 中，这会生成一个中等复杂的查询，如下所示:

SELECT TOP (2)
    ...
    FROM ( SELECT 
        ...
        FROM  [dbo].[Entity] AS [Extent1]
        INNER JOIN  (SELECT TOP (1) [Project1].[StartDate] AS [StartDate]
            FROM ( SELECT 
                [Extent2].[StartDate] AS [StartDate]
                FROM [dbo].[Entity] AS [Extent2]
                WHERE [Extent2].[StartDate] < @p__linq__1
            )  AS [Project1]
            ORDER BY [Project1].[StartDate] DESC ) AS [Limit1] ON 1 = 1
        WHERE ( NOT (([Extent1].[StartDate] = @p__linq__0) AND ((CASE WHEN ([Extent1].[StartDate] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = 0))) AND ([Extent1].[StartDate] >= [Limit1].[StartDate])
    )  AS [Project2]
    ORDER BY [Project2].[StartDate] ASC

我惊喜地看到 EF 核心 3.1.3 生成了一个相当简单的查询，如下所示:

SELECT TOP(@__p_2) ...
FROM [Entity] AS [e]
WHERE (([e].[StartDate] <> @__startDate_0) OR [e].[StartDate] IS NULL) AND ([e].[StartDate] >= (
    SELECT TOP(1) [e0].[StartDate]
    FROM [Entity] AS [e0]
    WHERE [e0].[StartDate] < @__startDate_1
    ORDER BY [e0].[StartDate] DESC))
ORDER BY [e].[StartDate]