Compare existence of record with date and future date

Question

I have the following model

public class AgendaExame
{
    public int Id {get; set;}
    public int PacienteId {get; set;}
    public int ExameId {get; set;}
    public DateTime Data {get; set;} 
    public DateTime ProximaData {get; set;}
}

The use of this model is to save the exam and date that was performed, and when will be the next exam (ProximaData)

Following this saved information:

ID, PacienteId, ExameId,         DATA,      PROXIMADATA
1      1             1        01/01/2018    01/01/2019  
2      1             2        01/01/2018    01/01/2019  
3      1             1        31/12/2018    31/12/2019

When I filter between 01/01/2019 à 31/01/2019 I need to seek the tests that certain patients need to perform.

However, as we see in line 3, the patient performed the exam 1 day in advance, so his next date is only on the day 31/12, then it should not be on the day 01/01

What I’ve tried so far is this:

  var busca = db.AgendaExames.AsQueryable()
                .Where(x =>
                     x.ProximaData != null &&
                     DbFunctions.TruncateTime(x.ProximaData ) >= model.Inicio &&
                     DbFunctions.TruncateTime(x.ProximaData ) <= model.Fim);

But this code only looks for the ones I have to perform, I still need to check if the patient did not take the exam in advance (as explained above)

Summary: Save exam and control when your next exam will be

Answer 1

I don’t know exactly what you want to do with the information. But from what you explained in the comment you want to validate Proximadata is within a period and also if for this same field there is future date.

To bring this information you can make this change in the code that is in your question:

var busca = db.AgendaExames.AsQueryable()
.Where(x =>
     x.ProximaData != null &&
     DbFunctions.TruncateTime(x.ProximaData ) >= model.Inicio &&
     DbFunctions.TruncateTime(x.ProximaData ) <= model.Fim ||
     DbFunctions.TruncateTime(x.ProximaData ) > model.Fim);

But I didn’t see much point in it. If it’s something different try to explain your rules better.

More simply you could do just that which would bring all records within the informed period and future period as well:

var busca = db.AgendaExames.AsQueryable()
    .Where(x =>
         x.ProximaData != null &&
         DbFunctions.TruncateTime(x.ProximaData ) >= model.Inicio);