Find if a date range encompasses current date and if not find latest date in a single linq query

Question

Currently pulling back a list of entities that have different date ranges by year. So StartDate and StopDate for one record in the table is 01/01/2020 and 12/31/2020 respectively. For the next record StartDate and StopDate is 01/01/2021 and 12/31/2021 respectively. And so on. My current logic figures out if today lands between two dates and if not just get the entity with the latest date. Here is my logic, but I would like to build a linq query that only brings back a single entity and not use server resources.

Entity1 entity1{ get; set; }
List<Entity1> entity1List { get; set; }

    using (var ctx = ContextFactory.CreateDbContext())
    {

        entity1List = await ctx.entity1.AsNoTracking().Where(e => e.Column1 == thisId).ToListAsync();
        entity1 = entity1List.Where(e => e.StartDate < DateTime.UtcNow && e.StopDate > DateTime.UtcNow).FirstOrDefault();
        entity1 = entity1 ?? entity1List.OrderByDescending(e => e.StopDate).FirstOrDefault();
    }

Answer 1

You can do that by concatenating two results. Pros here that databse can use indexes.

using (var ctx = ContextFactory.CreateDbContext())
{
    va query = ctx.entity1.AsNoTracking().Where(e => e.Column1 == thisId);

    query = query
        .Where(e => e.StartDate < DateTime.UtcNow && e.StopDate > DateTime.UtcNow)
        .Take(1)
        .Concat(query.OrderByDescending(e => e.StopDate).Take(1));

    entity1 = await query.FirstOrDefaultAsync();
}