Automatically order the SNo in table using LINQ

Question

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

score 1 · Answer 1 · answered May 03 '11 at 14:38

1

When rendering the data, you could increment a counter and render that as the row number.
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.

answered May 03 '11 at 14:38

Jaimal Chohan

8,530
6
43
64

how can i do the incrementing of counter through linq query ? Can i have code for that ? – Sreedhar Danturthi May 03 '11 at 14:41
@user why you want to use LINQ in this unfitting situation. LINQ is better used for querying data instead of doing incrementing and updating fields. – Shekhar_Pro May 03 '11 at 14:44

score 1 · Answer 2 · answered May 03 '11 at 14:41

You can do this with OnRowDataBound Event and TemplateField.

Do this:

Add OnRowDataBound="GridView1_RowDataBound" inside your GridView declaration:

Add TemplateField inside your GridView:

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

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)

Till · Accepted Answer · 2011-05-03T15:57:08.443

1

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

edited May 03 '11 at 15:57

answered May 03 '11 at 14:47

Till

3,084
17
18

model1 is just the name of the Linq2SQL data class model i used in my test-app – Till May 03 '11 at 15:58
no it is giving me error it is saying "Operator '+' cannot be applied to operands of type 'WebStoreWithLinqSql.Component' and 'int' – Sreedhar Danturthi May 03 '11 at 16:57
Are you sure you've implemented my suggestion correctly? It works just as expected on my end. Post your implementation and I'll have a look at it tomorrow. – Till May 03 '11 at 21:04
I have asked the question again with specific details of my project. Here is the question http://stackoverflow.com/questions/5873582/ordering-the-row-number-using-linqtosql-in-asp-net-c and it is solved. Please take a look at it. Thanks for the help and immediate feedback. Appreciate it !! Have a nice day !! – Sreedhar Danturthi May 03 '11 at 21:49

Automatically order the SNo in table using LINQ

3 Answers3