• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Database / Using SQL JOIN Operation In LinQ/C#

Using SQL JOIN Operation In LinQ/C#

March 30, 2011 by Rana Ahsan 4 Comments

linq to sql tutorial

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:
sample linq 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};


Read All LinQ Tutorials By CodeSamplez.com

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 🙂

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Database Tagged With: c#, linq

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. Venkat says

    May 16, 2011 at 11:56 pm

    I have gone through most of your articles. It has been really very helpful to understand the basics of LINQ, MVC3 & Razor.
    Solid building block.

    Reply
  2. Amine says

    May 6, 2015 at 8:31 am

    Hi, first thanks for the tutorials, it’s been really useful for me.

    You used DataContexts with an ‘s’ when you declared:

    BlogDataContexts blogDB = new BlogDataContexts();

    Shouldn’t it be DataContext same as in the first tutorial? I’m sorry if I missed something.

    Reply
    • Md Ali Ahsan Rana says

      May 6, 2015 at 9:39 pm

      I probably created new data context for this tutorial, can’t remember exactly though. But you should be fine with whatever data context object you are using.

      Reply
  3. chandukomati says

    July 5, 2016 at 1:34 am

    public ActionResult companydetail()
    {
    IList CompanyList = new List();
    var details = (from c in DB.cmps
    join e in DB.emp on c.Emp_Id equals e.Emp_Id
    join d in DB.dept on c.Dept_Id equals d.Dept_Id
    select new {
    Com_Id = c.Com_Id,
    Com_Name = c.Com_Name,
    Emp_Name = e.Emp_Name,
    Dept_Name = d.Dept_Name
    }).ToList();

    return View(details);
    }

    how to create View model
    can u plz help me…

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • Getting Started With UDP Programming in Java
    Getting Started With UDP Programming in Java
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#
  • How To Use Hotkeys/Keyboard Events In WPF Application Using C#
    How To Use Hotkeys/Keyboard Events In WPF Application Using C#

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023