Formulate Joins and Cross-Product Queries

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

Example

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;
Dim infoQuery = _
From cust In db.Customers, ord In cust.Orders _
Where cust.City = "London" _
Select ord

Example

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;
Dim infoQuery = _
    From prod In db.Products _
    Where prod.Supplier.Country = "USA" AndAlso _
        CShort(prod.UnitsInStock) = 0 _
    Select prod

Example

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
    };

Example

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
    };

Dim infoQuery = _
    From e1 In db.Employees, e2 In e1.Employees _
    Where e1.City = e2.City _
    Select FirstName1 = e1.FirstName, _
        LastName1 = e1.LastName, FirstName2 = e2.FirstName, _
        LastName2 = e2.LastName, e1.City

Example

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

Example

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() };
Dim q = From c In db.Customers _
    Group Join o In db.Orders On c.CustomerID Equals o.CustomerID _
        Into orders = Group _
    Select c.ContactName, OrderCount = orders.Count()

Example

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()
    };
Dim q = From c In db.Customers _
    Group Join o In db.Orders On c.CustomerID Equals o.CustomerID _
        Into ords = Group _
        Group Join e In db.Employees On c.City Equals e.City _
            Into emps = Group _
    Select c.ContactName, ords = ords.Count(), emps = emps.Count()

Example

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 };
Dim q = From e In db.Employees() _
    Group Join o In db.Orders On e Equals o.Employee Into ords _
        = Group _
    From o In ords.DefaultIfEmpty() _
    Select e.FirstName, e.LastName, Order = o

Example

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 };
Dim q = From c In db.Customers _
    Group Join o In db.Orders On c.CustomerID Equals o.CustomerID _
        Into ords = Group _
    Let z = c.City + c.Country _
        From o In ords _
        Select c.ContactName, o.OrderID, z

Example

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 };
Dim q = From o In db.Orders _
    From p In db.Products _
    Group Join d In db.OrderDetails On New With {o.OrderID, _
        p.ProductID} _
        Equals New With {d.OrderID, d.ProductID} Into details _
            = Group _
        From d In details _
    Select o.OrderID, p.ProductID, d.UnitPrice

Example

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 };
Dim q = From o In db.Orders _
    Group Join e In db.Employees On o.EmployeeID _
        Equals e.EmployeeID Into emps = Group _
        From e In emps _
    Select o.OrderID, e.FirstName

See Also

Query Examples