I want to add a unique identifier to each record in a dataset using Linq To Sql

Question

This is my reading:

    OnSiteV3DataContext _v3Context = new OnSiteV3DataContext();
        var dataset1 = (from sp in _v3Context.SQLPendingEvents
                        where 
                        (sp.EventType.ToString() == str100w)
                        select new
                        {

                            sp.KeyDeviceID,
                            sp.UpdateTime ,
                            sp.EventClass ,
                            sp.EventCode ,
                            sp.EventType ,
                            sp.EventDateTime ,
                            sp.KeyPropertyID,
                            sp.KeyEntityID,
                            sp.EventText,
                            sp.KeyUserID,
                            sp.EventImage,
                            sp.TaskType ,
                            sp.TaskSubType ,
                            sp.UniqueTaskID 
                        }).ToList();

    dataset1 = dataset1.Where(sp => !sp.KeyDeviceID.ToString().Contains('~')).ToList();
        gvSqlPendingEvent.DataSource = dataset1;
        gvSqlPendingEvent.DataBind();

Ther dataset does not have a unique identifier to identify individual records, so I would like to add it. I want the first column to be this identifier - it could be based on rowcount. How can I achieve this?

Answer 1

You can move the request request to the client side and use the overload List. Choose :

var dataset1 = _v3Context.SQLPendingEvents
                         .Where(sp => sp.EventType.ToString() == str100w)
                         .AsEnumerable() // further processing occurs on client
                         .Select((sp,index) => new {
                             ID = index + 1,
                             sp.KeyDeviceID,
                             sp.UpdateTime ,
                             sp.EventClass ,
                             sp.EventCode ,
                             sp.EventType ,
                             sp.EventDateTime ,
                             sp.KeyPropertyID,
                             sp.KeyEntityID,
                             sp.EventText,
                             sp.KeyUserID,
                             sp.EventImage,
                             sp.TaskType ,
                             sp.TaskSubType ,
                             sp.UniqueTaskID 
                         }).ToList();