在linq where子句中使用方法返回值

ycggw6v2  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(349)

这里我有一个linq查询,它工作得很好,但我正在努力使它更可读。基本上,我将新的datetime范围添加到db中,但它不能与现有的范围相交。我试着写一个方法,传递开始和结束时间,并在where子句中使用返回值,但据我所知,不能在查询中使用函数。有什么建议吗?

var salariesInPeriod = await db.Salaries
                .Where(x => x.Id != salaryIdToIgnore)
                .Where(x => x.UserId == userId)
                .Where(x =>
                    //These filters check if there are any overlapping ends
                (x.StartDate <= startDate &&
                 startDate <= (x.EndDate ?? DateTime.MaxValue))
                ||
                (x.StartDate <= (endDate ?? DateTime.MaxValue) &&
                 (endDate ?? DateTime.MaxValue) <= (x.EndDate ?? DateTime.MaxValue))
                ||
                (startDate <= x.StartDate &&
                 x.StartDate <= (endDate ?? DateTime.MaxValue))
                ||
                (startDate <= (x.EndDate ?? DateTime.MaxValue) &&
                 (x.EndDate ?? DateTime.MaxValue) <= (endDate ?? DateTime.MaxValue))
                )

                .Select(x => new
                {
                    x.StartDate,
                    x.EndDate
                })
                .ToListAsync();

这就是我所尝试的:

public bool CheckIntersections(DateTime currentStart, DateTime newStart, DateTime? currentEnd, DateTime? newEnd)
    {
        if ((currentStart <= newStart &&
             newStart <= (currentEnd ?? DateTime.MaxValue))
            ||
            (currentStart <= (newEnd ?? DateTime.MaxValue) &&
             (newEnd ?? DateTime.MaxValue) <= (currentEnd ?? DateTime.MaxValue))
            ||
            (newStart <= currentStart &&
             currentStart <= (newEnd ?? DateTime.MaxValue))
            ||
            (newStart <= (currentEnd ?? DateTime.MaxValue) &&
             (currentEnd ?? DateTime.MaxValue) <= (newEnd ?? DateTime.MaxValue)))
        {
            return true;
        }

        return false;
    }

然后我尝试在查询中使用结果:

var salariesInPeriod = await db.Salaries
            .Where(x => x.Id != salaryIdToIgnore)
            .Where(x => x.UserId == userId)
            .Where(x => CheckIntersections(x.StartDate, startDate, x.EndDate,endDate) == true)

            .Select(x => new
            {
                x.StartDate,
                x.EndDate
            })
            .ToListAsync();

有什么办法可以简化这个问题吗?

bqujaahr

bqujaahr1#

不能在linq查询中使用任何函数。为了更好的可读性,您可以使用扩展方法:

public static class SalaryQueryExtensions
{
    public static IQueryable<Salary> WithIntersections(
        this IQueryable<Salary> salaries, DateTime currentStart, DateTime newStart, DateTime? currentEnd, DateTime? newEnd)
    {
        // return your query. Example:
        // salaries.Where(x=> x.DateTime > currentStart);
    }

    public static IQueryable<Salary> WithIgnoreId(this IQueryable<Salary> salaries, Guid id)
    {
        // return your query.
    }

    public static IQueryable<Salary> WithUserID(this IQueryable<Salary> salaries, Guid userId)
    {
        // return your query.
    }
}

然后使用如下扩展方法:

var salariesInPeriod = await db.Salaries
            .WithIgnoreId(salaryIdToIgnore)
            .WithUserId(userId)
            .WithIntersections(startDate,endDate)
            .ToListAsync();

相关问题