Reorder data table rows with duplicate column value-CodePudding

I have a data table that I use to insert records into an Oracle DB. One of the columns in the data table is "Rank" and it needs to be unique. So I am trying to reorder the rows of the data table and if there is a duplicate I need to increment it and all following rows.

Col1    Col2    Rank
----    ----    ----
A       B       1
C       D       2
E       F       2
G       H       3

Needs to be changed to:

Col1    Col2    Rank
----    ----    ----
A       B       1
C       D       2
E       F       3
G       H       4

I think the following does the ordering of data table if they are out of order but not sure how to handle duplicate ranks and incrementing:

var dt = new DataTable();
dt.Columns.Add("Col1", typeof(string));
dt.Columns.Add("Col2", typeof(string));
dt.Columns.Add("Rank", typeof(int));

dt.Rows.Add("C", "D", 2);
dt.Rows.Add("G", "H", 3);
dt.Rows.Add("A", "B", 1);
dt.Rows.Add("E", "F", 2);

var dt2 = dt.AsEnumerable().OrderBy(x => x.Field<int>("Rank"));

This will give me first table, ordered. How do I get table 2?

Update

I think I omitted an important part!

I initially read the records from DB into a data table. This has records ranked correctly (1, 2, 3, ...).

I then add the new record that has, say, rank 2; so now I have two records with rank 2. But I need to make sure the new record retains rank 2 and succeeding records' rank get incremented. So I cannot blindly increment ranks otherwise I might end up with new record having rank 3, and so on.

CodePudding user response：

Well, why don't you simply assign a new value?

var dt2 = dt.Clone();
int rank = 0;
foreach (DataRow row in dt2.AsEnumerable().OrderBy(rank => rank.Field<int>("Rank")))
{
    row["Rank"] =   rank;
}

CodePudding user response：

With LINQ .Select() and assign index: i 1 to Rank column.

var dt2 = dt.AsEnumerable().OrderBy(x => x.Field<int>("Rank"))
            .Select((x, i) => 
                    {
                        x["Rank"] = i   1;
                        
                        return x;
                    })
            .ToList();

Demo @ .NET Fiddle