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 knowledge, 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 deeper 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 professional 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 others´ life easier by adding a sample project where everything mentioned in the article has been applied. In addition, an extension class that will be useful for those who don´t want to spent too much time coding each and every concept has been added.
Installation
LinQ Extended Joins is an open source project and your code is available in Github.
Your installation is very simple, and we add a nuget package.
Add the using MoralesLarios.Linq in the class to use.
Update Code
I have added a generic restriction in the extension method:
where TSource : class where TInner : class
With this restriction, I have fixed the bug with use sequences not nullables.
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 extension methods library has six extension 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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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 five main parts that will be shared for the rest of the extension methods:
- is the main Collection
- is the inner Collection
- is the PK
- is the FK
- is the type for the result collection
Results of the previous query:
As we can see, PersonIdAddresses values match with the AddressIdAddresses.
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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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 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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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 Address 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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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( Address.BuiltAddresses(), p => p.IdAddress, a => a.IdAddress, (p, a) => new { MyPerson = p, MyAddress = a }) .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, p => p.IdAddress, a => a.IdAddress, (p, a) => new {
MyPerson = p,
Addresses = a.Select(ad => ad).ToList()
}).ToList();
or:
var GroupAddresses = this.Addresses.GroupJoin(this.Persons, a => a.IdAddress, p => p.IdAddress, (a, p) => new {
MyAddress = a,
Persons = p.Select(ps => ps).ToList()
}).ToList();
Code for fill the treeview:
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.
History
- 4th November, 2012: Initial version
