locked
Left and right outer join by LINQ RRS feed

  • Question

  • User1183902823 posted

    see just a example taken from http://www.devcurry.com/2011/01/linq-left-join-example-in-c.html

    var orderForBooks = from bk in bookList
                join ordr in bookOrders
                on bk.BookID equals ordr.BookID
                into a
                from b in a.DefaultIfEmpty(new Order())
                select new
                {
                    bk.BookID,
                    Name = bk.BookNm,
                    b.PaymentMode
                };
    
    foreach (var item in orderForBooks)
        Console.WriteLine(item);

    normally when we do left or right outer join with sql query then we use left or right keyword to inform sql server that i want left or right join but with linq there is no such keyword used. so how system understand that i am using left or right join ?

    please help me to understand how system understand that i am using left or right join ? thanks

    Tuesday, November 21, 2017 9:37 AM

Answers

  • User1400794712 posted

    Hi tridip1974,

    As the code you provided, normally we use the DefaultIfEmpty method on the results of a group join to perform a left/right outer join. This article explained how to perform left outer joins with example, you can check it for detailed information.  

    Best Regards,

    Daisy

    <sub></sub><sup></sup>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 22, 2017 8:14 AM
  • User-832373396 posted

    <g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="10" data-gr-id="10">Hi</g> tridip1974,

     

    can show the <g class="gr_ gr_117 gr-alert gr_gramm gr_hide gr_inline_cards gr_run_anim Style multiReplace replaceWithoutSep replaceWithoutSep" id="117" data-gr-id="117">syntactical</g><g class="gr_ gr_117 gr-alert gr_gramm gr_hide gr_inline_cards gr_disable_anim_appear Style multiReplace replaceWithoutSep replaceWithoutSep" id="117" data-gr-id="117">  </g><g class="gr_ gr_117 gr-alert gr_gramm gr_hide gr_inline_cards gr_disable_anim_appear Style multiReplace replaceWithoutSep replaceWithoutSep" id="117" data-gr-id="117">difference</g> between left and right outer join by <g class="gr_ gr_115 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="115" data-gr-id="115">linq</g> with <g class="gr_ gr_116 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="116" data-gr-id="116">example</g>. thanks

    Perform inner joins:

    var query = from person in people
                    join pet in pets on person equals pet.Owner
                    select new { OwnerName = person.FirstName, PetName = pet.Name };

    and Perform left outer joins:

    (note, you could see that it will <g class="gr_ gr_599 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="599" data-gr-id="599">care  </g><g class="gr_ gr_599 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="599" data-gr-id="599">gj</g>.DefaultIfEmpty() and set default value )

    var query = from person in people
                    join pet in pets on person equals pet.Owner into gj
                    from subpet in gj.DefaultIfEmpty()
                    select new { person.FirstName, PetName = subpet?.Name ?? String.Empty };

    I suggest that you could learn the EF from Microsoft Official Document:

    Method-Based Query Syntax Examples: Join Operators

     https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-join-operators 

    Perform left outer joins

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

    Perform inner joins

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

    Overview:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/overview 

    More references:

    1 Entity Framework (EF) Documentation(Data developer Center)

    https://msdn.microsoft.com/en-us/data/ee712907(vs.113) 

    2 Microsoft Virtual Academy(<g class="gr_ gr_78 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="78" data-gr-id="78">Videoes</g>)

     Implementing Entity Framework with MVC

    https://mva.microsoft.com/en-US/training-courses/implementing-entity-framework-with-mvc-8931?l=e2H2lDC3_8304984382 

    3 MSDN document and videoes   

    https://msdn.microsoft.com/en-us/library/jj713564(v=vs.113).<g class="gr_ gr_79 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="79" data-gr-id="79">aspx</g>  

    https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).<g class="gr_ gr_80 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="80" data-gr-id="80">aspx</g>   

    https://msdn.microsoft.com/en-us/library/jj591620(v=vs.113).<g class="gr_ gr_81 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="81" data-gr-id="81">aspx</g> 

    4 Entityframeworktutorial site

    http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx 

    With regards, Angelina Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 22, 2017 9:31 AM
  • User-832373396 posted

    <g class="gr_ gr_8 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="8" data-gr-id="8">Hi</g> <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">tridip</g>,

    <g class="gr_ gr_14 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="14" data-gr-id="14">how</g> me the difference between left and right outer join syntax with a small example.

    In fact, it only changes the order; 

    The is the test example(LeftOuterJoinExample):

    var query = from person in people
                            join pet in pets on person equals pet.Owner into gj
                            from subpet in gj.DefaultIfEmpty()////  the gj represents pet conllection
                            select new { person.FirstName, PetName = subpet?.Name ?? String.Empty };
    // here 'PetName = ' is required,because Name property of subpet could null.
    foreach (var v in query) { Debug.WriteLine($"{v.FirstName + ":",-15}{v.PetName}"); }

    The is the test example(RightOuterJoinExample):

      var query = from pet in pets
                            join  person in  people on pet.Owner equals person into gj                        
                            from subpet in gj.DefaultIfEmpty()// now the gj represents person conllection
                            select new { FirstName= subpet?.FirstName ?? String.Empty,   pet.Name  };// here 'FirstName= ' is required
    foreach (var v in query) { Debug.WriteLine($"{v.Name + ":",-15}{v.FirstName } ");
    }

    Output:

    Magnus:        Daisy
    Terry:         Barley
    Terry:         Boots
    Terry:         Blue Moon
    Charlotte:     Whiskers
    Arlene:        
    ------------------------
    Barley:        Terry 
    Boots:         Terry 
    Whiskers:      Charlotte 
    Blue Moon:     Terry 
    Daisy:         Magnus 

    References:

    http://csharp-guide.blogspot.sg/2012/05/linq-to-<g class="gr_ gr_199 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="199" data-gr-id="199">sql</g>-right-outer-join.html

    http://csharp-guide.blogspot.sg/2012/05/linq-to-<g class="gr_ gr_219 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="219" data-gr-id="219">sql</g>-left-outer-join.html   

    With regards, Angelina Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 23, 2017 7:13 AM

All replies

  • User1120430333 posted

    I suggest that you use a SQL Profiler and look at the T-SQL generated by the EF engine before it is submitted for execution by the database engine.

    https://www.google.com/search?source=hp&ei=20oUWuesJuWF_QbyoYSoDw&q=sql+profiler+free+download&oq=sql+profiler+free&gs_l=psy-ab.1.0.0l2j0i22i30k1l8.1963.22680.0.28850.17.15.0.2.2.0.204.2076.0j14j1.15.0....0...1c.1.64.psy-ab..0.17.2136...0i131k1j0i10k1.0.4mDT63pZtHA

    Another tool you can use to see the generated T-SQL from a Linq query is LinqPAD.

    https://www.linqpad.net/

    Tuesday, November 21, 2017 3:52 PM
  • User1400794712 posted

    Hi tridip1974,

    As the code you provided, normally we use the DefaultIfEmpty method on the results of a group join to perform a left/right outer join. This article explained how to perform left outer joins with example, you can check it for detailed information.  

    Best Regards,

    Daisy

    <sub></sub><sup></sup>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 22, 2017 8:14 AM
  • User1183902823 posted

    what would be syntactical difference between left and right outer join with linq.

    can show the syntactical  difference between left and right outer join by linq with example. thanks

    Wednesday, November 22, 2017 8:27 AM
  • User-832373396 posted

    <g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="10" data-gr-id="10">Hi</g> tridip1974,

     

    can show the <g class="gr_ gr_117 gr-alert gr_gramm gr_hide gr_inline_cards gr_run_anim Style multiReplace replaceWithoutSep replaceWithoutSep" id="117" data-gr-id="117">syntactical</g><g class="gr_ gr_117 gr-alert gr_gramm gr_hide gr_inline_cards gr_disable_anim_appear Style multiReplace replaceWithoutSep replaceWithoutSep" id="117" data-gr-id="117">  </g><g class="gr_ gr_117 gr-alert gr_gramm gr_hide gr_inline_cards gr_disable_anim_appear Style multiReplace replaceWithoutSep replaceWithoutSep" id="117" data-gr-id="117">difference</g> between left and right outer join by <g class="gr_ gr_115 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="115" data-gr-id="115">linq</g> with <g class="gr_ gr_116 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="116" data-gr-id="116">example</g>. thanks

    Perform inner joins:

    var query = from person in people
                    join pet in pets on person equals pet.Owner
                    select new { OwnerName = person.FirstName, PetName = pet.Name };

    and Perform left outer joins:

    (note, you could see that it will <g class="gr_ gr_599 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="599" data-gr-id="599">care  </g><g class="gr_ gr_599 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="599" data-gr-id="599">gj</g>.DefaultIfEmpty() and set default value )

    var query = from person in people
                    join pet in pets on person equals pet.Owner into gj
                    from subpet in gj.DefaultIfEmpty()
                    select new { person.FirstName, PetName = subpet?.Name ?? String.Empty };

    I suggest that you could learn the EF from Microsoft Official Document:

    Method-Based Query Syntax Examples: Join Operators

     https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-join-operators 

    Perform left outer joins

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

    Perform inner joins

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

    Overview:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/overview 

    More references:

    1 Entity Framework (EF) Documentation(Data developer Center)

    https://msdn.microsoft.com/en-us/data/ee712907(vs.113) 

    2 Microsoft Virtual Academy(<g class="gr_ gr_78 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="78" data-gr-id="78">Videoes</g>)

     Implementing Entity Framework with MVC

    https://mva.microsoft.com/en-US/training-courses/implementing-entity-framework-with-mvc-8931?l=e2H2lDC3_8304984382 

    3 MSDN document and videoes   

    https://msdn.microsoft.com/en-us/library/jj713564(v=vs.113).<g class="gr_ gr_79 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="79" data-gr-id="79">aspx</g>  

    https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).<g class="gr_ gr_80 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="80" data-gr-id="80">aspx</g>   

    https://msdn.microsoft.com/en-us/library/jj591620(v=vs.113).<g class="gr_ gr_81 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="81" data-gr-id="81">aspx</g> 

    4 Entityframeworktutorial site

    http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx 

    With regards, Angelina Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 22, 2017 9:31 AM
  • User1183902823 posted

    thanks for your answer but you missed right outer join example because i asked what would be syntactical difference between left and right outer join with linq EF.

    so show me the difference between left and right outer join syntax with a small example. thanks

    Wednesday, November 22, 2017 9:45 AM
  • User-832373396 posted

    <g class="gr_ gr_8 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="8" data-gr-id="8">Hi</g> <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">tridip</g>,

    <g class="gr_ gr_14 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" id="14" data-gr-id="14">how</g> me the difference between left and right outer join syntax with a small example.

    In fact, it only changes the order; 

    The is the test example(LeftOuterJoinExample):

    var query = from person in people
                            join pet in pets on person equals pet.Owner into gj
                            from subpet in gj.DefaultIfEmpty()////  the gj represents pet conllection
                            select new { person.FirstName, PetName = subpet?.Name ?? String.Empty };
    // here 'PetName = ' is required,because Name property of subpet could null.
    foreach (var v in query) { Debug.WriteLine($"{v.FirstName + ":",-15}{v.PetName}"); }

    The is the test example(RightOuterJoinExample):

      var query = from pet in pets
                            join  person in  people on pet.Owner equals person into gj                        
                            from subpet in gj.DefaultIfEmpty()// now the gj represents person conllection
                            select new { FirstName= subpet?.FirstName ?? String.Empty,   pet.Name  };// here 'FirstName= ' is required
    foreach (var v in query) { Debug.WriteLine($"{v.Name + ":",-15}{v.FirstName } ");
    }

    Output:

    Magnus:        Daisy
    Terry:         Barley
    Terry:         Boots
    Terry:         Blue Moon
    Charlotte:     Whiskers
    Arlene:        
    ------------------------
    Barley:        Terry 
    Boots:         Terry 
    Whiskers:      Charlotte 
    Blue Moon:     Terry 
    Daisy:         Magnus 

    References:

    http://csharp-guide.blogspot.sg/2012/05/linq-to-<g class="gr_ gr_199 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="199" data-gr-id="199">sql</g>-right-outer-join.html

    http://csharp-guide.blogspot.sg/2012/05/linq-to-<g class="gr_ gr_219 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="219" data-gr-id="219">sql</g>-left-outer-join.html   

    With regards, Angelina Jolie

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 23, 2017 7:13 AM