Formulare query su prodotto incrociato e joinFormulate Joins and Cross-Product Queries

Negli esempi riportati di seguito viene illustrato come combinare risultati da più tabelle.The following examples show how to combine results from multiple tables.

EsempioExample

L'esempio seguente usa navigazione con chiave esterna nel From clausola in Visual Basic (from clausola in c#) per selezionare tutti gli ordini per i clienti nell'area londinese.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

EsempioExample

L'esempio seguente usa navigazione con chiave esterna nel Where clausola in Visual Basic (where clausola in c#) per filtrare per out-of-stock Products cui Supplier è negli Stati Uniti.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

EsempioExample

L'esempio seguente usa navigazione con chiave esterna nel From clausola in Visual Basic (from clausola in c#) per filtrare i dipendenti di Seattle ed elencare i territori.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
    };

EsempioExample

L'esempio seguente usa navigazione con chiave esterna nel Select clausola in Visual Basic (select clausola in c#) per filtrare le coppie di dipendenti in cui un dipendente riporta a altro ed entrambi sono della stessa City.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

EsempioExample

Nell'esempio di Visual Basic seguente esegue la ricerca di tutti i clienti e ordini, assicura che gli ordini siano associati ai clienti e garantisce che per ogni cliente nell'elenco, viene fornito un nome di contatto.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

EsempioExample

Nell'esempio seguente vengono unite in join in modo esplicito due tabelle e vengono proiettati i risultati da entrambe le tabelle.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()

EsempioExample

Nell'esempio seguente vengono unite in join in modo esplicito tre tabelle e vengono proiettati i risultati da ogni tabella.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()

EsempioExample

Nell'esempio seguente viene illustrato come ottenere LEFT OUTER JOIN usando DefaultIfEmpty().The following example shows how to achieve a LEFT OUTER JOIN by using DefaultIfEmpty(). Il metodo DefaultIfEmpty() restituisce null se non è presente alcun Order per 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

EsempioExample

Nell'esempio seguente viene proiettata l'espressione let risultante da un join.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

EsempioExample

Nell'esempio seguente viene illustrato un join con una chiave composita.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

EsempioExample

Nell'esempio seguente viene descritto come costruire un join in cui un solo lato è nullable.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

Vedere ancheSee Also

Esempi di queryQuery Examples