I have this model:
public class Documento
{
public int Id {get;set;}
public int CustomerId {get;set;}
public int DocumentTypeId {get;set;}
public DateTime Date {get;set;} //issuance of the document
public DateTime NextDate {get;set;} //will be valid
}
The Date
property means the date the document was generated and the NextDate
property is the date on which the document needs to be regenerated
Data sample
Id - CustomerId - DocumentTypeId Date - NextDate
1 - 1 - 1 - 12/12/2017 - 12/12/2018 - Generate in 2017, expiring in 2018
2 - 1 - 1 - 12/12/2018 - 12/12/2019 - Generate in 2018, this ok
3 - 1 - 1 - 10/01/2017 - 10/01/2018
I need to search documents that were not generated in 2018
My starting date is 01/01/2018
and final 01/01/2019
I tried this:
db.Documents.AsNoTracking().AsQueryable()
.Where(x =>
DbFunctions.TruncateTime(x.Date) >= model.Start &&
DbFunctions.TruncateTime(x.Date) <= model.End &&
DbFunctions.TruncateTime(x.NextDate) >= model.Start &&
DbFunctions.TruncateTime(x.NextDate) <= model.End).ToList();
[Edit]
When initial and final date is passed
01/01/2018
and 01/01/2019
the return corret is:
Id - CustomerId - DocumentTypeId Date - NextDate
3 - 1 - 1 - 10/01/2017 - 10/01/2018
Document with (Id 1)
was generate in 2017 and valid until 2018. But in 12/12/2018 was regenerated (Id 2)
If you only want the documents that expired in 2018 then I'm assuming you only need to look at the end dates.
This query will find documents that have a NextDate
that falls within your search.
Note: I used let
to avoid redundant calls to DbFunctions.TruncateTime
.
var expiredDocuments = from document in db.Documents.AsNoTracking().AsQueryable()
let endDate = DbFunctions.TruncateTime(document.NextDate)
where endDate >= model.Start && endDate <= model.End
select document;