Optimizing DataTable by removing duplicate entries

Question

I have a DataTable shown below:

  RefID     RefDescription      ReferenceUrl                  SortOrder
    0       Interdiscip       http://www.ncbi.nlm.nih.gov/      1
    0       Entropy 20133     http://www.mdpi.com/1099-4300     2
    0       Interdiscip       http://www.ncbi.nlm.nih.gov/      3
    0       Agriculture       http://www1.agric.gov.ab.ca/      4
    0       Interdiscip       http://www.ncbi.nlm.nih.gov/      5

In the above dataset if we have value of RefDescription and ReferenceUrl are same remove those duplicate rows and keep the single row, also append the numbers to it, according to sort Order.

Here, in the above dataSet-- RefDescription: Interdiscip is repeated three times and also it has ReferenceUrl same. so i want the following output as dataset.

Result i want:

 RefID     RefDescription      ReferenceUrl                  SortOrder
   0       3,5 Interdiscip    http://www.ncbi.nlm.nih.gov/      1
   0        Entropy 20133     http://www.mdpi.com/1099-4300     2
   0        Agriculture       http://www1.agric.gov.ab.ca/      4

Note:RefDescription and ReferenceUrl both should have same values in this scenarios otherwise no need to remove and append.

C# Code: i Tried

protected void   Page_Load(object sender, EventArgs e)
    {
        int rowcount = 0;
        DataTable dt = new DataTable("ReferenceData");
        dt.Columns.Add("ReferenceID");
        dt.Columns.Add("ReferenceDescription");
        dt.Columns.Add("ReferenceUrl");
        dt.Columns.Add("SortOrder");
        dt.Rows.Add("0","Interdiscip","http://www.ncbi.nlm.nih.gov/","1");
        dt.Rows.Add("0", "Entropy 20133", "http://www.mdpi.com/1099-4300", "2");
        dt.Rows.Add("0", "Interdiscip", "http://www.ncbi.nlm.nih.gov/", "3");
        dt.Rows.Add("0", "Agriculture", "http://www1.agric.gov.ab.ca/", "4");
        dt.Rows.Add("0", "Interdiscip", "http://www.ncbi.nlm.nih.gov/", "5");
        DataSet ds = new DataSet();
        DataTable dtOut = null;
        ds.Tables.Add(dt);
        DataView dv = dt.DefaultView;
        dv.Sort = "ReferenceDescription,ReferenceUrl";
        dtOut = dv.ToTable();

        for (int t = 0; t < dtOut.Rows.Count; t++)
        {
            int i = 0;
            int count = 0;
            int sortorder = 0;
            string space = null;
            string x = dtOut.Rows[t][1].ToString();
            string y = dtOut.Rows[t][2].ToString();
            sortorder = Convert.ToInt32(dtOut.Rows[rowcount][3]);
            for (int j = 0; j < dtOut.Rows.Count; j++)
            {
                if (x == dtOut.Rows[i][1].ToString() && y == dtOut.Rows[i][2].ToString())
                {
                    count++;
                    if (count > 1)
                    {
                        sortorder = Convert.ToInt32(dtOut.Rows[i][3]);
                        space += sortorder + " ";
                        dtOut.Rows[i].Delete();
                        dtOut.AcceptChanges();
                    }
                }

                i++;
            }
            dtOut.Rows[rowcount][1] = space + x;
            rowcount++;
        }

    }

Btw, you have shown what you've tried but you haven't mentioned what's wrong with it. — Tim Schmelter, Jan 06 '15 at 12:06

Tim Schmelter · Accepted Answer · 2015-01-06T12:45:28.780

1

You can use Linq-To-DataSet:

var grps = from row in dt.AsEnumerable()
           let RefDescription = row.Field<string>("RefDescription")
           let ReferenceUrl = row.Field<string>("ReferenceUrl")
           group row by new { RefDescription, ReferenceUrl } into groups
           select groups;
dt = grps.Select(g => 
     {
        DataRow first = g.First();
        if (g.Skip(1).Any())
        {
            // duplicates
            string otherSortOrders = String.Join(",", g.Skip(1).Select(r => r.Field<int>("SortOrder")));
            first.SetField("RefDescription", string.Format("{0} {1}",
                otherSortOrders,
                g.Key.RefDescription));
        }
        return first;
     })
    .CopyToDataTable();

or with one query in pure method syntax (i prefer a combination):

dt = dt.AsEnumerable()
    .GroupBy(r => new {
        RefDescription = r.Field<string>("RefDescription"),
        ReferenceUrl = r.Field<string>("ReferenceUrl")
    })
    .Select(grp =>
    {
        DataRow first = grp.First();
        if (grp.Skip(1).Any())
        {
            // duplicates
            string otherSortOrders = String.Join(",", grp.Skip(1).Select(r => r.Field<int>("SortOrder")));
            first.SetField("RefDescription", string.Format("{0} {1}",
                otherSortOrders,
                grp.Key.RefDescription));
        }
        return first;
    }).CopyToDataTable();

edited Jan 06 '15 at 12:45

answered Jan 06 '15 at 11:18

Tim Schmelter

450,073
74
686
939

wat?? i didnt get that ..C#3 tag..?? – Jan 06 '15 at 11:31
check http://stackoverflow.com/questions/27816728/modifying-and-updating-linkhref-which-is-bind-to-the-repeater-from-datatable ..and please give some solution.. – Jan 07 '15 at 11:43
@Pranav: in .NET 3.5 there was no overload of [`String.Join`](http://msdn.microsoft.com/en-us/library/dd992421(v=vs.110).aspx) that takes an `IEnumerable` but only one which takes a `String[]`. So you can use `ToArrray` first: `String.Join(",", g.Skip(1).Select(r => r.Field("SortOrder").ToString()).ToArray())` – Tim Schmelter Jan 08 '15 at 09:13
Can we trim the spaces when comparing the strings with LINQ..?? – Jan 08 '15 at 11:20
http://stackoverflow.com/questions/27936466/merge-2-or-more-rows-of-a-column-if-other-column-has-same-values-text-in-sqlserv – Jan 14 '15 at 06:08
what if i dont want to use group by...? – Jan 21 '15 at 10:51
@Pranav: then you need a different apporach. Can you be more specific? – Tim Schmelter Jan 21 '15 at 10:53
here what we have done is by grouping those 2 columns we are getting 1 of them as the are same...but i want both of them to be in datatable – Jan 21 '15 at 11:04
@Pranav: i'm afraid that you need to post a question with a meaningful title and desciption and also a little sample. Otherwise it's difficult to help. – Tim Schmelter Jan 21 '15 at 11:07

score 0 · Answer 2 · answered Jan 06 '15 at 11:48

I did few changes try this now if it solves the issue...

        int rowcount = 0;
        DataTable dt = new DataTable("ReferenceData");
        dt.Columns.Add("ReferenceID");
        dt.Columns.Add("ReferenceDescription");
        dt.Columns.Add("ReferenceUrl");
        dt.Columns.Add("SortOrder");

        dt.Rows.Add("0", "Interdiscip", "http://www.ncbi.nlm.nih.gov/", "1");
        dt.Rows.Add("0", "Entropy 20133", "http://www.mdpi.com/1099-4300", "2");
        dt.Rows.Add("0", "Interdiscip", "http://www.ncbi.nlm.nih.gov/", "3");
        dt.Rows.Add("0", "Agriculture", "http://www1.agric.gov.ab.ca/", "4");
        dt.Rows.Add("0", "Interdiscip", "http://www.ncbi.nlm.nih.gov/", "5");
        DataSet ds = new DataSet();
        DataTable dtOut = null;
        ds.Tables.Add(dt);

        DataView dv = dt.DefaultView;
        dv.Sort = "ReferenceDescription,ReferenceUrl";
        dtOut = dv.ToTable();

        dt = dv.ToTable();

        for (int t = 0; t < dtOut.Rows.Count; t++)
        {
            int i = 0;
            int count = 0;
            int sortorder = 0;
            string space = null;
            string x = dtOut.Rows[t][1].ToString();
            string y = dtOut.Rows[t][2].ToString();
            sortorder = Convert.ToInt32(dtOut.Rows[rowcount][3]);
            for (int j = 0; j < dtOut.Rows.Count; j++)
            {
                if (x == dtOut.Rows[j][1].ToString() && y == dtOut.Rows[j][2].ToString())
                {
                    count = dtOut.AsEnumerable().Where(s => s.Field<string>("ReferenceDescription").EndsWith(x) && s.Field<string>("ReferenceUrl") == y).Count();

                    //count++;
                    if (count > 1)
                    {
                        sortorder = Convert.ToInt32(dtOut.Rows[j][3]);
                        space += sortorder + " ";
                        dtOut.Rows[j].Delete();
                        dtOut.AcceptChanges();
                    }
                }

                i++;
            }
            dtOut.Rows[rowcount][1] = space + x;
            rowcount++;
        }

Optimizing DataTable by removing duplicate entries

2 Answers2