Joins in LINQ to SQL

The following post shows how to write different types of joins in LINQ to SQL. I am using the Northwind database and LINQ to SQL for these examples.

   1: NorthwindDataContext dataContext = new NorthwindDataContext();

INNER JOIN :

   1: var q1 = from c in dataContext.Customers

   2:          join o in dataContext.Orders on c.CustomerID equals o.CustomerID

   3:          select new

   4:          {

   5:              c.CustomerID,

   6:              c.ContactName,

   7:              o.OrderID,

   8:              o.OrderDate

   9:          };

LEFT JOIN :

   1: var q2 = from c in dataContext.Customers

   2:          join o in dataContext.Orders on c.CustomerID equals o.CustomerID into g

   3:          from a in g.DefaultIfEmpty()

   4:          select new

   5:          {

   6:              c.CustomerID,

   7:              c.ContactName,

   8:              a.OrderID,

   9:              a.OrderDate

  10:          };

INNER JOIN ON MULTIPLE :

   1: var q3 = from c in dataContext.Customers

   2:          join o in dataContext.Orders on new { a = c.CustomerID, b = c.Country } equals new { a = o.CustomerID, b = "USA" }

   3:          select new

   4:          {

   5:              c.CustomerID,

   6:              c.ContactName,

   7:              o.OrderID,

   8:              o.OrderDate

   9:          };

INNER JOIN ON MULTIPLE WITH “OR” :

   1: var q4 = from c in dataContext.Customers

   2:          from o in dataContext.Orders.Where(a => a.CustomerID == c.CustomerID || c.Country == "INDIA")

   3:          select new

   4:          {

   5:              c.CustomerID,

   6:              c.ContactName,

   7:              o.OrderID,

   8:              o.OrderDate

   9:          };

LEFT JOIN ON MULTIPLE WITH “OR” :

   1: var q5 = from c in dataContext.Customers

   2:          from o in dataContext.Orders.Where(a => a.CustomerID == c.CustomerID || c.Country == "USA").DefaultIfEmpty()

   3:          select new

   4:          {

   5:              c.CustomerID,

   6:              c.ContactName,

   7:              o.OrderID,

   8:              o.OrderDate

   9:          };

More Example base on this article will post shortly 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s