DataTable select with substring

Question

Hi

I have a datatable with data like:

col1          col2                              col3              col4

test        test data                        50                true

test        test result  - master       100              true

test        test result - number1      75                true

test        test result - number2     100               true

test        test test                         100              true

test        not test                          100              false

How can I select rows which col 4 is true and distinct col2 where if it contains '-' only take the text before the '-' e.g. I would be left with:

col1          col2                              

test        test data                       

test        test result    

test        test test                         

Thanks

Paul

Answer 1

Hi Paul,

Thank you for posting here.

We can modify the data using loops first and then use linq to  find the appropriate data.

            foreach (DataRow item in dataTable.Rows)
            {
                if (item["col2"].ToString().StartsWith("test result"))
                {
                    item["col2"] = "test result";
                }
            }

            var re1 = dataTable.AsEnumerable().Where(row => Convert.ToBoolean(row["col4"]) == true).Distinct(new DataRowComparer());
            var re = re1.CopyToDataTable();

        public class DataRowComparer : IEqualityComparer<DataRow>
        {
            public bool Equals(DataRow row1, DataRow row2)
            {
                return (row1.Field<string>("col2") == row2.Field<string>("col2"));
            }
            public int GetHashCode(DataRow t)
            {
                return t.ToString().GetHashCode();
            }
        }

Best Regards,

Timon

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi

Try the bellow code

 
  var b = "true";
var re1 = (from d in dataTable.AsEnumerable()
                         where d.Field<string>("col4") == b  //If type is boolean then pass boolean
                         select new
                         {
                            
                             c1 = d.Field<string>("col2").ToString().Contains("-") ? d.Field<string>("col2").ToString().Substring(0, d.Field<string>("col2").ToString().IndexOf("-")) : d.Field<string>("col2").ToString()
                           

                         }).Distinct().ToList();

hanks and regards

Answer 3

Hi,

Has your issue been resolved?

If so, please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

Best Regards,

Timon

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].