none
LINQ join between three list (Equi join & Left join) RRS feed

  • Question

  • i have three list list1, list2 & list3. i want to do equi join between list1 & list2 and left join between list1 & list3.

    how to achieve it with code. please share a example like Product, Order & OrderDetail list.

    thanks


    • Edited by Sudip_inn Sunday, March 31, 2019 9:28 AM
    Sunday, March 31, 2019 9:27 AM

Answers

  • Hi Studip_inn,

    I do the test with the examples in the link below. It would be helpful to you.

    equi join

    https://www.devcurry.com/2011/01/join-example-in-linq-and-c.html

    Left Joinhttps://www.devcurry.com/2011/01/linq-left-join-example-in-c.html

    Best Regards,

    Wendy


    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 MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Sunday, April 28, 2019 11:51 AM
    Monday, April 22, 2019 7:28 AM
    Moderator
  • Hi Sudip_inn,

    I think that this is the second time you post this question :
    https://social.msdn.microsoft.com/Forums/en-US/88bde5b1-0bcb-46a4-884f-b1a7051e1cb0/linq-to-object-how-to-perform-3-join-between-3-listlttgt?forum=csharpgeneral

    var list1 = new[]{
        new { CountryID = 12, CountryData = "Has good gras", regionUID = 4567  },
        new { CountryID = 12, CountryData = "nice weather", regionUID = 6789  },
        new { CountryID = 16, CountryData = "stormy weather", regionUID = 1234 },
        new { CountryID = 12, CountryData = "testData", regionUID = 1235 }};
    
    var list2 = new[]{
        new { CountryID = 12, CountryName = "green hill"},
        new { CountryID = 16, CountryName = "stormy mountain"}
    };
    
    var list3 = new[]{
        new {regionUID = 4567, regionName = "above 1000feet"},
        new {regionUID = 6789, regionName = "on the ground"},
        new {regionUID = 1234, regionName = "on the hill"},
        new {regionUID = 1235, regionName = "testName"}
    
    };
    var m1_left_join_m2 = (from m1 in list1
                            join m2 in list2 on m1.CountryID equals m2.CountryID into m1_join_m2
                            from item in m1_join_m2.DefaultIfEmpty() // <--- 
                            select new { item.CountryName, m1.CountryData, m1.regionUID, m1.CountryID });
    
    var result = from  m1jm2 in m1_left_join_m2
                join m3 in list3 
                on m1jm2.regionUID equals m3.regionUID
                select new { m1jm2.CountryName, m1jm2.CountryData, m3.regionName, m3.regionUID, m1jm2.CountryID };
    
    var combined_joins = from m1jm2 in 
                        (from m1 in list1
                            join m2 in list2 on m1.CountryID equals m2.CountryID into m1_join_m2
                            from item in m1_join_m2.DefaultIfEmpty()
                            select new { item.CountryName, m1.CountryData, m1.regionUID, m1.CountryID })
                join m3 in list3
                on m1jm2.regionUID equals m3.regionUID
                select new { m1jm2.CountryName, m1jm2.CountryData, m3.regionName, m3.regionUID, m1jm2.CountryID };
    take a look to this article "https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins", 

    Good Coding;

    • Marked as answer by Sudip_inn Sunday, April 28, 2019 11:54 AM
    Monday, April 22, 2019 3:50 PM

All replies

  • Hi Studip_inn,

    Thank you for posting here.

    For your question, please refer to the links below.

    Equi join: Please refer to the examples of Example - Multiple join.

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-inner-joins

    Left join: This is example for three tables, you could convert tables to lists.

    https://stackoverflow.com/questions/6145938/joining-three-tables-and-using-a-left-outer-join

    Best Regards,

    Wendy


    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 MSDNFSF@microsoft.com.

    Monday, April 1, 2019 5:46 AM
    Moderator
  • See my scenario and code.

    var cfToggleList = (from cf in QCHelperall
    join toggle in ToggleDataAll
      on new { val = cf.Section.Trim().ToUpper(), val1 = cf.Li.Trim().ToUpper(), val2 = cf.Period.Replace("A", "").Replace("E", "").Trim().ToUpper(), val3 = cf.Broker.Trim().ToUpper() }
      equals new { val = toggle.Section.Trim().ToUpper(), val1 = toggle.Lineitem.Trim().ToUpper(), val2 = toggle.Period.Trim().ToUpper(), val3 = toggle.Broker.Trim().ToUpper() }
      into tempJoin
    from leftJoin in tempJoin.DefaultIfEmpty()
    select new QCHelper()
    {
        Broker = cf.Broker,
        BrokerName = (from brk in BrokerCodeName
    		  where brk.BrokerCode.ToUpper() == cf.Broker.Split('-')[0].ToUpper()
    		  select brk.BrokerName).FirstOrDefault(),
        //Where(x => x.Field<string>("BCODE") == cf.Broker).Select(x => x.Field<string>("BNAME")).ToString(), /*cf.BrokerName,*/
        Section = cf.Section,
        Li = cf.Li,
        StandardDate = cf.Period,
        CrossCalc1Q = cf.CrossCalc1Q,
        CrossCalc2Q = cf.CrossCalc2Q,
        CrossCalc3Q = cf.CrossCalc3Q,
        CrossCalc4Q = cf.CrossCalc4Q,
        CrossCalcFY = cf.CrossCalcFY,
        Value = cf.Value,
        FycheckToggle = leftJoin == null ? string.Empty : (leftJoin.ToggleText.Contains("FYCHECKTOGGLE") ? leftJoin.ToggleText : string.Empty),
        QcCheckToggle = leftJoin == null ? string.Empty : (leftJoin.ToggleText.Contains("QCCHECKTOGGLE") ? leftJoin.ToggleText : string.Empty)
    }).ToList<QCHelper>();

    My 1st question in the above code i am doing left join between two list<T> 

    from cf in QCHelperall join toggle in ToggleDataAll

    there is another join for each iteration see the code

    Broker = cf.Broker,
    BrokerName = (from brk in BrokerCodeName
    	  where brk.BrokerCode.ToUpper() == cf.Broker.Split('-')[0].ToUpper()
    	  select brk.BrokerName).FirstOrDefault(),

    just tell me if i join like this way then this join will be performed for each iteration on above between from cf in QCHelperall join toggle in ToggleDataAll does it slow down my code when there is huge data in two list ?

    My 2nd question

    there is left join between QCHelperall and ToggleDataAll. now tell me how could i join my datatable BrokerCodeName with QCHelperall which will be equi join.

    please see my full join code at top and tell me how could i add few more code to equi join between BrokerCodeName with QCHelperallBrokerCodeName is also a listlooking for joining code between 3 list. one left join between QCHelperall & ToggleDataAll list and another equi join between BrokerCodeName with QCHelperall

    thanks


    Saturday, April 6, 2019 7:24 PM
  • please see my code and answer sir. i am looking for 3 join example where list1 & list will have left join and list1 & list3 will have equi join in same area.

    please provide code example.

    Sunday, April 7, 2019 6:27 PM
  • Hi Studip_inn,

    I do the test with the examples in the link below. It would be helpful to you.

    equi join

    https://www.devcurry.com/2011/01/join-example-in-linq-and-c.html

    Left Joinhttps://www.devcurry.com/2011/01/linq-left-join-example-in-c.html

    Best Regards,

    Wendy


    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 MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Sunday, April 28, 2019 11:51 AM
    Monday, April 22, 2019 7:28 AM
    Moderator
  • Hi Sudip_inn,

    I think that this is the second time you post this question :
    https://social.msdn.microsoft.com/Forums/en-US/88bde5b1-0bcb-46a4-884f-b1a7051e1cb0/linq-to-object-how-to-perform-3-join-between-3-listlttgt?forum=csharpgeneral

    var list1 = new[]{
        new { CountryID = 12, CountryData = "Has good gras", regionUID = 4567  },
        new { CountryID = 12, CountryData = "nice weather", regionUID = 6789  },
        new { CountryID = 16, CountryData = "stormy weather", regionUID = 1234 },
        new { CountryID = 12, CountryData = "testData", regionUID = 1235 }};
    
    var list2 = new[]{
        new { CountryID = 12, CountryName = "green hill"},
        new { CountryID = 16, CountryName = "stormy mountain"}
    };
    
    var list3 = new[]{
        new {regionUID = 4567, regionName = "above 1000feet"},
        new {regionUID = 6789, regionName = "on the ground"},
        new {regionUID = 1234, regionName = "on the hill"},
        new {regionUID = 1235, regionName = "testName"}
    
    };
    var m1_left_join_m2 = (from m1 in list1
                            join m2 in list2 on m1.CountryID equals m2.CountryID into m1_join_m2
                            from item in m1_join_m2.DefaultIfEmpty() // <--- 
                            select new { item.CountryName, m1.CountryData, m1.regionUID, m1.CountryID });
    
    var result = from  m1jm2 in m1_left_join_m2
                join m3 in list3 
                on m1jm2.regionUID equals m3.regionUID
                select new { m1jm2.CountryName, m1jm2.CountryData, m3.regionName, m3.regionUID, m1jm2.CountryID };
    
    var combined_joins = from m1jm2 in 
                        (from m1 in list1
                            join m2 in list2 on m1.CountryID equals m2.CountryID into m1_join_m2
                            from item in m1_join_m2.DefaultIfEmpty()
                            select new { item.CountryName, m1.CountryData, m1.regionUID, m1.CountryID })
                join m3 in list3
                on m1jm2.regionUID equals m3.regionUID
                select new { m1jm2.CountryName, m1jm2.CountryData, m3.regionName, m3.regionUID, m1jm2.CountryID };
    take a look to this article "https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins", 

    Good Coding;

    • Marked as answer by Sudip_inn Sunday, April 28, 2019 11:54 AM
    Monday, April 22, 2019 3:50 PM