Join of LINQ to SQL statements (4)

1. One-to-many relationship (1 to many):

var q =

From C in DB. Customers

From O in C.orders

where c.city = = "London"

Select O;

Statement Description: Customers is a one-to-many relationship with orders. That is, orders appears in EntitySet form in the Customers class. So the second from IS from C. Orders, rather than Db.orders, are screened. This example uses foreign key navigation in the FROM clause to select all orders for London customers.

var q =

from P in Db. Products

where p.supplier.country = = "USA" && P.unitsinstock = = 0

Select P;

Statement Description: This sentence uses the p.supplier.country condition and indirectly associates the Supplier table. This example uses foreign key navigation in the WHERE clause to filter the products whose suppliers are in the United States and are out of stock. The generated SQL statement is:

SELECT [T0]. [ProductID], [t0]. [ProductName], [t0]. [SupplierID],

[T0]. [Categoryid],[t0]. [Quantityperunit],[t0]. [UnitPrice],

[T0]. [UnitsInStock], [t0]. [Unitsonorder],[t0]. [ReorderLevel],

[T0]. [Discontinued] from [dbo]. [Products] As [t0]

Left OUTER JOIN [dbo]. [Suppliers] As [T1] on

[T1]. [SupplierID] = [T0]. [SupplierID]

WHERE ([t1].[ Country] = @p0) and ([t0].[ UnitsInStock] = @p1)

--@p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]

--@p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

2. Many-to-many relationships (many to many):

var q =

From E in DB. Employees

From ET in e.employeeterritories

where e.city = = "Seattle"

Select New

{

E.firstname,

E.lastname,

Et. Territory.territorydescription

};

Description: A Many-to-many relationship typically involves three tables (if one table is self-correlating, it is possible that there are only 2 tables). This sentence involves employees, employeeterritories, territories three tables. Their relationship is 1:m:1. Employees and territories do not have a very clear relationship.

Statement Description: This example uses foreign key navigation in the FROM clause to filter the employees in the Seattle, and also lists the regions in which they are located. This generated SQL statement is:

SELECT [T0]. [FirstName], [t0]. [LastName], [T2]. [Territorydescription]

from [dbo]. [Employees] As [t0] cross JOIN [dbo]. [Employeeterritories]

As [T1] INNER JOIN [dbo]. [Territories] As [T2] on

[T2]. [TerritoryID] = [T1]. [TerritoryID]

WHERE ([t0].[ City] = @p0) and ([t1].[ EmployeeID] = [T0]. [EmployeeID])

--@p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Seattle]

3. Self-Join relationship:

var q =

From E1 in Db. Employees

From E2 in E1. Employees

where E1. City = = E2. City

Select New {

FirstName1 = E1. FirstName, LastName1 = E1. LastName,

FirstName2 = E2. FirstName, LastName2 = E2. LastName,

E1. City

};

Statement Description: This example uses foreign key navigation in the SELECT clause to filter paired employees, one employee per pair is part of another employee, and two employees are from the same city. The generated SQL statement is:

SELECT [T0]. [FirstName] As [FirstName1], [t0]. [LastName] As

[Lastname1],[t1]. [FirstName] As [FirstName2], [T1]. [LastName] As

[Lastname2],[t0]. [City] from [dbo]. [Employees] As [T0],

[dbo]. [Employees] As [T1] WHERE ([t0].[ City] = [T1]. [City]) and

([T1]. [ReportsTo] = [T0]. [EmployeeID])

GroupJoin

As stated above, without joins and into, translated into SelectMany, with joins and into, then translated into GroupJoin. The idea here is to rename the results.

1. Bidirectional join (Two way join):

This example explicitly joins two tables and projects the 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 ()

};

Note: In a one-to-many relationship, the left side is 1, and each record is C (from the C in db). Customers), to the right is many, each of which is called O (Join O in db). Orders), each corresponding to the left of a C, there will be a set of O, that this group O, called Orders, that is, we named a group o orders, which is the into use. This is why in the SELECT statement, orders can call the aggregate function count. In T-SQL, the field value is used as a value of the embedded T-SQL return. ：

The generated SQL statement is:

SELECT [T0]. [ContactName], (

SELECT COUNT (*)

from [dbo]. [Orders] As [T1]

WHERE [T0]. [CustomerID] = [T1]. [CustomerID]

) as [OrderCount]

from [dbo]. [Customers] As [t0]

2. Three-to-join (there):

This example explicitly joins three tables and projects the results from each table individually:

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 generated SQL statement is:

SELECT [T0]. [ContactName], (

SELECT COUNT (*)

from [dbo]. [Orders] As [T1]

WHERE [T0]. [CustomerID] = [T1]. [CustomerID]

) as [Ords], (

SELECT COUNT (*)

from [dbo]. [Employees] As [T2]

WHERE [T0]. [City] = [T2]. [City]

) as [Emps]

from [dbo]. [Customers] As [t0]

3. Left OUTER join (Outer join):

This sample demonstrates how to get a left outer join by using DefaultIfEmpty () by using this example. When an employee does not have an order, the DefaultIfEmpty () method returns null:

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

};

Description: A null value is populated with the employees left table, the orders right table, and the Orders table as empty. The result of the join IS renamed Ords, which is queried again using the DefaultIfEmpty () function. There is an order in the final result because the from O in Ords. DefaultIfEmpty () is another traversal of the Ords group, so the order in the final result is not a collection. However, if there is no from O in Ords. DefaultIfEmpty (), the last SELECT statement is written as select new {e.firstname, e.lastname, order = ords}, then order is a collection.

4. Let assignment of the projection (projected let assignment):

Description: The Let statement is renamed. Let is located between the first from and select statements.

This example projects a final "let" expression 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

};

5. Key combination (Composite key):

This example shows a join with a key combination:

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

};

Description: Use three tables, and use anonymous classes to illustrate: Use three tables, and use anonymous classes to represent the relationships between them. The relationship between them cannot be described clearly with a key, so an anonymous class is used to represent the key combination. Another is that two tables are represented by a combination of keys, and no anonymous classes are required.

6. Can be null/a non-nullable key relationship (nullable/nonnullable key Relationship):

This example shows how to construct a join on one side that can be null and the other cannot be null:

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

};