none
How to convert dataset to List in C# using linq RRS feed

  • 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
    Wednesday, January 20, 2010 2:50 PM

Answers

  • 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 msdnmg@microsoft.com


    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.
    • Marked as answer by adiel Thursday, January 28, 2010 7:42 PM
    Friday, January 22, 2010 1:22 AM
    Moderator

All replies

  • 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.

    Wednesday, January 20, 2010 3:14 PM
  • 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

    Wednesday, January 20, 2010 4:33 PM
  • 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 msdnmg@microsoft.com


    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.
    Thursday, January 21, 2010 3:35 AM
    Moderator
  • 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
    Thursday, January 21, 2010 4:58 PM
  • 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 msdnmg@microsoft.com


    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.
    • Marked as answer by adiel Thursday, January 28, 2010 7:42 PM
    Friday, January 22, 2010 1:22 AM
    Moderator
  • 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 msdnmg@microsoft.com


    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.
    Monday, January 25, 2010 1:07 AM
    Moderator
  • That is exactly what I was looking for!  Thanks Again,

    Adiel
    Thursday, January 28, 2010 7:42 PM
  • 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 msdnmg@microsoft.com


    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.
    Friday, January 29, 2010 2:44 AM
    Moderator
  • 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

    Thursday, September 16, 2010 8:31 PM
  • 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
    Thursday, September 16, 2010 8:32 PM
  • 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

    Wednesday, May 30, 2012 8:38 AM
  • Thanks for your solutions.

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

    Thursday, September 6, 2012 1:59 PM
  • 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

    Wednesday, March 6, 2013 6:22 AM
  • There is very simpel way, Check the below link -
    http://vsstack.blogspot.in/2013/09/convert-datatable-to-list-in-c.html
    Sunday, September 15, 2013 8:39 AM