Click here to Skip to main content
Click here to Skip to main content

LinQ Extended Joins

, 27 Oct 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
The lost joins in System.Linq

Introduction

The very first time that I was interested by LinQ, there in 2007, apart from the great admiration for the product and the whole range of possibilities that it was before me, there was something that I really missed something that, for a SQL developer with a “wide” range of acknowledge, was difficult to understand about the new philosophy for queries over objects. It was nothing more than the absence of many of the SQL joins advanced sentences within the LinQ basis class extensive methods.

At the beginning I assumed that the problem was a matter of time for the Microsoft Developer team and that it will be fixed in the next versions. But, the more deep I analysed the product the more I realized that maybe it was not the better way to work for an object oriented developer and that it was needed to leave apart the solutions given for a database proffesional worker since they may be useful for them but not so valid for us. This was remarked even more at the time I knew about Entity Framework browsing  properties.

To this day, I learnt that anything is black or white but the main point is to be useful, that some solutions do not fit 100% with the pureness of the development premises but they are very handy and save time and money, which is essential in the daily work for a great developer. We can find some examples of it within the Framework (Extensive methods, anonymous types, dynamics, etc) 

Background 

Some years ago, I read an article by C. L. Moffatt (link) where he explained, in a very clear and concise way, the types of joins in SQL and the idea of writing a similar article for LinQ has been going round and round in my head since then. Now I´ve decided to do it.

I have seen many questions and answers about this topic in the forums but I couldn´t find any which embrace it all. It is my intention to overcome these missing topics with the next lines.

 This article only tries to be a didactic paper but also tries to make the others´ life easier  by adding a sample project where everything mentioned in the article has been applied. In addition, it is included an extension class that will beuseful for those who don´t want to spent too much time coding each and every concept. 

Using the code 

I will use two classes, to demonstrate my examples: 

 public class Person
{
    public string   ID        { get; set; }
    public string   Name      { get; set; }
    public int      Age       { get; set; }
    public double   Salary    { get; set; }
    public DateTime Born      { get; set; }
    public int      IdAddress { get; set; }
}

public class Address
{
    public int    IdAddress { get; set; }
    public string Street    { get; set; }
    public int    Num       { get; set; }
    public string City      { get; set; }
}    

These are the default values for the Person Class:

These are the default values for the Address Class:

 

My extenssion methods library has six extenssions methods. The main method INNER JOIN, was developed in the linq base library. The following methods will be explained:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL OUTER JOIN
  5. LEFT JOIN EXCLUDING INNER JOIN
  6. RIGHT JOIN EXCLUDING INNER JOIN
  7. FULL OUTER JOIN EXCLUDING INNER JOIN 

 

INNER JOIN  

 

This is the main method, It has been implemented in the .NET Framework, so there is no extension method for it. 

var result = from p in Person.BuiltPersons()
             join a in Address.BuiltAddresses()
             on p.IdAddress equals a.IdAddress
             select new 
	   { 
                 Name             = a.MyPerson.Name,
                 Age              = a.MyPerson.Age,
                 PersonIdAddress  = a.MyPerson.IdAddress,
                 AddressIdAddress = a.MyAddress.IdAddress,
                 Street           = a.MyAddress.Street
	   };  

 Lambda Expression: 

var resultJoint = Person.BuiltPersons().Join(                      /// Source Collection
                  Address.BuiltAddresses(),                        /// Inner Collection
                  p => p.IdAddress,                                /// PK
                  a => a.IdAddress,                                /// FK
                  (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                  .Select(a => new
                    {
                        Name             = a.MyPerson.Name,
                        Age              = a.MyPerson.Age,
                        PersonIdAddress  = a.MyPerson.IdAddress,
                        AddressIdAddress = a.MyAddress.IdAddress,
                        Street           = a.MyAddress.Street
                    });  

 As we can see, the extension method has 5 main parts that will be shared for the rest of the extension methods:

  1. Is the main Collection. 
  2. Is the inner Collection.
  3. Is the PK.
  4. Is the FK.
  5. Is the type for the result collection. 

 Results of the previous query:

 As we can see, PersonIdAddresses values matchs with the AddressIdAddesses. 

LEFT JOIN  

 

Extension Method: 

public static IEnumerable<TResult> 
	LeftJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                 IEnumerable<TInner> inner, 
                                                 Func<TSource, TKey> pk, 
                                                 Func<TInner, TKey> fk, 
                                                 Func<TSource, TInner, TResult> result)
{
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
 
    _result = from s in source
              join i in inner
              on pk(s) equals fk(i) into joinData
              from left in joinData.DefaultIfEmpty()
              select result(s, left);
 
    return _result;
}  

 Lambda Expression: 

var resultJoint = Person.BuiltPersons().LeftJoin(                    /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                    .Select(a => new
                    {
                        Name             = a.MyPerson.Name,
                        Age              = a.MyPerson.Age,
                        PersonIdAddress  = a.MyPerson.IdAddress,
                        AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
			Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")
                    }); 

We have to pay attention here, at the moment of call the select method and built our new result type we must control the values returned by the Address class, because the returned object can be null, and in that case, the reading of any of its properties would throw a NullReferenceException. 

 Results of the previous query: 

 

RIGHT JOIN  

 

Extension Method: 

public static IEnumerable<TResult> 
	RightJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                  IEnumerable<TInner> inner,
                                                  Func<TSource, TKey> pk,
                                                  Func<TInner, TKey> fk,
                                                  Func<TSource, TInner, TResult> result)
{
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
 
    _result  = from i in inner
                join s in source
                on fk(i) equals pk(s) into joinData
                from right in joinData.DefaultIfEmpty()
                select result(right, i);
 
    return _result;
}

 Lambda Expression: 

var resultJoint = Person.BuiltPersons().RightJoin(                   /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                    .Select(a => new
                    {
                        Name           = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"),
                        Age              = (a.MyPerson != null ? a.MyPerson.Age : -1),
                        PersonIdAddress  = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),
                        AddressIdAddress = a.MyAddress.IdAddress,
                        Street           = a.MyAddress.Street
                    }); 

Note that, we must coltrol null values in the Person Class in order to avoid exceptions.

 Results of the previous query: 

FULL OUTER JOIN  

 

Extension Method: 

public static IEnumerable<TResult> 
	FullOuterJoinJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                          IEnumerable<TInner> inner,
                                                          Func<TSource, TKey> pk,
                                                          Func<TInner, TKey> fk,
                                                          Func<TSource, TInner, TResult> result)
{
 
    var left = source.LeftJoin(inner, pk, fk, result).ToList();
    var right = source.RightJoin(inner, pk, fk, result).ToList();
 
    return left.Union(right);
} 

 Lambda Expression: 

var resultJoint = Person.BuiltPersons().FullOuterJoinJoin(           /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                    .Select(a => new
                    {
                        Name             = (a.MyPerson  != null ? a.MyPerson.Name       : "Null-Value"),
                        Age              = (a.MyPerson  != null ? a.MyPerson.Age        : -1),
                        PersonIdAddress  = (a.MyPerson  != null ? a.MyPerson.IdAddress  : -1),
                        AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
                        Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")
                    }); 

Note that we must control null values in both classes.

 Results of the previous query: 

LEFT EXCLUDING JOIN 

 

Extension Method: 

public static IEnumerable<TResult> 
	LeftExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                          IEnumerable<TInner> inner,
                                                          Func<TSource, TKey> pk,
                                                          Func<TInner, TKey> fk,
                                                          Func<TSource, TInner, TResult> result)
{
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
 
    _result = from s in source
                join i in inner
                on pk(s) equals fk(i) into joinData
                from left in joinData.DefaultIfEmpty()
                where left == null
                select result(s, left);
 
    return _result;
} 

 Lambda Expression:  

var resultJoint = Person.BuiltPersons().LeftExcludingJoin(           /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                    .Select(a => new
                    {
                        Name             = a.MyPerson.Name,
                        Age              = a.MyPerson.Age,
                        PersonIdAddress  = a.MyPerson.IdAddress,
                        AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
                        Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")
                    }); 

Note that we must control null values in Adress class. 

 Results of the previous query:  

 

RIGHT EXCLUDING JOIN  

Extension Method:  

public static IEnumerable<TResult> 
     RightExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                        IEnumerable<TInner> inner,
                                                        Func<TSource, TKey> pk,
                                                        Func<TInner, TKey> fk,
                                                        Func<TSource, TInner, TResult> result)
{
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
 
    _result = from i in inner
                join s in source
                on fk(i) equals pk(s) into joinData
                from right in joinData.DefaultIfEmpty()
                where right == null
                select result(right, i);
 
    return _result;
} 

 Lambda Expression:   

var resultJoint = Person.BuiltPersons().RightExcludingJoin(          /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                    .Select(a => new
                    {
                        Name             = (a.MyPerson != null ? a.MyPerson.Name      : "Null-Value"),
                        Age              = (a.MyPerson != null ? a.MyPerson.Age       : -1),
                        PersonIdAddress  = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),
                        AddressIdAddress = a.MyAddress.IdAddress,
                        Street           = a.MyAddress.Street
                    }); 

Note that we must control null values in Person class. 

 Results of the previous query:   

 

FULL OUTER EXCLUDING JOIN    

 

Extension Method:  

public static IEnumerable<TResult> 
   FulltExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
                                                      IEnumerable<TInner> inner,
                                                      Func<TSource, TKey> pk,
                                                      Func<TInner, TKey> fk,
                                                      Func<TSource, TInner, TResult> result)
{
    var left = source.LeftExcludingJoin(inner, pk, fk, result).ToList();
    var right = source.RightExcludingJoin(inner, pk, fk, result).ToList();
 
    return left.Union(right);
} 

 Lambda Expression:   

var resultJoint = Person.BuiltPersons().FulltExcludingJoin(          /// Source Collection
                    Address.BuiltAddresses(),                        /// Inner Collection
                    p => p.IdAddress,                                /// PK
                    a => a.IdAddress,                                /// FK
                    (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection
                    .Select(a => new
                    {
                        Name             = (a.MyPerson  != null ? a.MyPerson.Name       : "Null-Value"),
                        Age              = (a.MyPerson  != null ? a.MyPerson.Age        : -1),
                        PersonIdAddress  = (a.MyPerson  != null ? a.MyPerson.IdAddress  : -1),
                        AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
                        Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")
                    }); 

Note that we must control null values in both classes. 

 Results of the previous query:    

 

 

 -- THE BEST SOLUTION

I believe that is the best solution for a OOP developer. 

var GroupPersons = this.Persons.GroupJoin(this.Addresses,     /// Inner Collection
                                          p => p.IdAddress,   /// PK
                                          a => a.IdAddress,   /// FK
                                          (p, a) =>           /// Result Collection
                                          new { 
                                                  MyPerson  = p, 
                                                  Addresses = a.Select(ad => ad).ToList() 
                                               }).ToList();   

 or 

var GroupAddresses = this.Addresses.GroupJoin(this.Persons,         /// Inner Collection
                                              a => a.IdAddress,     /// PK
                                              p => p.IdAddress,     /// FK
                                              (a, p) =>             /// Result Collection
                                              new { 
                                                     MyAddress = a, 
                                                     Persons   = p.Select(ps => ps).ToList() 
                                                  }).ToList();   

Code for fill the treview: 

foreach (var data in GroupPersons)
{
    TreeViewItem tbi = new TreeViewItem{ Header = data.MyPerson };
    this.treePersons.Items.Add(tbi);
    foreach (var d in data.Addresses)
    {
        TreeViewItem tbiChild = 
		new TreeViewItem { Header = d , Background = Brushes.Gainsboro };
        this.treePersons.Items.OfType<TreeViewItem>().Last().Items.Add(tbiChild);
    }                        
}    

or 

foreach (var data in GroupAddresses)
{
    TreeViewItem tbi = new TreeViewItem{ Header = data.MyAddress };
    this.treeAddresses.Items.Add(tbi);
    foreach (var d in data.Persons)
    {
        TreeViewItem tbiChild = 
		new TreeViewItem { Header = d , Background = Brushes.Gainsboro };
        this.treeAddresses.Items.OfType<TreeViewItem>().Last().Items.Add(tbiChild);
    }                         
}    

Results:

 

 We change the  IdAddress values, we must do that in order to see more clearly . 

   

Results:

Application Testing 

In the test application, we can change the values of the Person and Address collections, and choose the join to apply the changes will be applied on the result collections.  

 

 

Thanks  

Thanks to Santiago Sánchez and Cesar Sanz for their English.  

 

 

My Blog  (in spanish) 

 .Net al Punto 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Juan Francisco Morales Larios
Software Developer (Senior) Cecabank
Spain Spain
MCPD - Designing and Developing Windows Applications .NET Framework 4
MCTS - Windows Applications Development .NET Framework 4
MCTS - Accessing Data Development .NET Framework 4
MCTS - WCF Development .NET Framework 4
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionVery Helpful PinmemberMember 1132116318-Dec-14 8:36 
GeneralAwesome Pinmembershahabj10-Dec-14 11:15 
GeneralMy vote of 5 Pinmembersrilekhamenon3-Nov-14 2:03 
Bugthe same value is removed when full join Pinmemberb0sus10-May-14 16:49 
GeneralVery useful Linq joins article PinmemberNaresh Kothur16-Apr-14 5:55 
QuestionIllustration is self explanatory Pinmembermoyeenm11-Mar-14 0:20 
QuestionBeautifully crafted and written article. PinmemberNigel Shaw3-Feb-14 16:52 
QuestionMy vote of 5 PinmemberPeterNguyen2-Jan-14 6:06 
GeneralVery Nice Article PinmemberArpitNagar27-Oct-13 22:56 
QuestionSuggest : Enumerate parameters to List PinmemberaliCarryme16-Sep-13 3:28 
GeneralMy vote of 5 PinmemberNidalNazer9-Jan-13 6:38 
GeneralMy vote of 5 PinmemberMihai MOGA14-Dec-12 6:10 
QuestionVery nice PinmemberCIDev12-Dec-12 6:12 
GeneralMy vote of 5 PinmemberSavalia Manoj M10-Dec-12 21:53 
GeneralMy vote of 5 PinmentorMd. Marufuzzaman9-Dec-12 1:55 
Questionmy 5! PinmemberMohammad A Rahman6-Dec-12 14:14 
QuestionHello from shiv PinmemberShivprasad koirala1-Dec-12 19:24 
Hello Juan Francisco Morales Larios,
 
We would like to print this article in a hard bind news paper which is published in India. I am not able to locate your email address for detail communication.
 
Can you send me a test mail at shiv_koirala@yahoo.com
My book .NET interview questions with 500 mostly asked questions in .NET world .NET Interview questions and answers

GeneralMy vote of 5 Pinmembertecgoblin12-Nov-12 12:51 
GeneralRe: My vote of 5 PinmemberJuan Francisco Morales Larios13-Nov-12 9:44 
GeneralRe: My vote of 5 Pinmembertecgoblin13-Nov-12 9:48 
GeneralMy vote of 5 Pinmemberklay11-Nov-12 18:35 
GeneralMy vote of 5 PinmemberRachelRubi9-Nov-12 1:25 
GeneralMy vote of 5 PinmemberMarius Bancila6-Nov-12 6:32 
GeneralRe: My vote of 5 PinmemberJuan Francisco Morales Larios9-Nov-12 22:15 
GeneralMy vote of 5 PinmemberAdam David Hill6-Nov-12 2:18 
GeneralRe: My vote of 5 PinmemberJuan Francisco Morales Larios6-Nov-12 7:59 
GeneralMy vote of 5 Pinmemberhoernchenmeister5-Nov-12 21:58 
SuggestionWell written piece PinmvpEspen Harlinn5-Nov-12 8:04 
GeneralRe: Well written piece PinmemberJuan Francisco Morales Larios5-Nov-12 8:32 
QuestionIt's an excelent article. Pinmember_OFF_5-Nov-12 6:05 
QuestionAll of these are handled in much easier ways PinmemberJohnGalt175-Nov-12 5:27 
AnswerRe: All of these are handled in much easier ways PinmemberJuan Francisco Morales Larios5-Nov-12 8:36 
AnswerRe: All of these are handled in much easier ways PinmemberJuan Francisco Morales Larios5-Nov-12 8:38 
GeneralRe: All of these are handled in much easier ways PinmemberJohnGalt175-Nov-12 8:42 
GeneralRe: All of these are handled in much easier ways PinmemberJuan Francisco Morales Larios5-Nov-12 10:20 
QuestionGreat Support......... Pinmemberswapnilmalap5654-Nov-12 18:55 
GeneralMy vote of 5 PinmemberMaximilian Haru Raditya4-Nov-12 15:14 
GeneralMy vote of 5 PinmemberCarsten V2.04-Nov-12 9:14 
QuestionVery useful and nice article. My five stars! PinmemberSantiago Sanchez4-Nov-12 7:24 
GeneralMy vote of 5 PinmemberSantiago Sanchez4-Nov-12 7:23 
QuestionVery good. Source code? PinmemberPHS2413-Nov-12 23:17 
AnswerRe: Very good. Source code? PinmemberMoralesLarios4-Nov-12 0:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 27 Oct 2013
Article Copyright 2012 by Juan Francisco Morales Larios
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid