Automatically order the SNo in table using LINQ

I have couple of rows in database table (lets call it Customer), each row is numbered by SNo which gets automatically incremented by the identity property inherent in MS SQLServer. But when i delete a particular row that particular row number is left blank but I want the table to auto correct itself.

To give you a example:

I have a sample Customer Table with following rows:

SNo CustomerName Age

1   Dani         28
2   Alex         29
3   Duran        21
4   Mark         24

And suppose I delete 3rd row the table looks like this:

SNo CustomerName Age

1   Dani         28
2   Alex         29
4   Mark         24

But I want the table to look like this:

SNo CustomerName Age

1   Dani         28
2   Alex         29
3   Mark         24

How can i achieve that using LINQ to SQL in c#? This is for presentation in gridview only, i don't want the SNo to be changed in database table, just want to show the right order to users.

I'm using the following lines of code:

(db.Components.AsEnumerable().Select((iterator)=> new{iterator.SNo + 1})

But i'm not getting any result, how can i achieve it, i just want the sno to be incremented regardless of the value in database table

Please help me out

Thanks in anticipation

PS: please dont flag it or downvote for some reason u might delete after getting answered

This does not alter your SNo but adds a counter (CustomerIterator) to the objects returned by LINQ.

var customers = model1.Customers.AsEnumerable()
  .OrderBy(x => x.SNo)
  .Select((x, iterator) => new { x.SNo, CustomerIterator = iterator + 1, x.CustomerName, x.Age });

The incrementation is not done by the "iterator + 1" part but by the Select overload found here: http://msdn.microsoft.com/en-us/library/bb534869.aspx. I just added the + 1 because the counter starts at 0

1. When rendering the data, you could increment a counter and render that as the row number.

2. If you need to expose the data for consumption, you could add a new "row number" property to your class, then after fetching the data from the database iterate over the data, incrementing the new property.

But this seems like a rendering issue, so I would go with 1.

You can do this with OnRowDataBound Event and TemplateField.

Do this:

1. Add OnRowDataBound="GridView1_RowDataBound" inside your GridView declaration:

2. Add TemplateField inside your GridView:

               <asp:TemplateField HeaderText="Serial number">
                   <ItemTemplate>
                       <asp:Label ID="lblSerial" runat="server"></asp:Label>
                   </ItemTemplate>
               </asp:TemplateField>
   

3. Add this in code-behind:

   int i = 1;
   protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
   {
       if (e.Row.RowType == DataControlRowType.DataRow)
       {
           Label lblSerial = (Label)e.Row.FindControl("lblSerial");
           lblSerial.Text = i.ToString();
           i++;
       }
   }
   

(source)