Reorder numerical sequence when changing array position

Question

I have a list of financial categories

My model

public class Categoria 
{
   public Guid Id {get;set;}
   public string Nome {get;set;}
   public int Sequencia {get;set;}
}

And as you move them up and down you need to update the Sequence property

var oldIndex = categoria.Sequencia;
var newIndex = categoria.Sequencia + 1 //Para cima
var newIndex = categoria.Sequencia - 1 //Para baixo

But I have no idea how to reorder the Sequencia field by updating its codes

Example, initial list:

1 - ÁGUA
2 - LUZ,
3 - Internet

You give a Up of the Internet, then it should update and stay in the following sequence

1 - ÁGUA
2 - Internet
3 - Luz

What I’ve tried so far:

    var categoria = await db.categorias.FirstOrDefaultAsync(x => x.Id == id);

    var categorias = await db.categorias.ToListAsync();

    var oldIndex = categoria.Sequencia;
    var newIndex = direcao == "UP" ? categoria.Sequencia + 1 : categoria.Sequencia - 1;

    foreach (var cat in categorias.Where(x => x.Sequencia > newIndex && x.Sequencia <= oldIndex && x.Id != categoria.Id))
    {
        cat.Sequencia = cat.Sequencia --;
    }
    category.Sequencia = newIndex;

Answer 1

As I mentioned in the comments, I would say to evaluate a better way to do this, because if the user clicks several times on the button, it is several calls to the database. I can’t suggest anything better here by message and without seeing your UI scenario, but surely there is a more efficient way to do this.

But I will try to help you in your current scenario.

var categoriaModificada1 = await db.categorias.FirstOrDefaultAsync(x => x.Id == id);

if (categoriaModificada1 != null)
{
    var novaSequencia = direcao == "UP" ? categoriaModificada1.Index - 1 : categoriaModificada1.Index + 1;
    var categoriaModificada2 = await db.categorias.FirstOrDefaultAsync(x => x.Index == novaSequencia);

    if (categoriaModificada2 != null)
    {
        categoriaModificada2.Index = categoriaModificada1.Index;
        categoriaModificada1.Index = novaSequencia;
        db.SaveChanges();            
    }
}

I made this code snippet based on your current scenario and your example above:

1 - ÁGUA
2 - LUZ,
3 - Internet

You give an Up from the Internet, then you should update and stay on the following sequence

1 - ÁGUA
2 - Internet
3 - Luz