How to filter in LINQ for every X months?

Question

Hello, I would like to know the best way to filter a list of objects using LINQ in C#, I have a list of objects called Measuring, each measurement has a Data property, which is the date that was registered.

What I wish to answer is:

• Check the absence of measurements in a given month.
• Whether there is no measurement in 2 or more consecutive months
• Whether a measurement is available every 3 months(independent of the range in days)
• Whether there is no measurement in 4 or more consecutive months

For those who care, imagine the following:

List<Medicao> medicoes = Banco.CarregarListaMedicoes();

The Medical object could be for example:

public class Medicao {
   public int Id { get; set; }
   public DateTime Data { get; set; }
}

I want to filter the list above using LINQ and following the criteria I passed. Do you have any structure that makes this easier? Suggestions?

Answer 1

Only with LINQ it is not possible, you can even do with T-SQL, either using LEAD/LAG, or using a CTE Recursive.

But only with C#, you can create a Dictionary, then consult if in a given period of months there is at least one Measurement.

public static class Util
{
    private static Dictionary<DateTime, int> qtdMedicoesMes = CountMedicoes();
    private static Dictionary<DateTime, int> CountMedicoes()
    {
        List<Medicao> medicoes = Banco.CarregarListaMedicoes();
        return (
            from medicao in medicoes
            group medicao by medicao.AddDays(medicao.Day * -1).AddDays(1).Today into medicoesMes
            select new {
                Mes = medicoesMes.Key,
                Medicoes = medicoesMes.ToList()
            }
        ).ToDictionary(qtdMedicaoMes => qtdMedicaoMes.Mes, qtdMedicaoMes => qtdMedicaoMes.Quantidade);
    }

    public static bool HasMedicao (DateTime dataInicial, int qtdMeses)
    {
        for (var mes = dataInicial; mes < dataInicial.AddMonths(qtdMeses); mes = mes.AddMonths(1))
        {
            if (countMedicoes.ContainsKey(mes) && countMedicoes[mes] > 0)
            {
                return true;
            }
        }
        return false;
    }
}

Then you will need to make the following calls:

public class MedicaoController : ApiController
{
    public bool Verificar(int mes, int ano)
    {
        var mes = new DateTime(ano, mes, 1);
        dynamic obj = new ExpandoObject();
        obj.AusenciaMedicaoUmMes = !Util.HasMedicao(mes, 1);
        obj.AusenciaMedicaoDoisMeses = !Util.HasMedicao(mes, 2);
        obj.PossuiMedicaoTresMeses = Util.HasMedicao(mes, 3);
        obj.AusenciaMedicaoQuatroMeses = !Util.HasMedicao(mes, 4);
        return obj;
    }
}

Answer 2

Follow a way of doing, remembering that this will not be very performatic:

var medicoes = Banco.CarregarListaMedicoes();
// Customize com a data desejada ou substitua por um DateTime.Now
var data = new DateTime(2016, 01, 18);
var iniciodomes = data.AddDays((data.Day * -1) + 1);
var fimdomes = iniciodomes.AddMonths(1).AddSeconds(-1);

// Verificar a ausência de medições em um determinado mês.
var resultado1 = medicoes.All(medicao => medicao.Data >= iniciodomes && medicao.Data <= fimdomes);

// Se existe ausência de medição em 2 ou mais meses consecutivos.
var resultado2 = medicoes.Where(
    medicao => 
        medicao.Data >= iniciodomes && medicao.Data <= fimdomes
    && 
        medicoes.All(m => m.Data >= iniciodomes.AddMonths(-1) && m.Data <= fimdomes.AddMonths(-1))
);

// Se existe uma medição a cada 3 meses(independente do intervalo em dias).
var resultado3 = medicoes.Where(medicao => medicoes.Any(m => m.Data >= iniciodomes.AddMonths(-2) && m.Data <= fimdomes));

// Se existe ausência de medição em 4 ou mais meses consecutivos.
var resultado4 = medicoes.Where(
    medicao =>
        medicao.Data >= iniciodomes && medicao.Data <= fimdomes
    &&
        medicoes.All(m => m.Data >= iniciodomes.AddMonths(-1) && m.Data <= fimdomes.AddMonths(-1))
    &&
        medicoes.All(m => m.Data >= iniciodomes.AddMonths(-2) && m.Data <= fimdomes.AddMonths(-2))
    &&
        medicoes.All(m => m.Data >= iniciodomes.AddMonths(-3) && m.Data <= fimdomes.AddMonths(-3))
);