none
How obtain single result from LINQ to DataSet query? RRS feed

  • Question

  • I have a DataSet that is populated with a set of fields and a number of rows. For the purpose of this question, the two most interesting are Name and XMLData, both holding string values.

    What I want to do is write a LINQ query, which has the following properties:

    * Returns only a single row, based on the Name column (if there's a duplicate name, that's a bug in another part of the system)
    * Returns the value of a single column, specifically XMLData
    * Preferably returns the value of the XMLData column in the row found as a string, rather than the whole row as a DataRow object

    It's easy to do this relatively cleanly on an instance of List<T> (obviously, there's some error-handling code in there as well):

                var data = this.GetListTOfData().FindAll(dataentry => dataentry.Name.Equals(searchForName));
                return data[0].Something;

    but I can't seem to find any way to do something similar over a DataTable.

    I know there is DataTable.Select() but since the name field might very well contain exotic characters, I am a little ambivalent toward using that one, and iterating over the whole collection in my code seems rather old-fashioned (I would rather express intent than method when possible). The closest I have been able to come up with is calling AsEnumerable().Where() on the DataTable to create a LINQ query, then iterating over that in a foreach look containing only a return statement which gets executed once, on the row I am interested in. Surely there must be a better way?

    Any assistance would be greatly appreciated. I've Googled this for a while but all the examples I find deal with collections of multiple DataRows and iterating over them, which is unneeded in my case. If I am missing something, do feel free to point me in the appropriate direction.
    Tuesday, October 13, 2009 3:16 PM

Answers

  • Hi Michael,

     

    Welcome to ADO.NET DataSets forum!

     

    Do you want to retrieve an single object which contains three properties, a single row whose Name column is some specific value, a list of string represents the XMLData column values, and a single string value of the XMLData column of the row found by the Name column value?   If so, please refer to the following LINQ to DataSets codes (dt is the corresponding DataTable):

    ==========================================================================
                var rows = dt.AsEnumerable();

               

                var data = new

                {

                    NameRow = rows.Single(r => r.Field<string>("Name") == "Name1"),

                    xmlData = rows.Single(r => r.Field<string>("Name") == "Name1").Field<string>("XMLData"),

                    xmlColumn = rows.Select(r => r.Field<string>("XMLData"))

                };
    ==========================================================================

     

    If I misunderstand your question, please provide us with more detailed information.  I will do my best to figure out how to resolve the issue.  J

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    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.
    Wednesday, October 14, 2009 5:50 AM
    Moderator

All replies

  • Hi Michael,

     

    Welcome to ADO.NET DataSets forum!

     

    Do you want to retrieve an single object which contains three properties, a single row whose Name column is some specific value, a list of string represents the XMLData column values, and a single string value of the XMLData column of the row found by the Name column value?   If so, please refer to the following LINQ to DataSets codes (dt is the corresponding DataTable):

    ==========================================================================
                var rows = dt.AsEnumerable();

               

                var data = new

                {

                    NameRow = rows.Single(r => r.Field<string>("Name") == "Name1"),

                    xmlData = rows.Single(r => r.Field<string>("Name") == "Name1").Field<string>("XMLData"),

                    xmlColumn = rows.Select(r => r.Field<string>("XMLData"))

                };
    ==========================================================================

     

    If I misunderstand your question, please provide us with more detailed information.  I will do my best to figure out how to resolve the issue.  J

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    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.
    Wednesday, October 14, 2009 5:50 AM
    Moderator
  • Lingzhi,

    Thank you! The second example (your data.xmlData assignment) worked perfectly and was exactly what I was looking for. I actually ended up wrapping it in a single statement, so in the end it became:

    dt.AsEnumerable().Single<DataRow>(dataRow => dataRow.Field<string>("Name").Equals(searchForName)).Field<string>("XMLData");

    A quick test seems to suggest that this does exactly what I want, and the documentation appears to agree, so all is well.
    Wednesday, October 14, 2009 6:30 AM
  • Hi Michael,

    You are welcome!  I am very glad that my solution helps you.  J

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    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.
    Wednesday, October 14, 2009 9:01 AM
    Moderator