|
BLToolkitLinq-provider(五)
|
ategoryID;
public string QuantityPerUnit;
public decimal ? UnitPrice,
public short ? UnitsInStock;
public short ? UnitsOnOrder;
public short ? ReorderLevel;
public bool Discontinued;
[Association(ThisKey="ProductID", OtherKey="ProductID")]
public List
OrderDetails;
[Association(ThisKey="CategoryID", OtherKey="CategoryID", CanBeNull=false)]
public Category Category;
[Association(ThisKey="SupplierID", OtherKey="SupplierID", CanBeNull=false)]
public Supplier Supplier;
}
|
What you need to know about associations? Member of the class to which the attribute is applied Association may have a list type, or be a reference to the object. Payroll type is one to many relationship. Ie in this case one
Product and many
Order Details. Just a reference to an object is one-to-one. Either a lot to one that for us it does not matter, because on SQL generation is not affected.Feedback is given by a pair of very attribute properties
ThisKey /
OtherKey. Property
ThisKey comma separated fields of the entity in which the association is declared. Property
OtherKey - relevant fields related entity. If the field names and the nature of the original database tables differ in these properties indicate the names of the fields being.Property
CanBeNull points BLToolkit what connection generate: Inner Join or Left Join. Be careful with this property. Left Join is not the case can very significantly improve query execution time.Besides BLToolkit can perform additional optimizations for one-to-one, and remove extra links from the request if it Inner Join. In general, the use of this attribute rule is: For one-to-many relationships
CanBeNull may be equal to false only if the master recording may not appear in the database with no child bath (e.g.,
Order ->
OrderDetails); for one-to-one relationships often
CanBeNull to be false, but it is necessary to specify explicitly.The members of the class to which the association applied
not filled in automatically when reading an object from the database entirely.
Now let's see what we've got:
from p in db.Product
select new
{
p.Category.CategoryName,
p.ProductName
} |
An explicit connection with Table Category disappeared, but generated SQL remains the same:
SELECT
[t1].[CategoryName],
[P]. [ProductName]
FROM
[Products] [p]
INNER JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID] |
Let's try to change the association Category property value CanBeNull to true, and look at the result:
SELECT
[t1].[CategoryName],
[P]. [ProductName]
FROM
[Products] [p]
LEFT JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID] |
You guessed it, now we turned Left Join.
Use the list-association may be, for example, as follows:
from p in db.Product
select new
{
p.OrderDetails.Count,
p.ProductName
} |
SQL:
SELECT
(
SELECT
Count(*)
FROM
[Order Details] [t1]
WHERE
[p].[ProductID] = [t1].[ProductID]
) as [c1],
[P]. [ProductName]
FROM
[Products] [p] |
As mentioned above related entities are not filled automatically when you create an object, but you can do it manually:
from o in db.Order
select new Northwind.Order
{
OrderID = o.OrderID,
Customer = o.Customer
}; |
SQL:
SELECT
[o]. [OrderID]
[t1].[CustomerID],
[T1]. [CompanyName]
[t1].[ContactName],
[t1].[ContactTitle],
[t1].[Address],
[t1].[City],
[t1].[Region] |
|