Ordering the row_number using LinqToSql in asp.net/C#

Question

I have set of records in a database table lets call it Components Table which is defined as follows.

The administrator can disable some of the components using disableflag which is the last column of the table. If a particular component is disabled it should not appear in the gridview of the user.

I'm getting the data from the database and presenting through the gridview as shown here, if you observe the SNo values are not in order.

The linq query that i'm using to retrieve the data is:

var gridViewResults = from results in db.Components where results.DisableFlag == false
               select  new { SNo = results.SNo, ComponentNames = results.Component_Name, Size =   results.Size__in_MB_, Price = results.Price__in_SEK_, TotalDownloads = results.Total_Downloads, Description = results.Description };

But I want the data to be shown in order meaning with SNo to be 1, 2, 3, 4 with out dependency on the database table SNO values: for reference look at this.

I'm not able to figure out how to use the linq query to achieve this:

I have tried this query:

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

But i think it is absurd. Can some one help me out on this.

Thanks in anticipation.

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

If you're absoutely certain you want to ignore the database numbers (why output the numbers if they don't actually correspond to anything?) you may be able to try the following:

var gridViewData = from results in db.Components
                   where results.DisableFlag == false
                   select new
                   {
                     ComponentNames = results.Component_Name,
                     Size = results.Size__in_MB_,
                     Price = results.Price__in_SEK_,
                     TotalDownloads = results.Total_Downloads,
                     Description = results.Description
                   };

var gridViewResults = gridViewData.AsEnumerable().Select((item, index) => new
                      {
                         SNo = index + 1,
                         ComponentNames = item.ComponentNames,
                         Size = item.Size,
                         Price = item.Price,
                         TotalDownloads = item.TotalDownloads,
                         Description = item.Description
                      });

EDIT: Alternate solution from How To Project a Line Number Into Linq Query Results

EDIT2: Fix for unsupported select by SQL: Linq error - "NotSupportedException: Unsupported overload used for query operator 'Select'"

edited May 23 '17 at 12:14

Community

1
1

answered May 03 '11 at 19:49

Joshua

8,112
3
35
40

@Joshuw my intention is to get the output onto gridview, how can i project this output in gridview – Sreedhar Danturthi May 03 '11 at 20:00
@Joshua "Property or indexer 'AnonymousType#1.SNo' cannot be assigned to -- it is read only" this is the error i'm getting in the line 'item.SNo = SNo'. Please help me out !! – Sreedhar Danturthi May 03 '11 at 20:04
Try the latest version. Part of the problem is the use of anonymous types, they need an intermediate step to massage the data that you need. Creating an actual class to hold the information would make it a little simpler. Also, the `gridViewResults` will be an enumeration of the anonymous type, so you'll use it on the grid view like you already have. – Joshua May 03 '11 at 20:09
@Joshua http://postimage.org/image/mqc2rrxg/ This is the error i'm getting if i use the code that was edited – Sreedhar Danturthi May 03 '11 at 20:15
@user653622: It should be fixed now, see the link for EDIT2 for the reason. – Joshua May 03 '11 at 20:18
@Joshua http://postimage.org/image/mu3ou8f8/ this is the error i'm getting i don't understand it. Help me out. Thanks in anticipation – Sreedhar Danturthi May 03 '11 at 20:23
@Joshua just have to add tolist to the end of second query. Any ways finally it works. Thank you so much and have a nice day !!! – Sreedhar Danturthi May 03 '11 at 20:37

score 1 · Answer 2 · answered May 03 '11 at 20:33

Hi everyone here is the final answer. Joshua did all of the work. A big thanks to him. Just want to highlight the answer to anyone with the same problem for the future. If any one want to vote up please vote for Joshua

var gridViewData = from results in db.Components
                           where results.DisableFlag == false
                           select new
                           {
                               ComponentNames = results.Component_Name,
                               Size = results.Size__in_MB_,
                               Price = results.Price__in_SEK_,
                               TotalDownloads = results.Total_Downloads,
                               Description = results.Description
                           };

        var gridViewResults = gridViewData.AsEnumerable().Select((item, index) => new
        {
            SNo = index + 1,
            ComponentNames = item.ComponentNames,
            Size = item.Size,
            Price = item.Price,
            TotalDownloads = item.TotalDownloads,
            Description = item.Description
        }).ToList();

This should work.

Ordering the row_number using LinqToSql in asp.net/C#

2 Answers2

Linked