在原理 (7) 中，我們完成了關聯的基本處理，只是我們做到的是一對一的關聯，如果今天要的是一對多的關聯時，我們就需要處理到集合物件，集合物件不像單一物件那麼簡單，尤其是集合物件的元素又和其他物件有關聯時，載入的方式就會決定程式的速度，以我們到目前為止的例子，Customers, Orders 和 Employees 三個表格，Customers 會和 Orders 有一對多的關係，而 Orders 和 Customers 與 Employees 有一對一的關係，我們在原理 (7) 中實作的是 Order 類別，所以是一對一，但如果我們要實作 Customers 和 Orders 之間的關係，會變成一對多，也就是我們要處理 Customer 類別內的 OrderCollection 集合物件...
在原理 (7) 中，我們完成了關聯的基本處理，只是我們做到的是一對一的關聯，如果今天要的是一對多的關聯時，我們就需要處理到集合物件，集合物件不像單一物件那麼簡單，尤其是集合物件的元素又和其他物件有關聯時，載入的方式就會決定程式的速度，以我們到目前為止的例子，Customers, Orders 和 Employees 三個表格，Customers 會和 Orders 有一對多的關係，而 Orders 和 Customers 與 Employees 有一對一的關係，我們在原理 (7) 中實作的是 Order 類別，所以是一對一，但如果我們要實作 Customers 和 Orders 之間的關係，會變成一對多，也就是我們要處理 Customer 類別內的 OrderCollection 集合物件。
1: public class Customer : EntityObject
2: {
3: public string CustomerID { get; set; }
4: public string CompanyName { get; set; }
5: public string ContactName { get; set; }
6: public string ContactTitle { get; set; }
7: public string Address { get; set; }
8: public string City { get; set; }
9: public string Region { get; set; }
10: public string PostalCode { get; set; }
11: public string Country { get; set; }
12: public string Phone { get; set; }
13: public string Fax { get; set; }
14:
15: public OrderCollection Orders { get; set; }
16: }
集合物件在處理上要考量的第一個因素，就是我們要花多少成本在載入資料上，以一般撰寫資料庫應用程式而言，有兩種作法，一種是一次 JOIN 所有的資料，另一種則是開不同的連線來載入資料，依照實測的結果，一次 JOIN 所有的資料速度會比開不同的連線要來得快，透過 JOIN 載入資料時間上也不會太慢，除非資料量大。只是資料載回來後還要把資料塞到集合中，這一點就有點麻煩，因為集合物件是依附在 entity 物件之下，當我們使用一次 JOIN 方式載回資料時，在 entity 上會有很多重覆資料出現，要怎麼略過這些重覆的 entity 資料，以處理我們需要的集合元素資料，就是要考量的第二個因素。
為了要達成這個目的，我們改寫了 DataContext.GetEntities<TCollection, T>() 方法：
1: public TCollection GetEntities<TCollection, T>()
2: where TCollection : List<T>
3: where T : class
4: {
5: TCollection entityCollection = (TCollection)Activator.CreateInstance(typeof(TCollection));
6: Configuration.EntityConfiguration entityConfiguration =
7: this._entitySetConfiguration.EntityConfigurations.GetConfigurationFromType(typeof(T).FullName);
8:
9: IDataReader reader = this._provider.ExecuteQuery(this.PrepareStatement<T>(), null);
10:
11: while (reader.Read())
12: {
13: T entity = null;
14: bool entityExists = false;
15:
16: if (entityCollection.Count > 0)
17: {
18: // check the key value for element is exist.
19: List<Configuration.EntitySchemaMap> keyColumns = entityConfiguration.EntitySchemaMaps.GetKeyColumns();
20: entity = entityCollection[entityCollection.Count - 1];
21:
22: foreach (Configuration.EntitySchemaMap keyColumn in keyColumns)
23: {
24: PropertyInfo entityProperty = entity.GetType().GetProperty(keyColumn.EntityPropertyName);
25:
26: // handle type casting.
27: TypeConverters.ITypeConverter typeConverter = TypeConverters.TypeConverterFactory.GetConvertType(entityProperty.PropertyType);
28: object value = null;
29:
30: if (!entityProperty.PropertyType.IsEnum)
31: {
32: value = Convert.ChangeType(typeConverter.Convert(reader.GetValue(reader.GetOrdinal(keyColumn.EntitySchemaName))), entityProperty.PropertyType);
33: }
34: else
35: {
36: TypeConverters.EnumConverter converter = typeConverter as TypeConverters.EnumConverter;
37: value = Convert.ChangeType(converter.Convert(entityProperty.PropertyType, reader.GetValue(reader.GetOrdinal(keyColumn.EntitySchemaName))), entityProperty.PropertyType);
38: }
39:
40: if (!this.CompareValue(entityProperty.GetValue(entity, null), value))
41: {
42: // if one of key value is not match, object is different.
43: entity = Activator.CreateInstance(typeof(T)) as T;
44: break;
45: }
46:
47: entityExists = true;
48: }
49: }
50: else
51: entity = Activator.CreateInstance(typeof(T)) as T;
52:
53: MethodInfo bindingMethod = this.GetType().GetMethod("BindingDataFromSchemaToProperty", BindingFlags.Instance | BindingFlags.NonPublic);
54: bindingMethod = bindingMethod.MakeGenericMethod(typeof(T));
55: bindingMethod.Invoke(this, new object[] { reader, entity, entityConfiguration, entityExists });
56:
57: if (!entityExists)
58: {
59: entityCollection.Add(entity);
60: }
61: }
62:
63: reader.Close();
64:
65: return entityCollection;
66: }
改寫的部份是判斷 entity 本身是否有被載入過，透過這個方式來判斷重覆資料，並且將處理集合的工作交給 BindingDataFromSchemaToProperty<T>() 方法：
1: private void BindingDataFromSchemaToProperty<T>(ref IDataReader reader, ref T entity, Configuration.EntityConfiguration entityConfiguration, bool entityIsExist)
2: {
3: PropertyInfo[] properties = entity.GetType().GetProperties();
4: Dictionary<string, int> propertySchemaMapList = new Dictionary<string, int>();
5: bool isEntityLoaded = false;
6:
7: foreach (PropertyInfo property in properties)
8: {
9: int ordinal = -1;
10: Type propType = property.PropertyType;
11:
12: // get attribute.
13: Configuration.EntitySchemaMap schemaMap = entityConfiguration.EntitySchemaMaps.GetConfigurationFromPropertyName(property.Name);
14:
15: if ((!entityIsExist) && schemaMap != null)
16: {
17: // get column index, if not exist, set -1 to ignore.
18: if (!propertySchemaMapList.ContainsKey(schemaMap.EntitySchemaName))
19: {
20: try
21: {
22: ordinal = reader.GetOrdinal(schemaMap.EntitySchemaName);
23: }
24: catch (IndexOutOfRangeException)
25: {
26: ordinal = -1;
27: }
28:
29: propertySchemaMapList.Add(schemaMap.EntitySchemaName, ordinal);
30: }
31: else
32: ordinal = propertySchemaMapList[schemaMap.EntitySchemaName];
33:
34: // set value.
35: if (ordinal >= 0)
36: {
37: TypeConverters.ITypeConverter typeConverter = TypeConverters.TypeConverterFactory.GetConvertType(propType);
38:
39: if (!propType.IsEnum)
40: {
41: property.SetValue(entity,
42: Convert.ChangeType(typeConverter.Convert(reader.GetValue(ordinal)), propType), null);
43: }
44: else
45: {
46: TypeConverters.EnumConverter converter = typeConverter as TypeConverters.EnumConverter;
47: property.SetValue(entity,
48: Convert.ChangeType(converter.Convert(propType, reader.GetValue(ordinal)), propType), null);
49: }
50:
51: if (!isEntityLoaded)
52: {
53: isEntityLoaded = true;
54: (entity as EntityObject).SetEntityLoaded();
55: }
56: }
57: }
58: else
59: {
60: // search schema.
61: Configuration.EntityRelationMap relationMap = entityConfiguration.EntityRelationMaps.GetConfigurationFromPropertyName(property.Name);
62: DataContext context = new DataContext(this._schemaConfigurationName);
63: MethodInfo methodGetEntity = null;
64: MethodInfo methodGetEntities = null;
65:
66: MethodInfo[] methods = context.GetType().GetMethods(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
67:
68: foreach (MethodInfo method in methods)
69: {
70: if (method.Name == "GetEntity")
71: methodGetEntity = method;
72:
73: if (method.Name == "InternalGetEntities")
74: methodGetEntities = method;
75: }
76:
77: if (relationMap != null)
78: {
79: if (!string.IsNullOrEmpty(
80: this._entitySetConfiguration.EntityCollections.GetEntityTypeFromCollectionTypeName(
81: property.PropertyType.FullName)))
82: {
83: object collection = property.GetValue(entity, null);
84: bool isEntityObjectLoaded = false;
85:
86: if (collection == null)
87: collection = Activator.CreateInstance(property.PropertyType);
88:
89: string entityType = this._entitySetConfiguration.EntityCollections.GetEntityTypeFromCollectionTypeName(property.PropertyType.FullName);
90: object entityObject = Activator.CreateInstance(Type.GetType(entityType));
91: List<Configuration.EntitySchemaMap> targetSchemaKeyColumns =
92: this._entitySetConfiguration.EntityConfigurations.GetConfigurationFromType(entityType).EntitySchemaMaps.GetKeyColumns();
93:
94: foreach (Configuration.EntitySchemaMap targetSchemaKeyColumn in targetSchemaKeyColumns)
95: {
96: try
97: {
98: object value = reader.GetValue(reader.GetOrdinal(targetSchemaKeyColumn.EntitySchemaName));
99:
100: if (value != DBNull.Value)
101: {
102: PropertyInfo entityObjectProperty = entityObject.GetType().GetProperty(targetSchemaKeyColumn.EntityPropertyName);
103: entityObjectProperty.SetValue(entityObject, value, null);
104:
105: if (!isEntityObjectLoaded)
106: {
107: (entityObject as EntityObject).SetEntityKeyLoaded();
108: isEntityObjectLoaded = true;
109: }
110: }
111: }
112: catch (IndexOutOfRangeException)
113: {
114: }
115: }
116:
117: //PropertyInfo[] entityObjectProperties = entityObject.GetType().GetProperties();
118:
119: //foreach (PropertyInfo entityObjectProperty in entityObjectProperties)
120: //{
121: // try
122: // {
123: // ordinal = reader.GetOrdinal(entityObjectProperty.Name);
124: // object value = reader.GetValue(ordinal);
125:
126: // if (value != DBNull.Value && ordinal >= 0)
127: // {
128: // TypeConverters.ITypeConverter typeConverter = TypeConverters.TypeConverterFactory.GetConvertType(entityObjectProperty.PropertyType);
129:
130: // if (!entityObjectProperty.PropertyType.IsEnum)
131: // {
132: // entityObjectProperty.SetValue(entityObject,
133: // Convert.ChangeType(typeConverter.Convert(reader.GetValue(ordinal)), entityObjectProperty.PropertyType), null);
134: // }
135: // else
136: // {
137: // TypeConverters.EnumConverter converter = typeConverter as TypeConverters.EnumConverter;
138: // entityObjectProperty.SetValue(entityObject,
139: // Convert.ChangeType(converter.Convert(propType, reader.GetValue(ordinal)), entityObjectProperty.PropertyType), null);
140: // }
141:
142: // if (!isEntityObjectLoaded)
143: // {
144: // (entityObject as EntityObject).SetEntityLoaded();
145: // isEntityObjectLoaded = true;
146: // }
147: // }
148: // }
149: // catch (IndexOutOfRangeException)
150: // {
151: // }
152: //}
153:
154: // append object into collection.
155: if (isEntityObjectLoaded)
156: collection.GetType().GetMethod("Add").Invoke(collection, new object[] { Convert.ChangeType(entityObject, Type.GetType(entityType)) });
157:
158: property.SetValue(entity, collection, null);
159: }
160: else
161: {
162: // load relation object. (single object).
163: ordinal = reader.GetOrdinal(relationMap.TargetSchemaPropertyName);
164: object entityObject = Activator.CreateInstance(property.PropertyType);
165: PropertyInfo[] entityObjectProperties = entityObject.GetType().GetProperties();
166:
167: foreach (PropertyInfo entityObjectProperty in entityObjectProperties)
168: {
169: if (relationMap.SourceSchemaPropertyName == entityObjectProperty.Name)
170: {
171: entityObjectProperty.SetValue(entityObject, reader.GetValue(ordinal), null);
172: }
173: }
174:
175: property.SetValue(entity, entityObject, null);
176: }
177: }
178: }
179: }
180: }
重點是在行號 79-158 之間的程式，該段程式是處理集合的程式碼，其中有一段被註解掉的，是做 Full Loading 的程式碼，它會將所有的資料載到元素物件，再交給集合物件。不過 Full Loading 的時間，依我們實測的結果，載入 Customers 以及所屬 Orders 所需要的時間要 35 秒左右，實在不利於應用程式的效能，這也是 ORM Framework 會遇到的一個主要問題，所以才會有 Lazy Loading (延遲載入) 的技術出現，我們不需要一開始就載入所有的資料，只要在呼叫時載入即可，因此上面的實作中的 94-115 行，就是處理延遲載入的工作，我們只要載入元素的鍵值，以後真的需要時再載入全部的資料即可。透過延遲載入的方式，程式所需要的時間可以縮短到只要 0.3-0.5 秒，速度差了 35 倍以上。
另外一個要處理的地方，是如果設定 LEFT JOIN 但沒有可對應的資料時要怎麼處理，在本例中我們只要發現資料有被載入到物件時，就設定一個 Loaded 的旗標，為了要達到這個目的，我們新增了一個 EntityObject 類別，並且設定所有資料類別都繼承它：
1: public abstract class EntityObject
2: {
3: public bool IsLoaded { get; private set; }
4: public bool IsKeyLoaded { get; private set; }
5: public DateTime LastModifiedDate { get; private set; }
6: public bool IsModified { get; private set; }
7:
8: public EntityObject()
9: {
10: this.IsLoaded = false;
11: this.LastModifiedDate = DateTime.MinValue;
12: this.IsModified = false;
13: }
14:
15: public void SetEntityLoaded()
16: {
17: this.IsLoaded = true;
18: }
19:
20: public void SetEntityKeyLoaded()
21: {
22: this.IsKeyLoaded = true;
23: }
24:
25: public void SetEntityModified()
26: {
27: this.IsModified = true;
28: this.LastModifiedDate = DateTime.Now;
29: }
30: }
我們也修改了組態檔，加入關聯的設定 (LEFT JOIN)：
1: <entity type="ConsoleApplication2.Customer" schema="Customers">
2: <maps>
3: <map propertyName="CustomerID" schemaName="CustomerID" isKey="true" />
4: <map propertyName="CompanyName" schemaName="CompanyName" />
5: <map propertyName="ContactName" schemaName="ContactName" />
6: <map propertyName="ContactTitle" schemaName="ContactTitle" />
7: <map propertyName="Address" schemaName="Address" />
8: <map propertyName="City" schemaName="City" />
9: <map propertyName="Region" schemaName="Region" />
10: <map propertyName="PostalCode" schemaName="PostalCode" />
11: <map propertyName="Country" schemaName="Country" />
12: <map propertyName="Phone" schemaName="Phone" />
13: <map propertyName="Fax" schemaName="Fax" />
14: </maps>
15: <relations>
16: <relation propertyName="Orders"
17: mapSchemaName="Orders"
18: mapType="LEFT JOIN"
19: sourceSchemaPropertyName="CustomerID"
20: targetSchemaPropertyName="CustomerID"
21: mapExpression="=" />
22: </relations>
23: </entity>
完成以後，修改主程式：
1: DB.DataContext db = new DB.DataContext("sql");
2: CustomerCollection customerList = db.GetEntities<CustomerCollection, Customer>();
3:
4: foreach (Customer customer in customerList)
5: {
6: Console.WriteLine("id: {0}, name: {1}, order count: {2}", customer.CustomerID, customer.CompanyName, customer.Orders.Count);
7: }
執行一下，我們可以看到執行很快，然後試著修改在 BindingDataFromSchemaToProperty<T>() 中的程式，將 Lazy Loading 改成 Full Loading，感受一下速度上的差異。
Source Code: https://dotblogsfile.blob.core.windows.net/user/regionbbs/1111/20111125121157709.rar
