How to split One Datatable into Two (matched record) (unmatched record) using LINQ C#

Question

I have one Datatable, i need to group the record into two, datatable(matched) and datatable(unmatched). for a record to be matched, there must be two rows having the same RRN and the sum of the two AMOUNT is equal to zero.

i have achieved this using the code below.

  if (InputCollection.Rows.Count > 0)
            {
                for (int i = InputCollection.Rows.Count - 1; i-- > 0;)
                {
                    var selectedRecord = InputCollection.Rows[i];
                    if (selectedRecord["RRN"].ToString() != "")
                    {
                        for (int j = 0; j < InputCollection.Rows.Count - 1; j++)
                        {
                            if (i != j)
                            {
                                var thisRecord = InputCollection.Rows[j];
                                var selectedRRN = selectedRecord["RRN"].ToString();
                                var thisRRN = thisRecord["RRN"].ToString();
                                if (selectedRRN.Trim() == thisRRN.Trim())
                                {
                                    if (Decimal.Parse(selectedRecord["AMOUNT"].ToString()) + Decimal.Parse(thisRecord["AMOUNT"].ToString()) == 0)
                                    {
                                        MatchedOutput.ImportRow(InputCollection.Rows[i]);
                                        MatchedOutput.ImportRow(InputCollection.Rows[j]);

                                        InputCollection.Rows.Remove(InputCollection.Rows[j]);
                                        InputCollection.Rows.Remove(InputCollection.Rows[i]);
                                        break;
                                    }

                                }
                            }


                        }

                    }

The problem with the code is that it is too slow. How can i rewrite the code above using LINQ Queries in c#? thanks.

Answer 1

This is not simple and Linq is not the correct method to accomplish. See code below

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 dt = new DataTable();
            dt.Columns.Add("RRN", typeof(string));
            dt.Columns.Add("Amount", typeof(decimal));

            dt.Rows.Add(new object[] { 1, 1.0 });
            dt.Rows.Add(new object[] { 1, -1.0 });
            dt.Rows.Add(new object[] { 1, 2.0 });
            dt.Rows.Add(new object[] { 1, -2.0 });
            dt.Rows.Add(new object[] { 1, 3.0 });
            dt.Rows.Add(new object[] { 2, -3.0 });
            dt.Rows.Add(new object[] { 2, 1.0 });
            dt.Rows.Add(new object[] { 2, -1.0 });
            dt.Rows.Add(new object[] { 3, 1.0 });
            dt.Rows.Add(new object[] { 3, -1.0 });
            dt.Rows.Add(new object[] { 4, 1.0 });

            var groupRRN = dt.AsEnumerable().Select(x => new Match() { row = x, RRN = x.Field<string>("RRN"), amount = x.Field<decimal>("Amount"), matched = false }).GroupBy(x => x.RRN).ToList();

            DataTable matchedTable = dt.Clone();
            DataTable unmatchedTable = dt.Clone();

            foreach (var group in groupRRN)
            {
                for (int i = group.Count() - 1; i >= 1; i--)
                {
                    decimal amountI = group.Skip(i).First().amount;
                    for (int j = i - 1; j >= 0; j--)
                    {
                        Match matchJ = group.Skip(j).First();
                        if (!matchJ.matched)
                        {
                            decimal amountJ = matchJ.amount;
                            if (amountI + amountJ == 0)
                            {
                                group.Skip(i).First().matched = true;
                                group.Skip(j).First().matched = true;
                                break;
                            }
                        }
                    }
                }
                foreach (Match match in group)
                {
                    if (match.matched == true)
                    {
                        matchedTable.Rows.Add(match.row.ItemArray);
                    }
                    else
                    {
                        unmatchedTable.Rows.Add(match.row.ItemArray);
                    }
                }

            }

        }
    }
    public class Match
    {
        public DataRow row { get; set; }
        public Boolean matched { get; set; }
        public string RRN { get; set; }
        public decimal amount { get; set; }
    }
}