Left and right outer join by LINQ

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

Answer 1

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/

Answer 2

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



Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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