How to make LINQ filter for every X months?

Question

How to make LINQ filter for every X months?

Navigation

#1 by (5 votes)
#2 by (3 votes)

4

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 Measurement , each measurement has a Date property, which is the date that was registered.

What I want to respond to is:

Check for missing measurements in a given month.
If there is no measurement in 2 or more consecutive months
If there is a measurement every 3 months (regardless of the interval in days)
If there is no measurement in 4 or more consecutive months

To whom it may concern, imagine the following:

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

The Medicao 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 a structure that facilitates this? Suggestions?

c# .net linq

asked by anonymous 18.01.2016 / 20:00

2 answers

Location of main in a project with MVC pattern Decrypt virus - vbscript

score 5 · Answer 1

Only with LINQ is not possible, you can even do with T-SQL , either using LEAD / LAG , or using% recursive%.

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

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;
    }
}

score 3 · Answer 2

Here's a way to do it, remembering that this is not going to be very performative:

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))
);