Cómo: Formular combinaciones y consultas entre productosFormulate Joins and Cross-Product Queries

En los ejemplos siguientes se muestra cómo combinar los resultados procedentes de varias tablas.The following examples show how to combine results from multiple tables.

EjemploExample

En el ejemplo siguiente se utiliza la navegación de From clave externa en lafrom cláusula de C#Visual Basic (cláusula de) para seleccionar todos los pedidos de los clientes de Londres.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

EjemploExample

En el ejemplo siguiente se utiliza la navegación de Where clave externa en lawhere cláusula de C#Visual Basic (cláusula en) para filtrar los Products valores Supplier de fuera de existencias, cuyo valor está en el Estados Unidos.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

EjemploExample

En el ejemplo siguiente se utiliza la navegación de From clave externa en lafrom cláusula de C#Visual Basic (cláusula de) para filtrar los empleados de Seattle y mostrar sus territorios.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
    };

EjemploExample

En el ejemplo siguiente se utiliza la navegación de Select clave externa en laselect cláusula de C#Visual Basic (cláusula de) para filtrar los pares de empleados en los que un empleado informa al otro y donde Cityambos empleados son de la misma.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

EjemploExample

En el siguiente Visual Basic ejemplo se buscan todos los clientes y pedidos, se asegura de que los pedidos coinciden con los clientes y se garantiza que, para cada cliente de esa lista, se proporciona un nombre de contacto.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

EjemploExample

En el ejemplo siguiente se combinan explícitamente dos tablas y se proyectan los resultados de ambas.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()

EjemploExample

En el ejemplo siguiente se combinan explícitamente tres tablas y se proyectan los resultados de cada una de ellas.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()

EjemploExample

En el ejemplo siguiente se muestra cómo lograr una LEFT OUTER JOIN mediante DefaultIfEmpty().The following example shows how to achieve a LEFT OUTER JOIN by using DefaultIfEmpty(). El método DefaultIfEmpty() devuelve null cuando no hay ningún Order para Employee.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

EjemploExample

En el ejemplo siguiente se proyecta una expresión let que es el resultado de una combinación.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

EjemploExample

En el ejemplo siguiente se muestra una join con una clave compuesta.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

EjemploExample

En el ejemplo siguiente se muestra cómo construir una join de tal forma que una parte acepte valores NULL y la otra no.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

Vea tambiénSee also