Associations

Modified on 2011/06/13 11:57 by IT — Categorized as: Linq

Associations between entity classes are analogous to relationships between tables in a database. You can define associations between entity classes by using the Association attribute as shown below:

[TableName("Products")]
public class Product
{
    [PrimaryKey, Identity] public int      ProductID;
    [NotNull]              public string   ProductName;
                           public int?     SupplierID;
                           public int?     CategoryID;
                           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<OrderDetail> OrderDetails;

    [Association(ThisKey="CategoryID", OtherKey="CategoryID", CanBeNull=false)]
    public Category Category;

    [Association(ThisKey="SupplierID", OtherKey="SupplierID", CanBeNull=false)]
    public Supplier Supplier;
}

A class member decorated with the Association attribute can be of either enumerable type or reference type. The enumerable type defines one-to-many relationship. Reference type defines one-to-one relationship or many-to-one, but it does not important as BLToolkit generates the same SQL in both cases.

The relationship itself is defined by the ThisKey/OtherKey properties of the attribute. The ThisKey property represents key fields (comma delimited) on the side where the association is defined. The OtherKey property represents key fields on the other side of the association. If names of the entity fields and database fields are different, the entity field names have to be used.

The CanBeNull property specifies the relation type: Inner Join or Left Join. If CanBeNull equals true (default value), Left Join is generated.

The following example demonstrates the use of the association:

from p in db.Product
select new
{
    p.Category.CategoryName,
    p.ProductName
};

Explicit join has gone, however generated SQL remains the same as here:

SELECT
    [t1].[CategoryName],
    [p].[ProductName]
FROM
    [Products] [p]
        INNER JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]

Let's change the CanBeNull property value of the Category association to true and check out the result SQL:

SELECT
    [t1].[CategoryName],
    [p].[ProductName]
FROM
    [Products] [p]
        LEFT JOIN [Categories] [t1] ON [p].[CategoryID] = [t1].[CategoryID]

Now we get Left Join.

Enumerable associations can be used in the following way:

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]

Associated entities are not populated automatically, however it can be done 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],
    [t1].[PostalCode],
    [t1].[Country],
    [t1].[Phone],
    [t1].[Fax]
FROM
    [Orders] [o]
        INNER JOIN [Customers] [t1] ON [o].[CustomerID] = [t1].[CustomerID]

The associations prove to be very powerful tools that allow for easy manipulation of very complicated queries. The following demonstrates the use of multilevel 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, we have different join types for different tables. This is the result of applying CanBeNull property.

Grouping by association:

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

Associative object comparison:

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]

To compare and group by associations BLToolkit uses primary key information of the entities. If a primary key is not define, BLToolkit generates a code that comparing all the object fields with each other. First of all it is not efficient. Secondly, it's not always possible as some SQL types are not comparable.

Let's remove primary key definition from the Employee entity and run the previous query again. Generated 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].[TitleOfCourtesy] AND
    ([t1].[BirthDate] IS NULL AND [t2].[BirthDate] IS NULL OR [t1].[BirthDate] IS NOT NULL AND [t2].[BirthDate] IS NOT NULL AND [t1].[BirthDate] = [t2].[BirthDate]) AND
    ([t1].[HireDate] IS NULL AND [t2].[HireDate] IS NULL OR [t1].[HireDate] IS NOT NULL AND [t2].[HireDate] IS NOT NULL AND [t1].[HireDate] = [t2].[HireDate]) AND
    [t1].[Address] = [t2].[Address] AND
    [t1].[City] = [t2].[City] AND
    [t1].[Region] = [t2].[Region] AND
    [t1].[PostalCode] = [t2].[PostalCode] AND
    [t1].[Country] = [t2].[Country] AND
    [t1].[HomePhone] = [t2].[HomePhone] AND
    [t1].[Extension] = [t2].[Extension] AND
    [t1].[Notes] = [t2].[Notes] AND
    ([t1].[ReportsTo] IS NULL AND [t2].[ReportsTo] IS NULL OR [t1].[ReportsTo] IS NOT NULL AND [t2].[ReportsTo] IS NOT NULL AND [t1].[ReportsTo] = [t2].[ReportsTo]) AND
    [t1].[PhotoPath] = [t2].[PhotoPath]

Such queries make me cry.

So, please be accurate defining your data model. BLToolkit uses your entity's metadata to optimize the queries and missing data may lead to inefficient SQL.