Using SQL JOIN Operation In LinQ/C#

Being LinQ an Object Relational Mapper, We can avoid writing sql query in many cases. Where its needed to be written even for simple purpose, we can achieve what we need just by calling linq provided methods with proper parameters.This facilitates to make the code more meaningful and less buggy. Join operations are done in sql for retrieve data from a complex relationship between tables, using linq we can avoid this complexity in most of the cases, therefore no more brainstorming for writing big/complex queries anymore. In this tutorial, I will give descriptive explanation along with c# code examples for performing linq to sql join operations easily and quickly. As this article focus completely on join operation, if you are a beginner linq programmer, consider my previous tutorial on getting started with linq to sql.

Take The advantages of using Primary/foreign key:

Generally, when we have some identification data of one table and we need to retrieve its related data from another table, we use join operations and this is the most simplest case of join. Such as, we have a ‘users’ table(columns: id,name,email etc) and also have a ‘articles’ table(id, title, description, userid). Below is the screen shot of how it will look like on dbml:

Now, we are about to find all articles for a specific user(given the user id). To achieve this, we will have to write a sql query something like below:

select id,title,description from articles inner join users on users.id = articles.id where users.id=10

However, if we keep up the best practice, and apply primary/foreign key accordingly, in these kind of situations, linq will help us a lot and we can achieve these goals without writing any query at all. Lets look into an example:

BlogDataContexts blogDB = new BlogDataContexts();
User user = blogDB.Users.Single(u=>u.id==10);

//we can get all info for this user
string name = user.Name;
string email = user.Email;

//we can also access the articles associated to that user
Article article = user.Articles[0];
string title = article.Title;
string description = article.Description;

As you can see. we can easily access data of another table that is associated with the user table. The only prerequisite is that, you will have to create the primary-foreign key relationship between the tables properly.

LinQ To SQL Join Operation Examples With Query Syntax:

Ok, we won’t be bound like this kind of scenario as above all the times. We will get a lot more complex scenario on the go where there will be no way but will have to make a query ourselves. So, lets move on with some example of sql like syntax for join operations with linq to sql query.

INNER Join Operations: Inner join retrieves the common related rows that matches some particular constraints(generally interconnected with some common column). lets re-use the prevously given example and implement it in linq to sql query syntax. This will be as follows:

List<Article> articles = (List<Article>) from u in blogDB.Users
                         join b in blogDB.Articles 
                         on u.Id equals b.UserId
                         where u.Id == 10                         
                         select b;

The above statement will result the same sql query we have given in the previous example.

Select New Object Type: Suppose, we want to select rows with combination of columns from both two tables(naturally, we need it), it won’t match any linq object types that already exists. So, in this case, we will have to create new type of object and assign it to ‘var’ or ‘dynamic’ type variable. Lets look into an example:

var userArticles = from u in blogDB.Users
                         join b in blogDB.Articles 
                         on u.Id equals b.UserId
                         where u.Id == 10                         
                         select new {Name=u.name,Email=u.Email,Title=b.Title,Description=b.Description};
foreach (var userArticle in userArticles)
{
   Console.WriteLine("{0} - {1}",userArticle.Name, userArticle.Title);
}

As the above code snippet shows, we can specify which columns to use and combine them and create a new dynamic object to be used later on. they can be accessed in them same way other object’s properties, only you won’t get visual studio intelligence help this time. If you want to include all columns from both tables and want to simplify this process, you can use as like follows:

var userArticles = from u in blogDB.Users
                         join b in blogDB.Articles 
                         on u.Id equals b.UserId
                         into userArticles 
                         where u.Id == 10                                                  

Left Join Operation: In Left join operations, all rows from the left hand site table(or first table) are taken which fulfill the constraints and common rows from the second tables. Well, left join isn’t actually exact similar to original sql query syntax, that means, we don’t have any keyword(‘left join’) to use. Instead, we will have to utilize a method ‘DefaultIfEmpty’ to achieve the result of left join. Ok, Here is an example to perform left join operation in linq to sql, i will explain every parts below the example:

var userArticles = from u in blogDB.Users
                         join b in blogDB.Articles 
                         on u.Id equals b.UserId
                         into userArticles
                         where u.Id == 10
                         from ua in userArticles.DefaultIfEmpty()                         
                         select new{Name=u.Name,Title = (ua==null)?"":ua.Title};

I think, you understood up to the the line where it says ‘where u.Id == 10’ very well if you have read the all the other examples above already. Now to keep all the rows from users table, we are using ‘ua in userArticles.DefaultIfEmpty()’, which means, it will includes the empty columns from both tables. Now, we will have to filter them when creating the new objects. That’s why, we are later using ‘Title = (ua==null)?””:ua.Title’ statement, that will include the rows those have empty article rows, but have non empty users row.

Right Join Operation: Right join operation does the same work as left join, just right hand table is taken special care instead of the left one. In LinQ To SQL, Right Join Operation can be achieved in the similar way as left join that we just did, just by changing the table name in the last part:

var userArticles = from u in blogDB.Users
                         join b in blogDB.Articles 
                         on u.Id equals b.UserId
                         into userArticles
                         where u.Id == 10
                         from ua in userArticles.DefaultIfEmpty()                         
                         select new{Name=(ua==null)?"":ua.Name,Title=a.Title};

Outer Join Operation:
I guess, you already can guess, how to perform the full outer join. Yes, we will just have to include properties from both tables which are empty as like follows:

var userArticles = from u in blogDB.Users
                         join b in blogDB.Articles 
                         on u.Id equals b.UserId
                         into userArticles
                         where u.Id == 10
                         from ua in userArticles.DefaultIfEmpty()                         
                         select new{Name=(ua==null)?"":ua.Name,Title=(ua==null)?"":ua.Title};

Alternative Methods To Use:
Instead of using syntax similar to sql query, we can also do the joins by calling method named ‘Join’. In this case, you will have to have the object lists on both tables to join. Below is an example that will better illustrate the usage:

List<User>users = blogDB.Users.ToList();
List<TVRole>articles= blogDB.Articles.ToList();
var userBlogs = users .Join(articles, user => user.Id, article=> article.UserId, (user, article) => new { Name = user.Name, Description = article.Description});
foreach (var userBlog in userBlogs)
{
   Console.WriteLine("{0} - {1}",userBlog.Name, userBlog.Description);
}

References:

To know more details in depth about the join operation in LinQ, consider the following links as your next to study materials:

• MSDN Reference For LinQ Join Method
• MSDN Examples On Inner Join Operations
• MSDN Examples On Left Join Operations

I hope this tutorial with above code examples will help you understand and apply linq to sql join operation.Let me know if you are having any issue. Happy coding 🙂