How to convert dataset to List in C# using linq

Question

I have two columns in a dataset that I want to convert to a list in C#:

firstname
lastname

I want to create a method that will return a list of strings:

public List<string> getFullName(int company)
...
    //this method will read the dataset (firstname and lastname columns) and copy them to a string list using LINQ

I know how to do this using a loop through the dataset and then adding each item to the list inside the loop.

BUT, my question is: Is there a way to do this using LINQ without having to loop through the dataset?
(basically move firstname + ' ' + lastname rows from dataset to string list)

Thanks Before Hand,
Adiel

Answer 1

Yes You can do like this

The below Linq Query is written using VB.Net, convert the same to C#.

Dim NameList  = From Tab In DataSet.TableName Select Tab.FirstName + " " + Tab.LastName;

Thanks

---

Best Regards,
Gopi V

If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

Answer 2

Thanks Gopi, this is what I have so far:

        public List<string> getFullName(int company)
        {
            string sql;
            SqlDataAdapter da;
            DataSet ds;

            // Setup SQL
            sql = "select * " +
                  "from TblCustomers " +
                  "where company = " + company;

            // Setup DataAdapter
            da = new SqlDataAdapter(sql, cn);
            // Instantiate a new dataset object
            ds = new DataSet();
            // Execute the command and assign to dataset
            da.Fill(ds, "Default");

            // Move names from dataset to list using LINQ
            List<string> NameList = from Tab1 in ds.Tables["Default"] select Tab1.firstname + " " + Tab1.lastname;

            // Send Back Records
            return NameList;

        }


I am getting an error in the folllowing line:
// Move names from dataset to list using LINQ
List<string> NameList = from Tab1 in ds.Tables["Default"] select Tab1.firstname + " " + Tab1.lastname;

The error is :

"Cannot convert lambda expression to type 'string' because it is not a delegate type"

And it is highlighting the select keyword in that line.

Thanks,
Adiel

Answer 3

Hello Adiel,

 

Welcome to ADO.NET DataSet forum!

 

What we need here is the LINQ to DataSet query, so we need to call DataTable.AsEnumerable() method to get a IEnumerable<DataRow> collection.   Gopi V’s post is helpful on this case.  However, he used VB.NET codes and the VB.NET compiler will automatically add the AsEnumerable() method on the DataTable if we do not write it.   J

 

So your sample codes just need a little modification:

===========================================================================================

List<string> NameList = (from r in ds.Tables["Default"].AsEnumerable()

                         select r.Field<string>("FirstName") + r.Field<string>("LastName")).ToList();

===========================================================================================

 

Also, since you need a List<String> collection, we need to call the ToList() method to convert the IEnumerable<String> to List<String> here.  

 

If you have any questions, please feel free to let me know.

 

Have a great day, both!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Lingzhi, that is amazing! That was exactly what I was looking for. :)  One more question if you don't mind, if instead of saving the outcome to a LIST I wanted to save it to a STRING of names comma delimited how would I do that? I tried this:

string NameList = from g in ds.Tables["Default"].AsEnumerable()
                              select g.Field<string>("firstname") + " " +
                                     g.Field<string>("lastname");

But now I get an error:

Cannot implicitly convert type 'System.Data.EnumerableRowCollection<string>' to 'string'

Thanks Again,
Adiel

Answer 5

Hello Adiel,

 

I am glad that my post is helpful to you.  For the new question, I think what you want is the Aggregate method to build up all the names into a name list which is delimited by comma.  Here are the sample codes:

================================================================================================

string NameList = (from r in ds.Tables["Default"].AsEnumerable()

                   select r.Field<string>("FirstName") + " " + r.Field<string>("LastName")).Aggregate((s1, s2) => s1 + "," + s2);

================================================================================================

 

Does this solve the problem?   If you need any further assistance, please feel free to let me know.

 

Have a nice weekend!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

Hello Adiel,

 

I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

 

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 7

That is exactly what I was looking for!  Thanks Again,

Adiel

Answer 8

It's my pleasure!  :) 

Have a nice weekend!
 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 9

I don´t get those erros anymore but I´m getting The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[System.String]', but this dictionary requires a model item of type 'System.Collections.Generic.List`1[MvcApplication1.Models.Roger]'.

What should I do then to solve it?

I did all that because I have

RogerDBEntities _db;
 _db = new RogerDBEntities();

ViewData.Model = _db.Roger.ToList(); and I´d like to be able to choose what to have in ToList

Any ideas?
thanks

Answer 10

I don´t get those erros anymore but I´m getting The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[System.String]', but this dictionary requires a model item of type 'System.Collections.Generic.List`1[MvcApplication1.Models.Roger]'.

What should I do then to solve it?

I did all that because I have

RogerDBEntities _db;
 _db = new RogerDBEntities();

ViewData.Model = _db.Roger.ToList(); and I´d like to be able to choose what to have in ToList

Any ideas?
thanks

Answer 11

Hi Lingzhi Sun,

I tried your code i am getting new error like "system.data.enuramablerowcollection<string> doesnot contain defination method aggreate  accepting first argument of type system.data.enuramablerowcollection<string>

List<string> lststring = new List<string>();

lststring = (from r in objDataSet.Tables["Default"].AsEnumerable()
                                   select r.Field<string>("FirstName") + " " + r.Field<string>("LastName")).Aggregate((s1, s2) => s1 + "," + s2);

Error pointing at Aggreate

---

Manjunath

Answer 12

Thanks for your solutions.

can we mention "FirstName" which is column name in datatable dynamically as much as column exists in datatable.

Answer 13

Hello Michael,

According to your solution i had applied code for list also and its working fine my code is as below :

List<string> NameList = (from r in ds.Tables["Default"].AsEnumerable()

                         select r.Field<string>("FirstName") + r.Field<string>("LastName")).ToList();

And its working Fine for me now i want little changes in it i want to use Dictionary<string.string> instead of using list.

So please help me to convert this code for Dictionary.

Thanks

---

sumitk