sábado, 22 de abril de 2017

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´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 be useful for those who don´t want to spent too much time 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  is an open source project and your code is available in Git Hub.

Your installation is very simple, and we add a nuget package.








Add the using    using MoralesLarios.Linq    in the class to use.    





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:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
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)
    where TSource : class where TInner : class
{
    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)
    where TSource : class where TInner : class
{
    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)
    where TSource : class where TInner : class
{
 
    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)
    where TSource : class where TInner : class
{
    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)
    where TSource : class where TInner : class
{
    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)
    where TSource : class where TInner : class
{
    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.  





Link to download de app testing