Initial and final period must contains a date and not contains in other date

c# entity-framework entity-framework-6

Question

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)

1
0
12/17/2018 7:50:35 PM

Popular Answer

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;
0
12/17/2018 8:11:10 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow