none
LINQ to dataset: modifying datarows before 'select' RRS feed

  • Question

  • I have a dt with 3 columns: MyPK, VARCHAR01, VARCHAR02.

    Foreach row, if VARCHAR01 or VARCHAR02 has the value "" (i.e. String.Empty) I want to replace that with NULL (which the underlying type allows).

    I would normally do this as follows:

    var MyCleanedDatarows = 
          from o in ds.Tables["dt"].AsEnumerable() 
          select new { 
                MyPK = o.Field<string>("MyPK"), 
                VARCHAR01 = (o.Field<string?>("VARCHAR01") == "" ? NULL : o.Field<string?>("VARCHAR01") ), 
                 VARCHAR02 = (o.Field<string?>("VARCHAR02") == "" ? NULL : o.Field<string?>("VARCHAR02") ) 
               }; 
    











    ...but is there a way to interrogate/update column values for a row BEFORE the 'select'..??



    T Sadikali
    Thursday, January 14, 2010 2:16 PM

Answers



  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable table = new DataTable("MyTable");
                DataColumn[] columns = new DataColumn[3]
                      {new DataColumn("MyPk", typeof(String)),
                       new DataColumn("VarChar01", typeof(String)),
                       new DataColumn("VarChar02", typeof(String))};
                table.Columns.AddRange(columns);
    
                //Add rows for demonstration
                table.Rows.Add("1", "", "");
                table.Rows.Add("2", "blah", "blah");
                table.Rows.Add("3", "", "Hobo");
                table.Rows.Add("4", "", "Jill");
                table.Rows.Add("5", "Jack", "");
                table.Rows.Add("6", "", "Joe");
                table.Rows.Add("7", "BigBlah", "");
    
                var cleanedDataRows = table.AsEnumerable()
                    .Select(i => new
                    {
                        MyPk = i.Field<String>("MyPk"),
                        VarChar1 = i[1].ToString() == String.Empty ? null : i.Field<String>(1),
                        VarChar2 = i[2].ToString() == String.Empty ? null : i.Field<String>(2)
                    });
                foreach (var item in cleanedDataRows)
                {
                    //they will be replaced
                }
            }
        }
    }
    

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, January 14, 2010 9:10 PM
  • Hello Tamim,

     

    I agree with John and his post is helpful.   Here, for your question “is there a way to interrogate/update column values for a row BEFORE the ‘select’”, I think we can use the let operator to retrieve certain column value before the select.   However, for updating the column values, I don’t think a LINQ query is a good option because main goal of LINQ query is to pick values instead of updating values in the original collection.

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

    var MyCleanedDatarows = from o in db.Tables[“dt”].AsEnumerable()

                                                let varchar1 = o[“VARCHAR01”].ToString() == String.Empty ? null : o.Field<string>(“VARCHAR01”)

                                                let varchar2 = o[“VARCHAR02”].ToString() == String.Empty ? null : o.Field<string>(“VARCHAR02”)

                                                // other operations like where, order by, and etc (we can the variables varchar1and varchar2here)

                                                select new { MyPK = o.Field<string>(“MyPK”), VARCHAR01 = varchar1, VARCHAR02 = varchar2 };

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

     

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

     

    Have a nice weekend, 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.
    Friday, January 15, 2010 1:38 AM
    Moderator

All replies



  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                DataTable table = new DataTable("MyTable");
                DataColumn[] columns = new DataColumn[3]
                      {new DataColumn("MyPk", typeof(String)),
                       new DataColumn("VarChar01", typeof(String)),
                       new DataColumn("VarChar02", typeof(String))};
                table.Columns.AddRange(columns);
    
                //Add rows for demonstration
                table.Rows.Add("1", "", "");
                table.Rows.Add("2", "blah", "blah");
                table.Rows.Add("3", "", "Hobo");
                table.Rows.Add("4", "", "Jill");
                table.Rows.Add("5", "Jack", "");
                table.Rows.Add("6", "", "Joe");
                table.Rows.Add("7", "BigBlah", "");
    
                var cleanedDataRows = table.AsEnumerable()
                    .Select(i => new
                    {
                        MyPk = i.Field<String>("MyPk"),
                        VarChar1 = i[1].ToString() == String.Empty ? null : i.Field<String>(1),
                        VarChar2 = i[2].ToString() == String.Empty ? null : i.Field<String>(2)
                    });
                foreach (var item in cleanedDataRows)
                {
                    //they will be replaced
                }
            }
        }
    }
    

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, January 14, 2010 9:10 PM
  • By the way, the Nullable structure supports using only a value type as a nullable type because reference types are nullable by design.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, January 14, 2010 9:34 PM
  • Hello Tamim,

     

    I agree with John and his post is helpful.   Here, for your question “is there a way to interrogate/update column values for a row BEFORE the ‘select’”, I think we can use the let operator to retrieve certain column value before the select.   However, for updating the column values, I don’t think a LINQ query is a good option because main goal of LINQ query is to pick values instead of updating values in the original collection.

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

    var MyCleanedDatarows = from o in db.Tables[“dt”].AsEnumerable()

                                                let varchar1 = o[“VARCHAR01”].ToString() == String.Empty ? null : o.Field<string>(“VARCHAR01”)

                                                let varchar2 = o[“VARCHAR02”].ToString() == String.Empty ? null : o.Field<string>(“VARCHAR02”)

                                                // other operations like where, order by, and etc (we can the variables varchar1and varchar2here)

                                                select new { MyPK = o.Field<string>(“MyPK”), VARCHAR01 = varchar1, VARCHAR02 = varchar2 };

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

     

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

     

    Have a nice weekend, 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.
    Friday, January 15, 2010 1:38 AM
    Moderator
  • Hope you are well Lingzhi
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, January 15, 2010 4:56 AM
  • Hello Tamim,

     

    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.
    Tuesday, January 19, 2010 1:24 AM
    Moderator