查找使用LINQ C＃在特定日期范围内活动的记录

Question

我需要列出在一段时间内某个点处活动的记录：

此处的活动概念是dateeyeeavation和Natedeeyactation之间的范围。

模型：

public class DME
{
    public DateTime DateActivation { get; set; }               // Is always set
    public Nullable<DateTime> DateDeactivation { get; set; }   // Set or Null
}

行动：

public ActionResult ActiveRecordsInTimeFrame(string DateStart, string DateFinish)
{
    IList<int> dmeIds = db.DMEs
                        .AsEnumerable()
                        .Where(d => d.DateActivation >= Convert.ToDateTime(DateStart))
                        .Where(d => d.DateActivation <= Convert.ToDateTime(DateFinish))
                        .Where(d => d.DateDeactivation == null || d.DateDeactivation <= Convert.ToDateTime(DateFinish))
                        .Select(d => d.Id).ToList();
}

我没有使它正常工作，因为当dateeactivation＆lt; lt;当我无法获得记录；例如，datestart和日期活化为null，或者当dateyctivation>datestart和日期激活> datefinish

我认为有5例记录在时间上处于活动状态：

 DateActivation | DateStart | DateDeactivation  | DateFinish
 DateStart | DateActivation | DateDeactivation  | DateFinish
 DateStart | DateActivation | DateFinish | DateDeactivation
 DateActivation | DateStart | DateFinish | DateDeactivation = NULL
 DateStart | DateActivation | DateFinish | DateDeactivation = NULL

请有任何帮助吗？

谢谢。

Answer 1

我认为这应该是您要寻找的条件：

(d.DateActivation == null || dtStart < d.DateDeactivation) && dtEnd > d.DateActivation

Answer 2

我会做这样的事情：

public class DME
{
    public DateTime DateActivation { get; set; }               // Is always set
    public DateTime? DateDeactivation { get; set; }   // Set or Null
}
public ActionResult ActiveRecordsInTimeFrame(string DateStart, string DateFinish)
{
    var model = GetActiveRecordsBetweenDates(
                    Convert.ToDateTime(DateStart), 
                    Convert.ToDateTime(DateFinish)).ToList();
    ...
}
...
private IQueryble<IEnumerable<int>> 
        GetActiveRecordsBetweenDates(DateTime dtStart, DateTime? dtEnd) 
{
    if(dtEnd.HasValue)
    {
        // both dates provided
        return from db in db.DMEs
               where d.DateActivation >= dtStart &&
                     d.DateActivation <= dtEnd
               select db.Id;
    }
    // no end date
    return from db in db.DMEs
           where d.DateActivation >= dtStart
           select db.Id;
}

---

从评论中更新：I only want the active ones.

假设如果DateDeactivation有一个日期，那是因为它已经停用

private IQueryble<IEnumerable<int>> 
        GetActiveRecordsBetweenDates(DateTime dtStart, DateTime? dtEnd) 
{
    if(dtEnd.HasValue)
    {
        // both dates provided
        return from db in db.DMEs
               where d.DateActivation >= dtStart &&
                     d.DateActivation <= dtEnd && 
                     d.DateDeactivation == null
               select db.Id;
    }
    // no end date
    return from db in db.DMEs
           where d.DateActivation >= dtStart && 
                 d.DateDeactivation == null
           select db.Id;
}