LINQ Extended Joins

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 have 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.

I'll try to make your  life easier by adding a sample project, where everything mentioned in the article has been applied. In addition, it includes an extension class that will be useful for those, who don´t want to spent too much time in coding each and every concept. 

Index

  • Installation
  • Using the code
  • Inner Join
  • Left Join
  • Right Join
  • Full Outer Join
  • Left Excluding Join
  • Right Excluding Join
  • Full Outer Excluding Join

The Best Solution (for me)

Application Testing

Installation

LinQ Extended Joins refers to an open source project and your code is available in GitHub.

Your installation is very simple and we will add a NuGet Package.
LinQ

Add it, using MoralesLarios.Linq in the class.

Using the code 

I will use two classes to demonstrate my examples, which are given below. 

  1. public class Person  
  2. {  
  3.     public string   ID        { get; set; }  
  4.     public string   Name      { get; set; }  
  5.     public int      Age       { get; set; }  
  6.     public double   Salary    { get; set; }  
  7.     public DateTime Born      { get; set; }  
  8.     public int      IdAddress { get; set; }  
  9. }  
  10.   
  11. public class Address  
  12. {  
  13.     public int    IdAddress { get; set; }  
  14.     public string Street    { get; set; }  
  15.     public int    Num       { get; set; }  
  16.     public string City      { get; set; }  
  17. }   

These are the default values for Person class.

LinQ

These are the default values for the Address Class.

LinQ

My extension methods library has six extension methods. The main method INNER JOIN was developed in LINQ based library. The methods given below will be explained,

  • Inner Join
  • Left JOIN
  • Right Join
  • Full Outer Join
  • Left Join excluding Inner Join
  • Right Join excluding Right Join excluding Inner Join
  • Full Outer Join excluding Inner Join 

INNER JOIN

LinQ

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

  1. var result = from p in Person.BuiltPersons()  
  2.              join a in Address.BuiltAddresses()  
  3.              on p.IdAddress equals a.IdAddress  
  4.              select new   
  5.        {   
  6.                  Name             = a.MyPerson.Name,  
  7.                  Age              = a.MyPerson.Age,  
  8.                  PersonIdAddress  = a.MyPerson.IdAddress,  
  9.                  AddressIdAddress = a.MyAddress.IdAddress,  
  10.                  Street           = a.MyAddress.Street  
  11.        };   

Lambda Expression

  1. var result = from p in Person.BuiltPersons()  
  2.              join a in Address.BuiltAddresses()  
  3.              on p.IdAddress equals a.IdAddress  
  4.              select new   
  5.        {   
  6.                  Name             = a.MyPerson.Name,  
  7.                  Age              = a.MyPerson.Age,  
  8.                  PersonIdAddress  = a.MyPerson.IdAddress,  
  9.                  AddressIdAddress = a.MyAddress.IdAddress,  
  10.                  Street           = a.MyAddress.Street  
  11.        };   

 As we can see, the extension method has 5 main parts, which will be shared for the rest of the extension methods, which are given 

  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. 

 Result of the previous query is given below.

LinQ

As we can see, PersonIdAddresses value matches with the AddressIdAddesses. 

Left Join 

LinQ

Extension Method 

  1. public static IEnumerable<TResult>   
  2.     LeftJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,  
  3.                                                  IEnumerable<TInner> inner,   
  4.                                                  Func<TSource, TKey> pk,   
  5.                                                  Func<TInner, TKey> fk,   
  6.                                                  Func<TSource, TInner, TResult> result)  
  7.                 where TSource : class where TInner : class  
  8. {  
  9.     IEnumerable<TResult> _result = Enumerable.Empty<TResult>();  
  10.    
  11.     _result = from s in source  
  12.               join i in inner  
  13.               on pk(s) equals fk(i) into joinData  
  14.               from left in joinData.DefaultIfEmpty()  
  15.               select result(s, left);  
  16.    
  17.     return _result;  
  18. }    

Lambda expression

  1. var resultJoint = Person.BuiltPersons().LeftJoin(                    /// Source Collection  
  2.                     Address.BuiltAddresses(),                        /// Inner Collection  
  3.                     p => p.IdAddress,                                /// PK  
  4.                     a => a.IdAddress,                                /// FK  
  5.                     (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection  
  6.                     .Select(a => new  
  7.                     {  
  8.                         Name             = a.MyPerson.Name,  
  9.                         Age              = a.MyPerson.Age,  
  10.                         PersonIdAddress  = a.MyPerson.IdAddress,  
  11.                         AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),  
  12.              Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")  
  13.                     });    

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

Result of the previous query is given below. 

LinQ

Right Join

LinQ

Extension Method 

  1. public static IEnumerable<TResult>   
  2.     RightJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,  
  3.                                                   IEnumerable<TInner> inner,  
  4.                                                   Func<TSource, TKey> pk,  
  5.                                                   Func<TInner, TKey> fk,  
  6.                                                   Func<TSource, TInner, TResult> result)  
  7.                 where TSource : class where TInner : class  
  8. {  
  9.     IEnumerable<TResult> _result = Enumerable.Empty<TResult>();  
  10.    
  11.     _result  = from i in inner  
  12.                 join s in source  
  13.                 on fk(i) equals pk(s) into joinData  
  14.                 from right in joinData.DefaultIfEmpty()  
  15.                 select result(right, i);  
  16.    
  17.     return _result;  
  18. }   

Lambda Expression

  1. var resultJoint = Person.BuiltPersons().RightJoin(                   /// Source Collection  
  2.                     Address.BuiltAddresses(),                        /// Inner Collection  
  3.                     p => p.IdAddress,                                /// PK  
  4.                     a => a.IdAddress,                                /// FK  
  5.                     (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection  
  6.                     .Select(a => new  
  7.                     {  
  8.                         Name           = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"),  
  9.                         Age              = (a.MyPerson != null ? a.MyPerson.Age : -1),  
  10.                         PersonIdAddress  = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),  
  11.                         AddressIdAddress = a.MyAddress.IdAddress,  
  12.                         Street           = a.MyAddress.Street  
  13.                     });   

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

Result of the previous query is given below.

LinQ

Full Outer Join 

LinQ

Extension Method 

  1. public static IEnumerable<TResult>   
  2.     FullOuterJoinJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,  
  3.                                                           IEnumerable<TInner> inner,  
  4.                                                           Func<TSource, TKey> pk,  
  5.                                                           Func<TInner, TKey> fk,  
  6.                                                           Func<TSource, TInner, TResult> result)  
  7.                 where TSource : class where TInner : class  
  8. {  
  9.    
  10.     var left = source.LeftJoin(inner, pk, fk, result).ToList();  
  11.     var right = source.RightJoin(inner, pk, fk, result).ToList();  
  12.    
  13.     return left.Union(right);  
  14. }    

Lambda Expression 

  1. var resultJoint = Person.BuiltPersons().FullOuterJoinJoin(           /// Source Collection  
  2.                     Address.BuiltAddresses(),                        /// Inner Collection  
  3.                     p => p.IdAddress,                                /// PK  
  4.                     a => a.IdAddress,                                /// FK  
  5.                     (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection  
  6.                     .Select(a => new  
  7.                     {  
  8.                         Name             = (a.MyPerson  != null ? a.MyPerson.Name       : "Null-Value"),  
  9.                         Age              = (a.MyPerson  != null ? a.MyPerson.Age        : -1),  
  10.                         PersonIdAddress  = (a.MyPerson  != null ? a.MyPerson.IdAddress  : -1),  
  11.                         AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),  
  12.                         Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")  
  13.                     });   

Note that we must control null values in both classes.

Result of the previous query is given below.

LinQ

Left Excluding Join

LinQ

Extension Method

  1. public static IEnumerable<TResult>   
  2.     LeftExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,  
  3.                                                           IEnumerable<TInner> inner,  
  4.                                                           Func<TSource, TKey> pk,  
  5.                                                           Func<TInner, TKey> fk,  
  6.                                                           Func<TSource, TInner, TResult> result)  
  7.                 where TSource : class where TInner : class  
  8. {  
  9.     IEnumerable<TResult> _result = Enumerable.Empty<TResult>();  
  10.    
  11.     _result = from s in source  
  12.                 join i in inner  
  13.                 on pk(s) equals fk(i) into joinData  
  14.                 from left in joinData.DefaultIfEmpty()  
  15.                 where left == null  
  16.                 select result(s, left);  
  17.    
  18.     return _result;  
  19. }   

Lambda Expression

  1. var resultJoint = Person.BuiltPersons().LeftExcludingJoin(           /// Source Collection  
  2.                     Address.BuiltAddresses(),                        /// Inner Collection  
  3.                     p => p.IdAddress,                                /// PK  
  4.                     a => a.IdAddress,                                /// FK  
  5.                     (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection  
  6.                     .Select(a => new  
  7.                     {  
  8.                         Name             = a.MyPerson.Name,  
  9.                         Age              = a.MyPerson.Age,  
  10.                         PersonIdAddress  = a.MyPerson.IdAddress,  
  11.                         AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),  
  12.                         Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")  
  13.                     });  

Note that we must control null values in an Address class. 

Result of the previous query is given below.

LinQ

Right Excluding Join

LinQ

Extension Method

  1. public static IEnumerable<TResult>   
  2.      RightExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,  
  3.                                                         IEnumerable<TInner> inner,  
  4.                                                         Func<TSource, TKey> pk,  
  5.                                                         Func<TInner, TKey> fk,  
  6.                                                         Func<TSource, TInner, TResult> result)  
  7.                 where TSource : class where TInner : class  
  8. {  
  9.     IEnumerable<TResult> _result = Enumerable.Empty<TResult>();  
  10.    
  11.     _result = from i in inner  
  12.                 join s in source  
  13.                 on fk(i) equals pk(s) into joinData  
  14.                 from right in joinData.DefaultIfEmpty()  
  15.                 where right == null  
  16.                 select result(right, i);  
  17.    
  18.     return _result;  
  19. }   

Lambda Expression

  1. var resultJoint = Person.BuiltPersons().RightExcludingJoin(          /// Source Collection  
  2.                     Address.BuiltAddresses(),                        /// Inner Collection  
  3.                     p => p.IdAddress,                                /// PK  
  4.                     a => a.IdAddress,                                /// FK  
  5.                     (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection  
  6.                     .Select(a => new  
  7.                     {  
  8.                         Name             = (a.MyPerson != null ? a.MyPerson.Name      : "Null-Value"),  
  9.                         Age              = (a.MyPerson != null ? a.MyPerson.Age       : -1),  
  10.                         PersonIdAddress  = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),  
  11.                         AddressIdAddress = a.MyAddress.IdAddress,  
  12.                         Street           = a.MyAddress.Street  
  13.                     });   

Note that we must control null values in Person class. 

Results of the previous query,

LinQ

Full Outer Excluding Join 

LinQ

Extension Method

  1. public static IEnumerable<TResult>   
  2.    FulltExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,  
  3.                                                       IEnumerable<TInner> inner,  
  4.                                                       Func<TSource, TKey> pk,  
  5.                                                       Func<TInner, TKey> fk,  
  6.                                                       Func<TSource, TInner, TResult> result)  
  7.                 where TSource : class where TInner : class  
  8. {  
  9.     var left = source.LeftExcludingJoin(inner, pk, fk, result).ToList();  
  10.     var right = source.RightExcludingJoin(inner, pk, fk, result).ToList();  
  11.    
  12.     return left.Union(right);  
  13. }  

Lambda Expression

  1. var resultJoint = Person.BuiltPersons().FulltExcludingJoin(          /// Source Collection  
  2.                     Address.BuiltAddresses(),                        /// Inner Collection  
  3.                     p => p.IdAddress,                                /// PK  
  4.                     a => a.IdAddress,                                /// FK  
  5.                     (p, a) => new { MyPerson = p, MyAddress = a })   /// Result Collection  
  6.                     .Select(a => new  
  7.                     {  
  8.                         Name             = (a.MyPerson  != null ? a.MyPerson.Name       : "Null-Value"),  
  9.                         Age              = (a.MyPerson  != null ? a.MyPerson.Age        : -1),  
  10.                         PersonIdAddress  = (a.MyPerson  != null ? a.MyPerson.IdAddress  : -1),  
  11.                         AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),  
  12.                         Street           = (a.MyAddress != null ? a.MyAddress.Street    : "Null-Value")  
  13.                     });  

Note that we must control null values in both the classes. 

Result of the previous query is given below.

LinQ

-- THE BEST SOLUTION

I believe that is the best solution for an OOPS developer. 

  1. var GroupPersons = this.Persons.GroupJoin(this.Addresses,     /// Inner Collection  
  2.                                           p => p.IdAddress,   /// PK  
  3.                                           a => a.IdAddress,   /// FK  
  4.                                           (p, a) =>           /// Result Collection  
  5.                                           new {   
  6.                                                   MyPerson  = p,   
  7.                                                   Addresses = a.Select(ad => ad).ToList()   
  8.                                                }).ToList();     
  9.  or   
  10. var GroupAddresses = this.Addresses.GroupJoin(this.Persons,         /// Inner Collection  
  11.                                               a => a.IdAddress,     /// PK  
  12.                                               p => p.IdAddress,     /// FK  
  13.                                               (a, p) =>             /// Result Collection  
  14.                                               new {   
  15.                                                      MyAddress = a,   
  16.                                                      Persons   = p.Select(ps => ps).ToList()   
  17.                                                   }).ToList();   

Code to fill the treview is given below. 

  1. foreach (var data in GroupPersons)  
  2. {  
  3.     TreeViewItem tbi = new TreeViewItem{ Header = data.MyPerson };  
  4.     this.treePersons.Items.Add(tbi);  
  5.     foreach (var d in data.Addresses)  
  6.     {  
  7.         TreeViewItem tbiChild =   
  8.         new TreeViewItem { Header = d , Background = Brushes.Gainsboro };  
  9.         this.treePersons.Items.OfType<TreeViewItem>().Last().Items.Add(tbiChild);  
  10.     }                          
  11. }      
  12. or   
  13. foreach (var data in GroupAddresses)  
  14. {  
  15.     TreeViewItem tbi = new TreeViewItem{ Header = data.MyAddress };  
  16.     this.treeAddresses.Items.Add(tbi);  
  17.     foreach (var d in data.Persons)  
  18.     {  
  19.         TreeViewItem tbiChild =   
  20.         new TreeViewItem { Header = d , Background = Brushes.Gainsboro };  
  21.         this.treeAddresses.Items.OfType<TreeViewItem>().Last().Items.Add(tbiChild);  
  22.     }                           
  23. }    

Results are given below.

LinQ

We changed the IdAddress values and we must do that in order to see more clearly. 

LinQ

The result is given below.

LinQ

Application Testing 

In the Test Application, we can change the values of the Person and Address collections and choose the join to apply so that the changes will be applied on the result collections.

LinQ