Search for missing record range

Asked

Viewed 108 times

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

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

c# linq-to-entities

  • Table.Firstordefault(o => o.Code == "02"); // selects the code "02" or null (if it does not exist) Table.Select(o => o.Code). Tolist() // selects all codes

  • The Codigo always accompanies the Id? That is, a possible Id 9 will have the code "09" or it can have an Id 9 and code "11" (for example)?

  • John, yes, Id has nothing to do with the Code.

  • So how can one know that there is a "fault" between the codes? The code is sequential?

  • No, the code isn’t sequential. Just what I need is this, in the table records, from the first, to find which record where there is a sequence "break". I found a solution, but the code is in SQL, I needed to "convert" to Linq, but I’m not getting it. I’ll leave it here, maybe you or another colleague can help me: Select t1.codigogroup + 1 as free FROM group AS t1 LEFT JOIN group AS t2 on t1.codigogroup + 1 = t2.codigogroup Where t2.codigogroup IS NULL AND t1.codigogroup > 1

  • 1

    You will need to create a set of all the intermediate items between the smallest and largest code you have, and then make a selection of which ones do not exist in your original set, where the gaps would be.

  • In case, would id item 5 receive code "02"? because you need this sequence with this weak interface?

  • I think that that question is related to the same problem, but with the perspective of the bank instead of Linus

Show 3 more comments

1 answer

0


Although I don’t think this is the solution to your problem, this is an answer to your question.

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

Using Linq, Range() and Except() with only 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 the 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');

Browser other questions tagged c# linq-to-entities

You are not signed in. Login or sign up in order to post.