none
LINQ to object : How to perform 3 join between 3 List<T> RRS feed

  • Question

  • suppose i have List1, List2 & List3

    now i want to perform left join between List1 & List2 and equi join between List1 & List3 at same place. how to achieve it....if possible please include a sample code.

    this kind of join i am looking for 

    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 }};
    
    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"}
    };
    
    var result = from m1 in list1
        join m2 in list2 on m1.CountryID equals m2.CountryID
        select new { m2.CountryName, m1.CountryData, m1.regionUID } into intermediate
        join m3 in list3 on intermediate.regionUID equals m3.regionUID
        select new { intermediate.CountryName, intermediate.CountryData, m3.regionName};

    but the above code does not suit for my scenario. so include a sample code where left join between List1 & List2 and equi join between List1 & List3 at same place.

    thanks

    Sunday, April 14, 2019 10:24 AM

All replies

  • hello Sudip_inn,

    `A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

    from an msdn article "https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins", 

    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 };

    Good Coding;



    Sunday, April 14, 2019 5:39 PM