Consult validity with two dates using Ef 6

Question

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 customer, stating the date of issue and when it will be the next date that he should issue again (ProximaEmissao).

An example in 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 get the client’s documents Id = 1 which has been due for the year of 2018 (01/01/2018 à 12/12/2018), in the above table can not return any. See has a next date in 2018 Id = 1 but he’s already gone and done Id = 2, then you can’t come as a loser.

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 past date, but ProximaEmissao contains the last date.

Answer 1

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 @dataInitial and @dataFinal with their respective dates.