Search for missing records range

Question

Search for missing records range

Navigation

#1 by (0 votes)

3

Imagine the following situation: a table looks like this:

Id (int) - Codigo (int) - Descricao (varchar)
1        -      01      - Descrição 1
2        -      03      - Descrição 2
3        -      04      - Descrição 3
4        -      05      - Descrição 4

Notice that between Id 1 and 2 the code "02" is missing. I'm looking for a way to find out if there is a "code available" between the records in the table and show it in a TextBox, but I'm not able to organize the idea to do this query. I'm using Linq. Could someone help me with this? Hugs to all.

c# linq-to-entities

asked by anonymous 23.08.2018 / 15:18

1 answer

Concatenate .sass files with Compass Generation of SHA1 - Another way to do it?

score 0 · Accepted Answer

Although this does not seem to me to be the solution to your problem, this is an answer to your question.

See two examples of how to find the first interval / hole in a set of codes represented as string , but which can be converted to integers.

Using Linq , Range() and Except() with just a few additional validations.

string[] codigos = { "01", "03", "05", "04" };

List<int> codigosConvertidos = codigos.Select(x => int.Parse(x)).ToList();
int intervaloEncontrado = 1;
int valorMaximo = codigosConvertidos.Max();

if (valorMaximo > 0)
    intervaloEncontrado = Enumerable.Range(codigosConvertidos.Min(), codigosConvertidos.Count()).Except(codigosConvertidos).FirstOrDefault();                                   

int primeiroCodigoDisponivel = intervaloEncontrado > 0 ? intervaloEncontrado : valorMaximo + 1;

string resultado = primeiroCodigoDisponivel.ToString().PadLeft(2, '0');

And below I leave another example using Linq just to sort the vector

string[] codigos = { "01", "03", "05", "04" };

var inteirosOrdenados = codigos.Select(x => int.Parse(x)).OrderBy(x => x).ToArray();

int? codigoAnterior = null;
int? intervaloEncontrado = null;

int tamanhoVetor = inteirosOrdenados.Length;
for (int i = 0; i < tamanhoVetor; i++)
{
    if (codigoAnterior != null)
    {
        int intervaloEsperado = inteirosOrdenados[i] - 1;

        if (intervaloEsperado != codigoAnterior)
        {
            intervaloEncontrado = intervaloEsperado;
            break;
        }
        else
        {
            codigoAnterior = inteirosOrdenados[i];
        }
    }
    else
    {
        codigoAnterior = inteirosOrdenados[i];
    }

}

if (intervaloEncontrado == null)
    intervaloEncontrado = inteirosOrdenados[tamanhoVetor - 1] + 1;

string resultado = intervaloEncontrado.ToString().PadLeft(2, '0');