C # 3.0 Getting Started series (10)-Join operation

This section talks about join operations. We know that in T-SQL there are three basic Join,inner joins, left joins, and right joins. And DLinq does not support right join. The truth is simple, right join is based on the right table, and no corresponding record in the left table will be filled with a null value. Instead, DLinq creates objects in the left table as the primary table. If an object is null, how do you get its other attributes?

In the introduction to the C # 3.0 introductory series (iv)-select operation, we mentioned that query expression is first translated into a standard API, while DLINQ provides us with three APIs in a join operation. They are joins, SelectMany and GroupJoin

Join

In the 101 sample, there is no example of join. When a query expression a join phrase without an into Word, it is translated into a join method. For example, take customers as the main table, orders for the child table, with CustomerID To do the join operation.

var q =
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID

select new { c.CustomerID, o.EmployeeID };

It will be translated into

var q = db.Customers.Join(db.Orders, c => c.CustomerID, o => o.CustomerID, (c, o) => new { c.CustomerID, o.EmployeeID });

The first parameter of the Join method, which is the child table, the second parameter, represents the selection key in the primary table, the third parameter is the corresponding key in the child table, and the fourth is the final filter result. We need to pay attention to, because the order of the parameters is OK, so when writing dlinq statements, C.customerid equals O.customerid order is immutable.

The T-SQL statement produced by the statement is

SELECT [t0].[CustomerID], [t1].[EmployeeID]
FROM [Customers] AS [t0]
INNER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]

SelectMany

In 101sample, 4 examples of SelectMany were given. will be translated into SelectMany needs to meet 2 conditions. There is no join and into,2 in the 1,query statement, and EntitySet must appear. For EntitySet, refer to the C#3.0 Advanced Series (i)-From the map

Let's look at the first example.

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

Customers and orders are the 1:m of the relationship. Orders in the Customers class, to EntitySet appear. So the second from IS from C. Orders instead of Db.orders. Mapping code, which defines their relationship, preserves their relationship with attributes. Such as

[Association(Name="Order_OrderDetail", Storage="_OrderDetails", OtherKey="OrderID")]
[Association(Name="Order_OrderDetail", Storage="_Order", ThisKey="OrderID", IsForeignKey=true)]

So, you don't have to worry about whether DLinq knows you should press that key to associate. Interested friends, you can modify the OtherKey and ThisKey values here, to see if the translated T-SQL statements have changed.

A second example

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

In this case, the p is used directly. Supplier.country make the condition, thus, also indirectly related to the Supplier table. The T-SQL statement generated by this statement is more worth guessing, which is probably the simplest dlinq statement for a left-out Join.

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 String (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0