LINQ Grammar Encyclopedia (Transpose)

Join operator

Application scenario: In our table relationship there is a relationship, a one-to-many relationship, many-to-many relationships, and so on. For the relationships between the tables, you can use these to implement operations on multiple tables.

Description: In the join operation, join (join query), SelectMany (select-to-Multiple selection), and GroupJoin (group Join query), respectively. The extension method inner JOIN operation on elements that match the keys in two sequences

SelectMany

Description: When we write a query statement, we need to meet 2 conditions if translated into SelectMany. 1: There is no join and into,2 in the query statement: EntitySet must appear. In our table relationship there is a relationship, a one-to-many relationship, many-to-many relationships, and so on, respectively.

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

var q = from in      db. Customers    from in  c.orders    where"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 =     from inch db. Products    where= =&&=0    Select P

Statement Description: This sentence uses p. Supplier.country conditions, indirectly associated with 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.

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

var q = from in      db. Employees    from in  e.employeeterritories    where" Seattle "    Select New     {        e.firstname,        e.lastname,        et. Territory.territorydescription    };

3. Self-Join relationship:

var q = from in      db. Employees    from in  E1. Employees    where E1. City = = E2. City    Selectnew  {        = e1. FirstName, LastName1 = E1. LastName,        = E2. FirstName, LastName2 = E2. LastName,        E1. City    };

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):

var q = from in      db. Customers    in  db. Orders on C.customerid    equals o.customerid into orders    Selectnew    {        C.contactname,         = orders. Count ()    };

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):

var q = from in      db. Customers    in  db. Orders on C.customerid    equals O.customerid to Ords    in  db. Employees on c.city    equals e.city into Emps    Selectnew    {        C. ContactName,        = ords. Count (),        = 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  Span style= "color: #000000;" > {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 in      db. Customers    in  db. Orders on C.customerid    equals O.customerid to ords    = c.city + c.country    from inch Ords     Select New     {        c.contactname,        o.orderid,        z    };

5. Key combination (Composite key):

This example shows a join with a key combination:

varQ = fromOinchdb. Orders fromPinchdb. Products Join Dinchdb. OrderDetails onNew{o.orderid, p.productid} equalsNew{d.orderid, d.productid} into details fromDinchDetailsSelect 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 in      db. Orders    in  db. Employees on        O.employeeid equals        (int?) E.employeeid to Emps    from in  emps    SelectNew      {        o.orderid,        e.firstname    };

LINQ Grammar Encyclopedia (Transpose)