C# Entity Framework. Simplify query-CodePudding

How to simplify the next query to database:

public Plan? Get(DateTime now)
{
    return context.Plans
        .Where(x => IsActivePlan(x, now)) // 1 condition

        .Where(x => IsPlolongingPlan(x, now)) // 2 condition
        
        .OrderBy(x => x.StartedAt)
        .FirstOrDefault();
}

What I need: If there are objects after 1 condition, then execute "OrderBy" and return the first element. If there are no objects, then go to the 2 condition, execute "OrderBy" and return "FirstOrDefault". I don't want the objects to be taken at once by two conditions.

Thank you

CodePudding user response：

something like this

public Plan? Get(DateTime now)
{
    return context.Plans.Where(x => IsActivePlan(x, now)).OrderBy(x => x.StartedAt).FirstOrDefault()
        ?? context.Plans.Where(x => IsPlolongingPlan(x, now)).OrderBy(x => x.StartedAt).FirstOrDefault();
}

CodePudding user response：

because you don't want two conditions been true at once you have to:

return context.Plans
        .Where(x => (IsActivePlan(x, now) && !IsPlolongingPlan(x, now)) ||
                    (!IsActivePlan(x, now) && IsPlolongingPlan(x, now)))            
        .OrderBy(x => x.StartedAt)
        .FirstOrDefault();

CodePudding user response：

You can check before if exist any

Like this:

            var result = null;
            if (context.Plans.Any(x => IsActivePlan(x, now)))
            {
                result = context.Plans.Where(x => IsActivePlan(x, now))
                    .OrderBy(x => x.StartedAt)
                    .FirstOrDefault();
            }
            else
            {
                result = context.Plans.Where(x => IsPlolongingPlan(x, now))
                    .OrderBy(x => x.StartedAt)
                    .FirstOrDefault();
            }