[Data Access] ORM 原理 (7) 物件關聯性

2011-11-24

7680
0
ADO.NET

資料關聯 (data relation) 是 DBMS 的特色之一，它通常也是和物件難以整合的重要因素，因為物件的關聯和資料的關聯是不同的，物件的關聯是在物件內以屬性的方式連接另一個物件，但資料的關聯是在兩個表格之間以鍵值資料 (key) 串接，且 SQL 指令會透過 JOIN 指令 (不論是 INNER, OUTER 或 FULL) 來撈取關聯的資料，只是如果要在 ORM 內實作這樣的機制，勢必會有不小的難度，因為 JOIN 指令要由 ORM Framework 來產生，而且在取得關聯資料時，ORM Framework 未必會直接撈取關聯資料，而是在存取行為發生時才會實際填入資料 (又稱為延遲載入)，後者要判斷的事就更多了。

在 ORM 原理 (6) 時，我們已經實作了一個 DataContext 物件，內含有 GetEntities() 以及 GetEntity() 方法，其中我們只用到了 GetEntities()，不過在 (7) 時，我們會用到 GetEntity() 方法，它會在物件關聯時載入與物件關聯的另一個物件，所以開發人員可以用物件的方式存取資料庫，包含關聯。在 (7) 我們所用的範例是 Orders 表格，在程式中是 Order 類別和其集合 OrderCollection 類別，而 Orders 表格會和 Customers 表格與 Employees 表格有關聯，因此我們也在 Order 類別中加入對應 (其中的 DataSourceKeyAttribute 是設定哪一個屬性是 Key 資料的欄位)：


public class Order
{
    [DataSourceKey]
    public string OrderID { get; private set; }
    public DateTime OrderDate { get; set; }
    public DateTime RequiredDate { get; set; }
    public DateTime ShippedDate { get; set; }
    public double Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }

    // relational object.
    public Customer Customer { get; set; }
    public Employee Employee { get; set; }
}

依照前面在原理 (5) 的作法，我們可以很順利的將屬性和欄位繫結，可是 Customer 和 Employee 要怎麼做？首先會想到的就是 SQL 的 INNER JOIN 方式，當 Order.CustomerID = Customer.CustomerID 成立時，即載入資料，否則設為 null 值，Employee 也是相同的處理法，但我們在原本的組態設定中並沒有設定這樣的關聯，所以我們要在組態檔中加：


public class EntityRelationMap : ConfigurationElement
{
    [ConfigurationProperty("propertyName", IsKey = true, IsRequired = true)]
    public string PropertyName { get { return base["propertyName"].ToString(); } }
    [ConfigurationProperty("mapSchemaName", IsRequired = true)]
    public string MapSchemaName { get { return base["mapSchemaName"].ToString(); } }
    [ConfigurationProperty("mapType", IsRequired = true)]
    public string MapType { get { return base["mapType"].ToString(); } }
    [ConfigurationProperty("sourceSchemaPropertyName", IsRequired = true)]
    public string SourceSchemaPropertyName { get { return base["sourceSchemaPropertyName"].ToString(); } }
    [ConfigurationProperty("targetSchemaPropertyName", IsRequired = true)]
    public string TargetSchemaPropertyName { get { return base["targetSchemaPropertyName"].ToString(); } }
    [ConfigurationProperty("mapExpression", IsRequired = true)]
    public string MapExpression { get { return base["mapExpression"].ToString(); } }
}

它會產生這樣的組態 (relations 以及其下的 relation 元素)：


<entity type="ConsoleApplication2.Order" schema="Orders">
  <maps>
    <map propertyName="OrderID" schemaName="OrderID" />
    <map propertyName="OrderDate" schemaName="OrderDate" />
    <map propertyName="RequiredDate" schemaName="RequiredDate" />
    <map propertyName="ShippedDate" schemaName="ShippedDate" />
    <map propertyName="Freight" schemaName="Freight" />
    <map propertyName="ShipName" schemaName="ShipName" />
    <map propertyName="ShipAddress" schemaName="ShipAddress" />
    <map propertyName="ShipCity" schemaName="ShipCity" />
    <map propertyName="ShipRegion" schemaName="ShipRegion" />
    <map propertyName="ShipPostalCode" schemaName="ShipPostalCode" />
    <map propertyName="ShipCountry" schemaName="ShipCountry" />
  </maps>
  <relations>
    <relation propertyName="Customer"
              mapSchemaName="Customers" mapType="INNER JOIN"
              sourceSchemaPropertyName="CustomerID" targetSchemaPropertyName="CustomerID" mapExpression="="/>
    <relation propertyName="Employee"
              mapSchemaName="Employees" mapType="INNER JOIN"
              sourceSchemaPropertyName="EmployeeID" targetSchemaPropertyName="EmployeeID" mapExpression="="/>
  </relations>
</entity>

有了關聯性後，我們就可以處理 SQL 指令的生成，所以我們改寫了 DataContext 類別，加入專屬的 PrepareStatement<T>() 方法：


private string PrepareStatement<T>(ExpressionTree QueryConditionTree)
{
    StringBuilder sqlbuilder = new StringBuilder();
    StringBuilder columnBuilder = new StringBuilder();
    StringBuilder relationBuilder = new StringBuilder();
    Configuration.EntityConfiguration entityConfiguration =
        this._entityConfiguration.EntityConfigurations.GetConfigurationFromType(typeof(T).FullName);

    // load columns.
    sqlbuilder.Append("SELECT ");

    int pos = 0;

    foreach (ConfigurationElement entityMapItem in entityConfiguration.EntitySchemaMaps)
    {
        if (pos == 0)
            columnBuilder.Append(entityConfiguration.SchemaName + "." + (entityMapItem as Configuration.EntitySchemaMap).EntitySchemaName);
        else
        {
            columnBuilder.Append(", ");
            columnBuilder.Append(entityConfiguration.SchemaName + "." + (entityMapItem as Configuration.EntitySchemaMap).EntitySchemaName);
        }

        pos++;
    }

    // load relation information for build join expression.
    if (entityConfiguration.EntityRelationMaps.Count > 0)
    {
        foreach (ConfigurationElement entityRelationItem in entityConfiguration.EntityRelationMaps)
        {
            Configuration.EntityRelationMap entityRelationMap = entityRelationItem as Configuration.EntityRelationMap;

            columnBuilder.Append(", ");
            columnBuilder.Append(entityRelationMap.MapSchemaName + "." + entityRelationMap.TargetSchemaPropertyName);

            relationBuilder.Append(" ");
            relationBuilder.Append(entityRelationMap.MapType + " " + entityRelationMap.MapSchemaName);
            relationBuilder.Append(" ON ");
            relationBuilder.Append(entityConfiguration.SchemaName + "." + entityRelationMap.SourceSchemaPropertyName);
            relationBuilder.Append(" " + entityRelationMap.MapExpression + " ");
            relationBuilder.Append(entityRelationMap.MapSchemaName + "." + entityRelationMap.TargetSchemaPropertyName);
        }
    }

    sqlbuilder.Append(columnBuilder.ToString());
    sqlbuilder.Append(" FROM " + entityConfiguration.SchemaName);
    sqlbuilder.Append(relationBuilder.ToString());

    // prepare WHERE clause.
    if (QueryConditionTree != null)
        sqlbuilder.Append(" WHERE " +  QueryConditionTree.BuildExpressionEval(ExpressionTraversalType.LMR));

    return sqlbuilder.ToString();
}

其中 ExpressionTree 是一個二元樹資料結構 (Binary Tree)，二元樹是寫程式時常用的資料結構之一，在這裡用的是運算式樹，考過資訊類研究所的人應該很熟悉它吧，考試不是很常考什麼前序 (preorder)，中序 (inorder) 和後序 (postorder) 的，在這裡我們使用的是中序走訪 (inorder traversal)，中序走訪可以輸出合乎格式的 SQL 運算式，本例中 WHERE 的運算式會透過 ExpressionTree 來產生：


public class ExpressionTree
{
    private ExpressionNode _rootNode = null;

    public ExpressionTree()
    {
    }

    public ExpressionTree(ExpressionNode RootNode)
    {
        this._rootNode = RootNode;
        this._rootNode.UpdateNodeLevel(0);
    }

    public Queue<ExpressionNode> Traversal(ExpressionTraversalType TraversalType)
    {
        switch (TraversalType)
        {
            case ExpressionTraversalType.LMR:
                return this.DoLeftMediumRightTraversal();
            case ExpressionTraversalType.MLR:
                return this.DoMediumLeftRightTraversal();
            case ExpressionTraversalType.LRM:
                return this.DoLeftRightMediumTraversal();
        }

        return null;
    }

    // front order.
    private Queue<ExpressionNode> DoLeftMediumRightTraversal()
    {
        if (this._rootNode == null)
            return null;
            
        Queue<ExpressionNode> queue = new Queue<ExpressionNode>();

        this.DoLeftMediumRightTraversal(ref queue, this._rootNode);

        return queue;
    }

    private void DoLeftMediumRightTraversal(ref Queue<ExpressionNode> Queue, ExpressionNode Node)
    {
        // traversal left
        if (Node.ExpressionLeft != null)
            this.DoLeftMediumRightTraversal(ref Queue, Node.ExpressionLeft);

        // add medium node.
        Queue.Enqueue(Node);

        // traversal right.
        if (Node.ExpressionRight != null)
            this.DoLeftMediumRightTraversal(ref Queue, Node.ExpressionRight);
    }

    // medium order.
    private Queue<ExpressionNode> DoMediumLeftRightTraversal()
    {
        if (this._rootNode == null)
            return null;

        Queue<ExpressionNode> queue = new Queue<ExpressionNode>();

        this.DoMediumLeftRightTraversal(ref queue, this._rootNode);

        return queue;
    }

    private void DoMediumLeftRightTraversal(ref Queue<ExpressionNode> Queue, ExpressionNode Node)
    {
        // add medium node.
        Queue.Enqueue(Node);

        // traversal left
        if (Node.ExpressionLeft != null)
            this.DoMediumLeftRightTraversal(ref Queue, Node.ExpressionLeft);

        // traversal right.
        if (Node.ExpressionRight != null)
            this.DoMediumLeftRightTraversal(ref Queue, Node.ExpressionRight);
    }

    // back order.
    private Queue<ExpressionNode> DoLeftRightMediumTraversal()
    {
        if (this._rootNode == null)
            return null;

        Queue<ExpressionNode> queue = new Queue<ExpressionNode>();

        this.DoLeftRightMediumTraversal(ref queue, this._rootNode);

        return queue;
    }

    private void DoLeftRightMediumTraversal(ref Queue<ExpressionNode> Queue, ExpressionNode Node)
    {
        // traversal left
        if (Node.ExpressionLeft != null)
            this.DoLeftRightMediumTraversal(ref Queue, Node.ExpressionLeft);

        // traversal right.
        if (Node.ExpressionRight != null)
            this.DoLeftRightMediumTraversal(ref Queue, Node.ExpressionRight);

        // add medium node.
        Queue.Enqueue(Node);
    }

    public string BuildExpressionEval(ExpressionTraversalType TraversalType)
    {
        switch (TraversalType)
        {
            case ExpressionTraversalType.LMR:
                return this.BuildExpressionEvalLMR(this._rootNode);
            case ExpressionTraversalType.MLR:
                return this.BuildExpressionEvalMLR(this._rootNode);
            case ExpressionTraversalType.LRM:
                return this.BuildExpressionEvalLRM(this._rootNode);
        }

        return null;
    }

    private string BuildExpressionEvalLMR(ExpressionNode Node)
    {
        if (Node.NodeType == ExpressionNodeType.Parameter)
            return (Node as ParameterExpressionNode).Value;
        if (Node.NodeType == ExpressionNodeType.Value)
        {
            DataExpressionNode dataNode = Node as DataExpressionNode;

            if (dataNode.DataType == typeof(string))
                return string.Format("'{0}'", (Node as DataExpressionNode).Value.ToString());
            else
                return (Node as DataExpressionNode).Value.ToString();
        }

        StringBuilder evalBuilder = new StringBuilder();

        evalBuilder.Append("(");
        evalBuilder.Append(this.BuildExpressionEvalLMR(Node.ExpressionLeft));
        evalBuilder.Append(this.EvalOperator(Node.NodeType));
        evalBuilder.Append(this.BuildExpressionEvalLMR(Node.ExpressionRight));
        evalBuilder.Append(")");

        return evalBuilder.ToString();
    }

    private string BuildExpressionEvalMLR(ExpressionNode Node)
    {
        if (Node.NodeType == ExpressionNodeType.Parameter)
            return (Node as ParameterExpressionNode).Value;
        if (Node.NodeType == ExpressionNodeType.Value)
        {
            DataExpressionNode dataNode = Node as DataExpressionNode;

            if (dataNode.DataType == typeof(string))
                return string.Format("'{0}'", (Node as DataExpressionNode).Value.ToString());
            else
                return (Node as DataExpressionNode).Value.ToString();
        }

        StringBuilder evalBuilder = new StringBuilder();

        evalBuilder.Append(this.EvalOperator(Node.NodeType));
        evalBuilder.Append(this.BuildExpressionEvalMLR(Node.ExpressionLeft));
        evalBuilder.Append(this.BuildExpressionEvalMLR(Node.ExpressionRight));

        return evalBuilder.ToString();
    }

    private string BuildExpressionEvalLRM(ExpressionNode Node)
    {
        if (Node.NodeType == ExpressionNodeType.Parameter)
            return (Node as ParameterExpressionNode).Value;
        if (Node.NodeType == ExpressionNodeType.Value)
        {
            DataExpressionNode dataNode = Node as DataExpressionNode;

            if (dataNode.DataType == typeof(string))
                return string.Format("'{0}'", (Node as DataExpressionNode).Value.ToString());
            else
                return (Node as DataExpressionNode).Value.ToString();
        }

        StringBuilder evalBuilder = new StringBuilder();

        evalBuilder.Append(this.BuildExpressionEvalLRM(Node.ExpressionLeft));
        evalBuilder.Append(this.BuildExpressionEvalLRM(Node.ExpressionRight));
        evalBuilder.Append(this.EvalOperator(Node.NodeType));

        return evalBuilder.ToString();
    }

    private string EvalOperator(ExpressionNodeType NodeType)
    {
        switch (NodeType)
        {
            case ExpressionNodeType.Add:
                return (" + ");
            case ExpressionNodeType.Division:
                return (" / ");
            case ExpressionNodeType.LogicalAnd:
                return (" AND ");
            case ExpressionNodeType.LogicalNot:
                return (" NOT ");
            case ExpressionNodeType.LogicalOr:
                return (" OR ");
            case ExpressionNodeType.Multiply:
                return (" * ");
            case ExpressionNodeType.Equal:
                return (" = ");
            case ExpressionNodeType.NotEqual:
                return (" <> ");
            case ExpressionNodeType.LessThan:
                return (" < ");
            case ExpressionNodeType.LessThanEqual:
                return (" <= ");
            case ExpressionNodeType.GreaterThan:
                return (" > ");
            case ExpressionNodeType.GreaterThanEqual:
                return (" >= ");
            case ExpressionNodeType.Subtract:
                return (" - ");
            default:
                return " ";
        }
    }
}

有了運算式樹後，我們就可以來改寫 DataContext.GetEntity<T>() 方法，由於 GetEntity() 會以 Entity 的 Key 值來取得對應的資料，所以會需要傳入帶有 Key 值的 Entity 物件，然後透過 SQL 的 WHERE 產生必要的資料：


public T GetEntity<T>(T contextObject)
    where T : class
{
    T entity;
    ExpressionTree queryConditionTree = null;

    if (contextObject == default(T))
        entity = Activator.CreateInstance(typeof(T)) as T;
    else
    {
        entity = contextObject;
                
        string keyColumn = null;
        object keyColumnValue = null;
        PropertyInfo[] properties = typeof(T).GetProperties();

        foreach (PropertyInfo property in properties)
        {
            DataSourceKeyAttribute[] keyAttributes = property.GetCustomAttributes(typeof(DataSourceKeyAttribute), true) as DataSourceKeyAttribute[];

            if (keyAttributes != null)
            {
                keyColumn = property.Name;
                keyColumnValue = property.GetValue(entity, null);
                break;
            }
        }

        OperatorExpressionNode queryNode = new OperatorExpressionNode(
            ExpressionNodeType.Equal,
            new ParameterExpressionNode(keyColumn),
            new DataExpressionNode(keyColumnValue));

        queryConditionTree = new ExpressionTree(queryNode);
    }

    Configuration.EntityConfiguration entityConfiguration =
        this._entityConfiguration.EntityConfigurations.GetConfigurationFromType(typeof(T).FullName);

    IDataReader reader = this._provider.ExecuteQuery(this.PrepareStatement<T>(queryConditionTree), null);

    while (reader.Read())
    {
        MethodInfo bindingMethod = this.GetType().GetMethod("DoObjectBinding", BindingFlags.Instance | BindingFlags.NonPublic);
        bindingMethod = bindingMethod.MakeGenericMethod(typeof(T));
        bindingMethod.Invoke(this, new object[] { reader, entity, entityConfiguration });

        break;
    }

    reader.Close();

    return entity;
}

透過 PrepareStatement<T>() 產生的 SQL 會是這樣：


SELECT Orders.OrderID, 
       Orders.OrderDate, 
       Orders.RequiredDate, 
       Orders.ShippedDate, 
       Orders.Freight, 
       Orders.ShipName, 
       Orders.ShipAddress, 
       Orders.ShipCity, 
       Orders.ShipRegion, 
       Orders.ShipPostalCode, 
       Orders.ShipCountry, 
       Customers.CustomerID, 
       Employees.EmployeeID 
FROM Orders 
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID 
     INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

而帶有參數的則會是這樣：


SELECT Customers.CustomerID, 
               Customers.CompanyName, 
               Customers.ContactName, 
               Customers.ContactTitle, 
               Customers.Address, 
               Customers.City, 
               Customers.Region, 
               Customers.PostalCode,
               Customers.Country, 
               Customers.Phone, 
               Customers.Fax 
FROM Customers 
WHERE (CustomerID = 'VINET')

最後，我們就可以修改一下用戶端程式：


DB.DataContext db = new DB.DataContext("sql");
OrderCollection orderList = db.GetEntities<OrderCollection, Order>();

foreach (Order orderItem in orderList)
{
    Console.WriteLine("id: {0}, order date: {1}, ship date: {2}, ship name: {3}, ship address: {4}, customer company name: {5}, employee name: {6}",
        orderItem.OrderID, orderItem.OrderDate, orderItem.ShippedDate, orderItem.ShipName, orderItem.ShipAddress,
        orderItem.Customer.CompanyName, orderItem.Employee.FirstName + " " + orderItem.Employee.LastName);
}

實際執行，可以看到 Order 物件以及所屬的 Customer 和 Employee 資料都有被載入，表示程式可以正常運作。

Sample Code: https://dotblogsfile.blob.core.windows.net/user/regionbbs/1111/2011112416264552.rar

回首頁

小朱® 的技術隨手寫

本部落格不吹捧、不自戀、不造神，也絕不用技術文偷渡政治文，請各位朋友安心服用。

[Data Access] ORM 原理 (7) 物件關聯性

標籤雲