C# how to join two datatable without LINQ

Question

C# how to join two datatable without LINQ

T.Zacks 3,996

when we extract data using join two datatables by LINQ then we get good performance but i heard for loop is faster so how to achieve the same result without LINQ

below is LINQ JOIN

BogeyConfigList = (from bogyconfiglist in Bogeylist.AsEnumerable() //10QKbogey data read here
                                join LiList in list.AsEnumerable()             //~10QKConfig.liconfg file read here
                                on new
                                {
                                    val = bogyconfiglist.LineItem.Trim().ToUpper(),
                                    val1 = bogyconfiglist.Section.Trim().ToUpper()
                                }
                                equals new
                                {
                                    val = LiList.LI.Trim().ToUpper(),
                                    val1 = LiList.Section.Trim().ToUpper()
                                }
                                into conbogylist
                                from confg in conbogylist.DefaultIfEmpty()
                                select new QCHelper()
                                {
                                    Section = bogyconfiglist.Section,
                                    Li = bogyconfiglist.LineItem,
                                    CrossCalc1Q = confg == null ? string.Empty : (confg.CrossCalc1Q == null ? "" : confg.CrossCalc1Q.Replace("~9999", string.Empty).Trim()),
                                    CrossCalc2Q = confg == null ? string.Empty : (confg.CrossCalc2Q == null ? "" : confg.CrossCalc2Q.Replace("~9999", string.Empty).Trim()),
                                    CrossCalc1H = confg == null ? string.Empty : (confg.CrossCalc1H == null ? "" : confg.CrossCalc1H.Replace("~9999", string.Empty).Trim()),
                                    CrossCalc3Q = confg == null ? string.Empty : (confg.CrossCalc3Q == null ? "" : confg.CrossCalc3Q.Replace("~9999", string.Empty).Trim()),
                                    CrossCalc4Q = confg == null ? string.Empty : (confg.CrossCalc4Q == null ? "" : confg.CrossCalc4Q.Replace("~9999", string.Empty).Trim()),
                                    CrossCalc2H = confg == null ? string.Empty : (confg.CrossCalc2H == null ? "" : confg.CrossCalc2H.Replace("~9999", string.Empty).Trim()),
                                    CrossCalcFY = confg == null ? string.Empty : (confg.CrossCalcFY == null ? "" : confg.CrossCalcFY.Replace("~9999", string.Empty).Trim()),
                                    AllowComma = confg == null ? false : confg.AllowComma,
                                    AllowedDecimalPlace = confg == null ? string.Empty : confg.AllowedDecimalPlace,
                                    AllowPercentageSign = confg == null ? false : confg.AllowPercentageSign,
                                    CurrencySign = confg == null ? string.Empty : confg.CurrencySign,
                                    IsQcCheck = confg == null ? false : confg.QCCheck,
                                    QcType = confg == null ? string.Empty : confg.QCType,
                                    FormulaLiConfig = confg == null ? string.Empty : (confg.StandrdFormula == null ? "" : confg.StandrdFormula.Replace("~9999", string.Empty).Trim()),
                                    xFundCode = bogyconfiglist.xFundCode == null ? string.Empty : bogyconfiglist.xFundCode
                                }).Distinct().ToList<QCHelper>();

see my above LINQ join code and tell me how could i achieve the same output without using LINQ join. if possible please provide a sample code.

few relevant url i have checked.
https://www.codeproject.com/Questions/699839/Looping-through-two-datatables-to-match-a-value
https://stackoverflow.com/questions/39524569/c-sharp-linq-to-combine-or-join-two-datatables-into-one

thanks

Answer accepted by question author

0 additional answers

Your answer

Answer 1

@T.Zacks , you could try the following code to join two datatable without LINQ.

 internal class Program  
    {  
        static void Main(string[] args)  
        {  
            List<Bogey> Bogeylist = new List<Bogey>();  
            Bogeylist.Add(new Bogey() { Section = "test1", LineItem = "1001"  });  
            Bogeylist.Add(new Bogey() { Section = "test2", LineItem = "1002" });  
            Bogeylist.Add(new Bogey() { Section = "test4", LineItem = "1004" });  
            Bogeylist.Add(new Bogey() { Section = "test5", LineItem = "1005" });  
  
            List<LII> list = new List<LII>();  
            list.Add(new LII() { StandrdFormula = "Hello", Section = "test1", LI = "1001", CrossCalc1Q = "111" ,AllowComma=true});  
            list.Add(new LII() { StandrdFormula = "Hi", Section = "test2", LI = "1002", CrossCalc1Q = "222" ,AllowComma=false});  
            list.Add(new LII() { StandrdFormula = "Greeting", Section = "test3", LI = "1003", CrossCalc1Q = "333" ,AllowComma=true});  
            list.Add(new LII() { StandrdFormula = "Greeting", Section = "test4", LI = "1004", CrossCalc1Q = "333", AllowComma = true });  
            var BogeyConfigList = (from bogyconfiglist in Bogeylist.AsEnumerable() //10QKbogey data read here  
                                  join LiList in list.AsEnumerable()             //~10QKConfig.liconfg file read here  
                                  on new  
                                  {  
                                      val = bogyconfiglist.LineItem.Trim().ToUpper(),  
                                      val1 = bogyconfiglist.Section.Trim().ToUpper()  
                                  }  
                                  equals new  
                                  {  
                                      val = LiList.LI.Trim().ToUpper(),  
                                      val1 = LiList.Section.Trim().ToUpper()  
                                  }  
                                  into conbogylist  
            from confg in conbogylist.DefaultIfEmpty()  
            select new QCHelper()  
            {  
                Section = bogyconfiglist.Section,  
                Li = bogyconfiglist.LineItem,  
                AllowComma = confg == null ? false : confg.AllowComma,  
                CrossCalc1Q = confg == null ? string.Empty : (confg.CrossCalc1Q == null ? "" : confg.CrossCalc1Q.Replace("~9999", string.Empty).Trim()),  
                FormulaLiConfig = confg == null ? string.Empty : (confg.StandrdFormula == null ? "" : confg.StandrdFormula.Replace("~9999", string.Empty).Trim()),  
            }).Distinct().ToList<QCHelper>();  


           // New method without using linq  
            List<QCHelper> helperlist = new List<QCHelper>();  
            var newlist = new List<Bogey>();  
            foreach (var bogyconfiglist in Bogeylist)  
            {  
                foreach (var LiList in list)  
                {  
                    if (bogyconfiglist.LineItem == LiList.LI && bogyconfiglist.Section == LiList.Section)  
                    {  
                        QCHelper helper = new QCHelper();  
                        helper.Section = LiList.Section;  
                        helper.Li = LiList.LI;  
                        helper.CrossCalc1Q = LiList == null ? string.Empty : (LiList.CrossCalc1Q == null ? "" : LiList.CrossCalc1Q.Replace("~9999", string.Empty).Trim());  
                        helper.AllowComma = LiList == null ? false : LiList.AllowComma;  
                        helper.FormulaLiConfig = LiList == null ? string.Empty : (LiList.StandrdFormula == null ? "" : LiList.StandrdFormula.Replace("~9999", string.Empty).Trim());  
                        helperlist.Add(helper);  
                        newlist.Add(bogyconfiglist);  
                    }  
                     
                     
  
                }  
            }  
            var leftlist = Bogeylist.Except(newlist).ToList();  
            foreach (var bogyconfiglist in leftlist)  
            {  
  
                QCHelper helper = new QCHelper();  
                helper.Section = bogyconfiglist.Section;  
                helper.Li = bogyconfiglist.LineItem;  
                helper.CrossCalc1Q = "";  
                helper.AllowComma = false;  
                helper.FormulaLiConfig = "";  
                helperlist.Add(helper);  
            }  
  
            foreach (var item in BogeyConfigList)  
            {  
                string result = string.Format("{0}-{1}-{2}-{3}-{4}", item.Section, item.Li, item.CrossCalc1Q, item.AllowComma, item.FormulaLiConfig);  
                Console.WriteLine(result);  
            }  
            Console.WriteLine("****************");  
            foreach (var item in helperlist)  
            {  
                string result = string.Format("{0}-{1}-{2}-{3}-{4}", item.Section, item.Li, item.CrossCalc1Q, item.AllowComma, item.FormulaLiConfig);  
                Console.WriteLine(result);  
            }  
  
            Console.ReadKey();  
  
        }  
    }  
  
    public class Bogey  
    {  
        public string LineItem { get; set; }  
  
        public string Section { get; set; }   
  
    }  
  
    public class LII  
    {  
        public string LI { get; set; }  
  
        public string Section { get; set; }  
  
        public string StandrdFormula { get; set; }  
        public string CrossCalc1Q { get; set; }  
        public bool AllowComma { get; set; }  
    }  
  
    public class QCHelper  
    {  
  
        public string Section { get; set; }  
        public string Li{ get; set; }  
        public string CrossCalc1Q { get; set; }  
        public bool AllowComma { get; set; }  
        public string FormulaLiConfig { get; set; }  
    }

For testing your code, I created two list and three classes.

Result:

Update for comparing two methods by using stopwatch:

internal class Program  
    {  
        static void Main(string[] args)  
        {  
            Stopwatch stopwatch1 = new Stopwatch();  
            Stopwatch stopwatch2= new Stopwatch();  
            List<Bogey> Bogeylist = new List<Bogey>();  
            Bogeylist.Add(new Bogey() { Section = "test1", LineItem = "1001"  });  
            Bogeylist.Add(new Bogey() { Section = "test2", LineItem = "1002" });  
            Bogeylist.Add(new Bogey() { Section = "test4", LineItem = "1004" });  
            Bogeylist.Add(new Bogey() { Section = "test5", LineItem = "1005" });  
  
            List<LII> list = new List<LII>();  
            list.Add(new LII() { StandrdFormula = "Hello", Section = "test1", LI = "1001", CrossCalc1Q = "111" ,AllowComma=true});  
            list.Add(new LII() { StandrdFormula = "Hi", Section = "test2", LI = "1002", CrossCalc1Q = "222" ,AllowComma=false});  
            list.Add(new LII() { StandrdFormula = "Greeting", Section = "test3", LI = "1003", CrossCalc1Q = "333" ,AllowComma=true});  
            list.Add(new LII() { StandrdFormula = "Greeting", Section = "test4", LI = "1004", CrossCalc1Q = "333", AllowComma = true });  
            stopwatch1.Start();  
            var BogeyConfigList = (from bogyconfiglist in Bogeylist.AsEnumerable() //10QKbogey data read here  
                                  join LiList in list.AsEnumerable()             //~10QKConfig.liconfg file read here  
                                  on new  
                                  {  
                                      val = bogyconfiglist.LineItem.Trim().ToUpper(),  
                                      val1 = bogyconfiglist.Section.Trim().ToUpper()  
                                  }  
                                  equals new  
                                  {  
                                      val = LiList.LI.Trim().ToUpper(),  
                                      val1 = LiList.Section.Trim().ToUpper()  
                                  }  
                                  into conbogylist  
            from confg in conbogylist.DefaultIfEmpty()  
            select new QCHelper()  
            {  
                Section = bogyconfiglist.Section,  
                Li = bogyconfiglist.LineItem,  
                AllowComma = confg == null ? false : confg.AllowComma,  
                CrossCalc1Q = confg == null ? string.Empty : (confg.CrossCalc1Q == null ? "" : confg.CrossCalc1Q.Replace("~9999", string.Empty).Trim()),  
                FormulaLiConfig = confg == null ? string.Empty : (confg.StandrdFormula == null ? "" : confg.StandrdFormula.Replace("~9999", string.Empty).Trim()),  
            }).Distinct().ToList<QCHelper>();  
            stopwatch1.Stop();  
            stopwatch2.Start();  
            List<QCHelper> helperlist = new List<QCHelper>();  
            var newlist = new List<Bogey>();  
            foreach (var bogyconfiglist in Bogeylist)  
            {  
                foreach (var LiList in list)  
                {  
                    if (bogyconfiglist.LineItem == LiList.LI && bogyconfiglist.Section == LiList.Section)  
                    {  
                        QCHelper helper = new QCHelper();  
                        helper.Section = LiList.Section;  
                        helper.Li = LiList.LI;  
                        helper.CrossCalc1Q = LiList == null ? string.Empty : (LiList.CrossCalc1Q == null ? "" : LiList.CrossCalc1Q.Replace("~9999", string.Empty).Trim());  
                        helper.AllowComma = LiList == null ? false : LiList.AllowComma;  
                        helper.FormulaLiConfig = LiList == null ? string.Empty : (LiList.StandrdFormula == null ? "" : LiList.StandrdFormula.Replace("~9999", string.Empty).Trim());  
                        helperlist.Add(helper);  
                        newlist.Add(bogyconfiglist);  
                    }  
                     
                     
  
                }  
            }  
            var leftlist = Bogeylist.Except(newlist).ToList();  
            foreach (var bogyconfiglist in leftlist)  
            {  
  
                QCHelper helper = new QCHelper();  
                helper.Section = bogyconfiglist.Section;  
                helper.Li = bogyconfiglist.LineItem;  
                helper.CrossCalc1Q = "";  
                helper.AllowComma = false;  
                helper.FormulaLiConfig = "";  
                helperlist.Add(helper);  
            }  
            stopwatch2.Stop();  
        
            Console.WriteLine(stopwatch1.ElapsedMilliseconds);  
            Console.WriteLine(stopwatch2.ElapsedMilliseconds);  
  
           
  
            Console.ReadKey();  
  
        }  
    }

Comparing result:

Hope my code example could help you.

Best Regards,
Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

T.Zacks 3,996 Reputation points

2022-02-22T06:55:28.587+00:00

if i get data from two datatable using for loop without joining by LINQ then which approach will be performant ? which one will take less time?

thanks
Jack J Jun 25,316 Reputation points

2022-02-22T07:11:08.74+00:00

@T.Zacks , I used stopwatch to compare their execute time. As the result shown, the linq method indeed spends more time than the other method without using linq. Also, I have updated my code in my answer.
T.Zacks 3,996 Reputation points

2022-02-24T07:25:17.72+00:00

Thank you sir for your example code but this area not clear why we need to use Except() ? please help me to understand this.

var leftlist = Bogeylist.Except(newlist).ToList(); foreach (var bogyconfiglist in leftlist) { QCHelper helper = new QCHelper(); helper.Section = bogyconfiglist.Section; helper.Li = bogyconfiglist.LineItem; helper.CrossCalc1Q = ""; helper.AllowComma = false; helper.FormulaLiConfig = ""; helperlist.Add(helper); }
Jack J Jun 25,316 Reputation points

2022-02-24T07:41:19.48+00:00

@T.Zacks , Except() method is used to produce the set difference of two sequences, which means that exclude the same item in Bogeylist and list.
For example, in my code, { Section = "test5", LineItem = "1005" }); is not contained in the second list, we need to get it Individually by using list.except.
In a word, we can use the code to get a list of Section and Line item that it not contains in the second list.

Share via

C# how to join two datatable without LINQ

0 additional answers

Your answer