Check validity with two dates using ef 6

0

I have the following model:

public class Documento
{
   public int Id {get;set;}
   public int ClienteId {get;set;}
   public int TipoDocumentoId {get;set;}
   public DateTime Emissao {get;set;}
   public DateTime ProximaEmissao {get;set;}
}

and store the documents of a particular client, informing the date of issue and when will be the next date that it should issue again ( ProximaEmissao ).

An example data:

Id  -  ClienteId   TipoDocumentoId      Emissão       -   ProximaEmissao
1   -  1           -    1            -  12/12/2017     -     12/12/2018
2   -  1           -    1            -  12/12/2018     -     12/12/2019

I want to fetch the documents from the Id = 1 client that has expired for the year 2018 ( 01/01/2018 à 12/12/2018 ), in the table above can not return any. See has an upcoming date in 2018 Id = 1 but it has already been there and made Id = 2 , so it can not come as expired.

My current code is this:

 db.Documentos
     .AsNoTracking()
     .Where(x => x.Type == model.Type &&
                 DbFunctions.TruncateTime(x.ProximaEmissao) >= model.Inicio &&
                 DbFunctions.TruncateTime(x.ProximaEmissao) <= model.Fim)
     .ToList();

The condition is that DataEmissao does not contain the date passed, but the ProximaEmissao contains the last date.

    
asked by anonymous 17.12.2018 / 17:59

1 answer

0
db.Documentos
    .GroupBy(o => new 
    { 
        o.ClientId, 
        o.TipoDocumentId 
    }
    .Select(o => new
    {
        o.Key.ClientId,
        o.Key.TipoDocumentId,
        Data = o.Max(d => d.ProximaEmissao)
    }
    .Where(o => o.Data >= @dataInicial && o.Data <= @dataFinal)
    .ToArray();

Try this. Replace @database and @dataFinal for their dates.

    
19.12.2018 / 18:21