LINQ join Clause – Inner Join

Internal joins are the least complex kind of join. This sort of join restores a level or  rectangular outcome. It implies that when you take a gander at the aftereffect of the inquiry, it  would resemble a table in which each cell has an esteem. Assume we have two  database tables named Authors and  Books. The Authors table contains information of  distinctive creators. The Books table contains information of various books alongside the ID of the writer that composed a specific book. Joining the two tables using  internal join will yield a rectangular outcome as found in Figure 1.

 Author  Book
 John Smith  Little Blue Riding Hood
 John Smith  Snow Black
 John Smith  Hanzel and Brittle
 Harry Gold  My Rubber Duckie
 Harry Gold  He Who Doesn’t Know His Name
 Ronald Schwimmer  The Three Little Piggy Banks

Figure 1 – A Rectangular Result

An author can have multiple books assigned to him.  In Figure 1, you can see that John Smith wrote three books, Harry Gold wrote  two, and Ronald Schwimmer wrote only one book.

Figure 2 – Inner Join

For every item in the inner data source, it searches for its  corresponding item in the outer data source and creates a combined item of  that outer item and the inner item. After the first inner item finds its  corresponding outer item, the second inner item is next and it also  searches for its corresponding item in the outer data source. This repeats until  all the inner items from the inner data source have found their corresponding  item. To determine if two items are equal, both items must have a member or  property which should have equal value. As you can see in Figure 2, Outer Item 4  was not included in the results. Any outer items that has no corresponding inner  item is not included in the results. Same goes to an inner item that has no  corresponding outer item.

Let’s take a look at the following example. The following code defines two classes, Author  and Book.

class Author
{
    public int Author { get; set; }
    public string Nameone { get; set; }
}

class Book
{
    public int Author { get; set; }
    public string Title { get; set; }
}

As you can see in the above code, both Author and Book has the same AuthorId  property. This will be the property that will determine the correspondence of a Book object to an Author   object. Note that the properties doesn’t need to have the same name but they   should have the same data type. Example 1 creates Author  and Book objects. Each book has its AuthorId pointed to a specific Author.

class Program
{
    public static void Main(string[] args)
    {
        Author[] authors = new Author[] 
        {
            new Author() { Author = 1, Nameone = "John Smith" },
            new Author() { Author = 2, Nameone = "Harry Gold" },
            new Author() { Author = 3, Nameone = "Ronald Schwimmer" },
            new Author() { Author = 4, Nameone = "Jerry Mawler" }
        };
 
        Book[] books = new Book[] 
        {
            new Book() { Author = 1, Title = "Little Blue Riding Hood" },
            new Book() { Author = 3, Title = "The Three Little Piggy Banks" },
            new Book() { Author = 1, Title = "Snow Black" },
            new Book() { Author = 2, Title = "My Rubber Duckie" },
            new Book() { Author = 2, Title = "He Who Doesn't Know His Name" },
            new Book() { Author = 1, Title = "Hanzel and Brittle" }
        };
 
        var result = from a in authors                              
                     join b in books on a.Author equals b.AuthorId
                     select new { a.Nameone, b.Title };                
 
        foreach (var r in result)
        {
            Console.WriteLine("{0} - {1}", r.Nameone, r.Title);
        }
    }
}

Example 1

John Smith - Little Blue Riding Hood
John Smith - Snow Black
John Smith - Hanzel and Brittle
Harry Gold - My Rubber Duckie
Harry Gold - He Who Doesn't Know His Name
Ronald Schwimmer - The Three Little Piggy Banks

The query expression in lines 23-25 uses a join clause. The query expression first gets an Author object from the outer data source which is the authors. Then in the join  clause, we retrieve a Book object from the inner  source named books and test if the AuthorIdof the retrieved author is equal to the  books AuthorId. Note that the equals keyword was used instead of ==. The join clause  can only test for equality. You cannot use operators such as > or < to compare  keys. So to remind you that you can only do equality comparison, Microsoft  created an equals keyword instead.

If the current book’s AuthorId is equal to an  author’s Author ID, then we can proceed to the select  clause where the Name of the  author and the Title of the book will be  projected. Each of the book is iterated and compared to each author and all of  the books with a corresponding author will be included in the results when  execution of the query begins. Note that Jerry Mawler was not included in the  results because he did not write any book, therefore, there is no item in the  inner source that can be joined to him. Also, any book which AuthorId does not exist in the authors will not be  included.

The join clause, like any other clause, is  translated during compilation into a call to the Join  method. The query expression in Figure 4 can be translated into the following  call to the Join method:

var result = authors.Join(books, 
                          author => author.Author, 
                          book => book.Author, 
                          (author, book) => new { author.Nameone, book.Title }
                         );

This version of Join method performs an inner  join. The method accepts four parameters. The first parameter accepts the inner  data source, in this case, the books collection. The second parameter accepts a  lambda expression that describes the outer key selector. It should have one  parameter which will hold the every item from the outer source and then return  the key that will be used for joining (we used the AuthorId  as the key). The third parameter accepts a lambda expression which has one  parameter that will hold every item from the inner data source, and returns the  key to be compared to the outer source’s key. Finally, the fourth parameter  accepts a lambda expression with two parameters, the outer item and inner item  that have matching keys. You can then use projection to create the type with the  results from the two items.