Export (0) Print
Expand All

How to: Formulate Joins and Cross-Product Queries (LINQ to SQL)

The following examples show how to combine results from multiple tables.

The following example uses foreign key navigation in the From clause in Visual Basic (from clause in C#) to select all orders for customers in London.


var infoQuery =
    from cust in db.Customers
    from ord in cust.Orders
    where cust.City == "London"
    select ord;


The following example uses foreign key navigation in the Where clause in Visual Basic (where clause in C#) to filter for out-of-stock Products whose Supplier is in the United States.


var infoQuery =
    from prod in db.Products
    where prod.Supplier.Country == "USA" && prod.UnitsInStock == 0
    select prod;


The following example uses foreign key navigation in the From clause in Visual Basic (from clause in C#) to filter for employees in Seattle and to list their territories.


var infoQuery =
    from emp in db.Employees
    from empterr in emp.EmployeeTerritories
    where emp.City == "Seattle"
    select new
    {
        emp.FirstName,
        emp.LastName,
        empterr.Territory.TerritoryDescription
    };


The following example uses foreign key navigation in the Select clause in Visual Basic (select clause in C#) to filter for pairs of employees where one employee reports to the other and where both employees are from the same City.


            var infoQuery =
                from emp1 in db.Employees
                from emp2 in emp1.Employees
                where emp1.City == emp2.City
                select new
                {
                    FirstName1 = emp1.FirstName,
                    LastName1 = emp1.LastName,
                    FirstName2 = emp2.FirstName,
                    LastName2 = emp2.LastName,
                    emp1.City
                };



The following Visual Basic example looks for all customers and orders, makes sure that the orders are matched to customers, and guarantees that for every customer in that list, a contact name is provided.


Dim q1 = From c In db.Customers, o In db.Orders _
    Where c.CustomerID = o.CustomerID _
    Select c.CompanyName, o.ShipRegion

' Note that because the O/R designer generates class
' hierarchies for database relationships for you,
' the following code has the same effect as the above
' and is shorter:

Dim q2 = From c In db.Customers, o In c.Orders _
    Select c.CompanyName, o.ShipRegion

For Each nextItem In q2
    Console.WriteLine("{0}   {1}", nextItem.CompanyName, _
        nextItem.ShipRegion)
Next


The following example explicitly joins two tables and projects results from both tables.


var q =
    from c in db.Customers
    join o in db.Orders on c.CustomerID equals o.CustomerID
        into orders
    select new { c.ContactName, OrderCount = orders.Count() };


The following example explicitly joins three tables and projects results from each of them.


var q =
    from c in db.Customers
    join o in db.Orders on c.CustomerID equals o.CustomerID
        into ords
    join e in db.Employees on c.City equals e.City into emps
    select new
    {
        c.ContactName,
        ords = ords.Count(),
        emps = emps.Count()
    };


The following example shows how to achieve a LEFT OUTER JOIN by using DefaultIfEmpty(). The DefaultIfEmpty() method returns null when there is no Order for the Employee.


var q =
    from e in db.Employees
    join o in db.Orders on e equals o.Employee into ords
        from o in ords.DefaultIfEmpty()
        select new { e.FirstName, e.LastName, Order = o };


The following example projects a let expression resulting from a join.


var q =
    from c in db.Customers
    join o in db.Orders on c.CustomerID equals o.CustomerID
        into ords
    let z = c.City + c.Country
        from o in ords
        select new { c.ContactName, o.OrderID, z };


The following example shows a join with a composite key.


var q =
    from o in db.Orders
    from p in db.Products
    join d in db.OrderDetails
        on new { o.OrderID, p.ProductID } equals new
    {
        d.OrderID,
        d.ProductID
    } into details
        from d in details
        select new { o.OrderID, p.ProductID, d.UnitPrice };


The following example shows how to construct a join where one side is nullable and the other is not.


var q =
    from o in db.Orders
    join e in db.Employees
        on o.EmployeeID equals (int?)e.EmployeeID into emps
        from e in emps
        select new { o.OrderID, e.FirstName };


Show:
© 2014 Microsoft