Joining Data Sources
There are times where you want to combine values from different data sources into just one result. Of course, those data sources must be related in some way. In LINQ, you can use the join clause or the Join() method to join multiple data sources with properties or fields that can be tested for equivalency. With the join clause or the Join() method, you can do inner joins, group joins, or left outer joins. The concepts of joins in LINQ can be compared to joins in SQL. If you know how to do joins in SQL, then you may find the following concepts very familiar. Joins can be very hard to understand for a beginner so I will try my best to explain every concept as clear as possible.You will see the way to use the join clause within the next lesson.
Consider associate Author info table containing names of authors and their respective AuthorId. Another table named Books which contains records of books with their titles and the AuthorId of the author that wrote them. One can join this two tables which mean, each record of the result of a query is a combination of values from each of the table. A combined record, for example, will have the Nameof the author and the Title of the book. For two data sources to be joint together, each item or record must have a key that will be tested for equivalence. Only the two records which have equivalent keys will be combined. In our Authors and Books example, we can add an AuthorId field to both of the tables. An author can have an AuthorId that will uniquely identify him while a book can have an AuthorId that determines which author wrote that book.
In a join, there is an inner data source and an outer data source. The inner data source contains items which will be combined to the outer data source.Each of the inner item searches for an identical outer item and therefore the 2 things is joined to make one new record.
The following lessons discusses three types of joins. Inner joins allow you to combine two data sources and create a rectangular result. During an inner join, outer items that have no corresponding inner item are not included in the result set. Inner joins are the simplest and easiest type of join. Another type of join is the group join, which produces a hierarchical result set. It groups related items from one source by an item from another table. For example, you can place all the books written by a certain author into a group. Left outer joins are similar to an inner join as it also creates a rectangular result set, but it also includes outer items which have no corresponding inner item. You will learn more about each of this types of join in the following lessons.
Note that you can also do joins using multiple from clauses, but it will require you to properly structure your classes when defining them. For example, an Author can have a property named Books, which contains a collection of Book objects that the author writes. Join clause can be effectively used if both classes have no defined relationship. We just need to define the key property to be compared during the join operation.