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. }
  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>();
  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);
  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>();
  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);
  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. {
  10. var left = source.LeftJoin(inner, pk, fk, result).ToList();
  11. var right = source.RightJoin(inner, pk, fk, result).ToList();
  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>();
  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);
  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>();
  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);
  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();
  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