Join of LINQ (2)

Join of LINQ

Category: LINQ 2010-06-30 333 people read comments (0) collect reports

Applicable scenarios: We have one-to-one relationships, one-to-multiple relationships, and many-to-many relationships in our table relationships. For the relationship between tables, you can use these to operate on multiple tables.

Description: In the join operation, they are join (join query), selectmany (select one-to-multiple choice), and groupjoin (Group join query ).
This extension method performs the inner Join Operation on the elements matching the middle keys in the two sequences.

Selectworkflow

Description: When writing a query statement, if it is translated into selectmany, two conditions must be met. 1: The query statement does not contain join and into. 2: entityset must appear. In our table, there are one-to-one, one-to-many, and many-to-many relationships. The following describes them respectively.

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

var q =    from c in db.Customers    from o in c.Orders    where c.City == "London"    select o;

Statement Description: MERs has a one-to-multiple relationship with orders. That is, orders appears in entityset form in the MERs class. Therefore, the second from is filtered from C. Orders instead of DB. Orders. In this example, use the foreign key navigation in the from clause to select all orders of London customers.

var q =    from p in db.Products    where p.Supplier.Country == "USA" && p.UnitsInStock == 0    select p;

Statement Description: This statement uses the p. Supplier. Country condition and indirectly associates with the supplier table. In this example, the where clause uses a foreign key navigation bar to filter products whose suppliers are out of stock in the United States. The 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 ):

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

Note: many-to-many relationships generally involve three tables (if one table is self-associated, there may be only two tables ). This statement involves three tables: employees, employeeterritories, and territories. Their relationship is 1: M: 1. There is no clear relationship between employees and territories.

Statement Description: This example uses foreign key navigation in the from clause to filter employees in Seattle and list their regions. The 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. Each employee belongs to another employee and both of them come from the same city. The 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 mentioned above, if there is no join or into, it is translated into selectjoins. If there is a join or into, it is translated as groupjoin. Here, the concept of into is to rename the result.

1. Two Way join ):

In this example, two tables are explicitly joined and projected from the two 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 C in dB. customers), and the right side is orders. Each record is called O (join o in dB. orders), each corresponding to a C on the left, there will be a group of O, then this group of O is called orders, that is, we name a group of O orders, this is the use of. This is why orders can call the aggregate function count in the SELECT statement. In the T-SQL, use its nested T-SQL return value as the field value. :

The 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-way join ):

In this example, three tables are explicitly joined and the results are projected from each table:

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

This example shows how to use this example to obtain the left Outer Join by using defaultifempty. If 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    };

Note: Fill in the left table of employees, right table of orders, and empty table of orders with null values. Rename the join result ords and use the defaultifempty () function to query it again. There is an order in the final result, because from o in ords. defaultifempty () is another traversal of the ords group, so the order in the final result is not a set. However, if there is no from o in ords. defaultifempty (), and the final select statement is written as select new {e. firstname, E. if lastname, order = ords} is used, order is a set.

4. Projected let assignment ):

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

In this example, the final "let" expression is projected from the 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. composite key ):

This example shows the 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    };

Note: use three tables and use anonymous classes to describe: use three tables and use anonymous classes to represent the relationships between them. The relationship between them cannot be clearly described using a key. Therefore, an anonymous class is used to represent the key combination. Another way is to use a combination of keys to represent the relationship between two tables without using an anonymous class.

6. key relationships that can be null or cannot be null (nullable/nonnullable key relationship ):

This instance shows how to construct a join where one side can be null and the other side 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    };