|
BLToolkitLinq-provider(六)
|
,
[t1].[PostalCode],
[t1].[Country],
[t1].[Phone],
[t1].[Fax]
FROM
[Orders] [o]
INNER JOIN [Customers] [t1] ON [o].[CustomerID] = [t1].[CustomerID] |
Associations are a very powerful tool, makes it easy to write the most complex queries. Let's try to write a query with multi-level associations:
from o in db.OrderDetail
select new
{
o.Product.ProductName,
o.Order.OrderID,
o.Order.Employee.ReportsToEmployee.Region
} |
SQL:
SELECT
[T1]. [ProductName]
[o]. [OrderID]
[t2].[Region]
FROM
[Order Details] [o]
INNER JOIN [Products] [t1] ON [o].[ProductID] = [t1].[ProductID]
INNER JOIN [Orders] [t4]
LEFT JOIN [Employees] [t3]
LEFT JOIN [Employees] [t2] ON [t3].[ReportsTo] = [t2].[EmployeeID]
ON [t4].[EmployeeID] = [t3].[EmployeeID]
ON [o].[OrderID] = [t4].[OrderID] |
Note that we have used for the different tables of different types of Join. This is the result of application parameter CanBeNull.
Another interesting point is the use of the field to Select OrderID in the table OrderDetails, although in the Linq query uses a field in the table Orders. It's just that most optimization, which performs BLToolkit to retract unnecessary links. Let's change our example:
from o in db.OrderDetail
select new
{
o.Product.ProductName,
o.Order.OrderID,
//o.Order.Employee.ReportsToEmployee.Region
}; |
SQL:
SELECT
[T1]. [ProductName]
[o]. [OrderID]
FROM
[Order Details] [o]
INNER JOIN [Products] [t1] ON [o].[ProductID] = [t1].[ProductID] |
Since the table Order to access the Employee we no longer need and use OrderID from OrderDetails does not change the logic of the request, the connection with the Order, too, disappeared.
In addition, the same type of associative objects of different entities can be compared:
from o in db.Order
from t in db.EmployeeTerritory
where o.Employee == t.Employee
select new
{
o.OrderID,
o.EmployeeID,
t.TerritoryID
} |
SQL:
SELECT
[o]. [OrderID]
[o]. [EmployeeID]
[t1].[TerritoryID]
FROM
[Orders] [o], [EmployeeTerritories] [t1]
WHERE
[o].[EmployeeID] = [t1].[EmployeeID] |
And use for grouping:
from p in db.Product
group p by p.Category into g
where g.Count() == 12
select g.Key.CategoryName; |
SQL:
SELECT
[t1].[CategoryName]
FROM
[Products] [p]
INNER JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]
GROUP BY
[t1].[CategoryID],
[t1].[CategoryName]
HAVING
Count(*) = 12 |
For comparison and grouping of objects BLToolkit uses primary key information object. If the primary key is not specified, BLToolkit generates code that compares all of the object with each other. This is, firstly, not effective, and, secondly, is not always possible, because not all types in SQL can be compared.
As an experiment, let's try to remove from the Employee entity primary key and run the previous query again. The resulting SQL:
SELECT
[o]. [OrderID]
[o]. [EmployeeID]
[t3].[TerritoryID]
FROM
[Orders] [o]
LEFT JOIN [Employees] [t1] ON [o].[EmployeeID] = [t1].[EmployeeID],
[EmployeeTerritories] [t3]
LEFT JOIN [Employees] [t2] ON [t3].[EmployeeID] = [t2].[EmployeeID]
WHERE
[o].[EmployeeID] = [t3].[EmployeeID] AND
[t1].[LastName] = [t2].[LastName] AND
[t1].[FirstName] = [t2].[FirstName] AND
[t1].[Title] = [t2].[Title] AND
[t1].[TitleOfCourtesy] = [t2].[TitleOfCourtes |
|