LLBL Gen對單個表的查詢，這裡再來分析一下LLBL Gen對多表的操作。
Adapter模式,用到的變量adapter定義為DataAccessAdapter adapter=new DataAccessAdapter ();
SalesOrder是采購單主表，它下面有明細表SalesOrderDetail,用於記錄采購單的物料信息。
查詢編號為OE20100821的采購單及其物料明細
SalesOrderEntity salesOrder=new SalesOrderEntity (“OE20100821”);
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.SalesOrderEntity);
prefetchPath.Add(SalesOrderEntity.PrefetchPathSalesOrderDetails);
bool found = adapter.FetchEntity(salesOrder, prefetchPath, null, null);
PrefetchPath為因深度不同，一次可讀取多個表的記錄到內存中。LLBL Gen要求明確指定需要讀取的從表深度。
保存采購單及其物料明細。先保存采購單主表，再保存從表采購單明細
SalesOrderEntity salesOrder=new SalesOrderEntity (“OE20100821”);
adapter.SaveEntity(salesOrder, true, false);
foreach (SalesOrderDetailEntity detail in salesOrder.SalesOrderDetails)
{
adapter.SaveEntity(detail);
}
刪除采購單。先刪除從表采購單明細，再刪除采購單主表
SalesOrderEntity salesOrder;//OrderNo=”OE20100821”
foreach (SalesOrderDetailEntity detail in salesOrder.SalesOrderDetails)
{
adapter.DeleteEntity(detail);
}
adapter.DeleteEntity(salesOrder);
LLBL Gen框架可以區分INSERT/UPDATE命令，統一用方法SaveEntity即可。
判斷實體是新增加的還是從數據庫中讀取的，可用如下的方法
bool loadedCorrectly = (salesOrder.Fields.State == EntityState.Fetched);
SQL語句如下，這個例子也演示了表達式的用法。
SELECT OrderID,ProductID,(UnitPrice * Quantity) AS RowTotal FROm [Order Details]
對應的LLBL Gen的寫法如下
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(OrderDetailsFields.OrderId, 0);
fields.DefineField(OrderDetailsFields.ProductId, 1);
fields.DefineField(new EntityField2("RowTotal",(OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity)), 2);
DataTable results = new DataTable();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, results, null);
再來看一個復雜的從多表中查詢結果的例子
先定義存放結果的DataTable,根據字段需要設計對應的表結構
DataTable inventoryBalance= new FastSerializableDataTable("InventoryBalance");
inventoryBalance.Columns.Add("Loc", typeof(string));
inventoryBalance.Columns.Add("Description", typeof(string));
inventoryBalance.Columns.Add("QtyOnHand", typeof(decimal));
inventoryBalance.Columns.Add("QtyOnInspect", typeof(decimal));
定義數據項的來源
ResultsetFields inventoryBalanceFields = new ResultsetFields(4);
inventoryBalanceFields .DefineField(InventoryBalanceFields.Loc, 0);
inventoryBalanceFields .DefineField(LocationFields.Description, 1);
inventoryBalanceFields .DefineField(InventoryBalanceFields.QtyOnHand, 2);
inventoryBalanceFields .DefineField(InventoryBalanceFields.QtyOnInspect, 3);
定義查詢過濾條件
IRelationPredicateBucket filterBucket = new RelationPredicateBucket();
filterBucket.PredicateExpression.Add(InventoryBalanceFields.ItemNo == itemNo);
設定以上數據來源項的表之間的關系
filterBucket.Relations.Add(InventoryBalanceEntity.Relations.LocationEntityUsingLoc);
對結果排序
ISortExpression sorter = new SortExpression(InventoryBalanceFields.ItemNo | SortOperator.Ascending)
最後加載數據到內存表中
adapter.FetchTypedList(inventoryBalanceFields, inventoryBalance, filterBucket, 0, sorter, false, null);
倉庫LocationEntity與庫存余額InventoryBalanceEntity的對應關系是一對一，使用Loc倉庫編碼來關聯。
這個查詢還可以復雜一些，要查詢的字段不一定是數據庫有的，比如聚合(求和，求平均值)函數的結果。
我們在為上面的查詢添加一個字段，ORM寫法如下,表示已經下采購單的物料合計數量
DbFunctionCall dbFunQtyUnused = new DbFunctionCall("ISNULL", new object[] { (SalesOrderDetailFields.QtyOrdered), 0 });
EntityField2 eQtyOrder = new EntityField2("QtyOrdered", dbFunQtyUnused);
inventoryBalanceFields.DefineField(eQtyOrder , 4, "QtyOrdered");
inventoryBalanceFields[4].AggregateFunctionToApply = AggregateFunction.Sum;
因為新加入了表SalesOrderEntity，需要增加關系,與倉庫關聯為采購單的每一筆物料的進倉倉庫。
filterBucket.Relations.Add(InventoryBalanceEntity.Relations.SalesOrderDetailEntityUsingLoc);
先看一個例子，SQL語句如下，查詢客戶表中的客戶編號及其所有的訂單合計數量
SELECT CustomerID,
( SELECT COUNT(*) FROM Orders WHERE CustomerID = Customers.CustomerID ) AS NumberOfOrders FROM Customers
Adapter模式的ORM的寫法如下
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.CustomerID, 0);
fields.DefineField(new EntityField2("NumberOfOrders", new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), (CustomerFields.CustomerId == OrderFields.CustomerId))), 1);
DataTable results = new DataTable();
adapter.FetchTypedList(fields, results, null);
如果只需要查詢單個值，則DataAccessAdapter有更直接的支持，請看下面的例子
IRelationPredicateBucket filterBucket = new RelationPredicateBucket();
filterBucket.PredicateExpression.Add(SalesOrderDetailFields.ItemNo == ‘PC’);
filterBucket.PredicateExpression.Add(SalesOrderFields.OrderNo== ‘OE20110921’);
filterBucket.Relations.Add(SalesOrderEntity.Relations.SalesOrderDetailEntityUsingOrderNo);
DbFunctionCall dbFunQtyOrdered = new DbFunctionCall("IsNull", new object[] { (SalesOrderDetailFields.QtyOrdered), 0 });
object result = adapter.GetScalar(SalesOrderDetailFields.QtyOrdered, dbFunQtyOrdered , AggregateFunction.Sum, filterBucket);
這段ORM語句是要查詢采購單OE20110921中的物料為PC的物料合計數量，用SQL寫出來就是
SELECT SUM(QtyOrdered) FROM SalesOrder a, SalesOrderDetail b WHERE a.OrderNo=b.OrderNo
AND a.OrderNo=’OE20110921’ AND b.ItemNo=’PC’
聚合函數應用的場景，常常如下所示，就是需要構建結果集ResultsetFields
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Country, 0, "Country");
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers");
fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct
要查詢的數據如果來源於數據庫，可以應用以上的幾種方法，如果要查詢的數據來源於內存集合，則應用下面的模式。
比如，要找出采購單明細表中未出貨的物料，則應用以下的寫法
List<int> indices = SalesOrderDetails.FindMatches(SalesOrderDetailFields.QtyPacked <= 0);
for (int i = indices.Count - 1; i >= 0; i--)
{
SalesOrderDetails unPackedOrder=SalesOrderDetails[indices[i]);
}
FindMatches方法也可以傳入下面的類型
IPredicate filterBucket = (SalesOrderDetailFields.QtyPacked <= 0);
如果要操作的數據有先後之分，並且些時EntityCollection中的數據不是業務規則操作需要的順序，則可以以下面的模式，用EntityView2來排序過濾數據，重新獲取按照指定規則排序的數據。
IPredicate filterBucket = (SalesOrderDetailFields.ItemNo == “PC”);
ISortExpression sortExpression = new SortExpression();
sortExpression.Add(SalesOrderDetailFields.ItemNo| SortOperator.Ascending);
EntityCollection<SalesOrderDetailEntity> detailEntities = SalesOrder.SalesOrderDetails;
EntityView2<SalesOrderDetailEntity> entityView = new EntityView2<SalesOrderDetailEntity>(detailEntities);
entityView.Filter = filterBucket;
entityView.Sorter = sortExpression;
關鍵的三句,依據EntityCollection構造EntityView2,並且傳入指定的排序和過濾條件。
EntityCollection<SalesOrderDetailEntity> tmpCollection = (EntityCollection<SalesOrderDetailEntity>)entityView.ToEntityCollection();
foreach (SalesOrderDetailEntity detail in tmpCollection)
{
//在這裡，采購單中的物料明細已經按照物料編號升序排序了
}
自從應用ORM後，很少寫SELECT * 這樣的查詢語句，因為這樣太耗費時間，影響Performance，這種模式如下
IncludeFieldsList fieldList = new IncludeFieldsList();
fieldList.Add(SalesOrderFields.DueDay);
fieldList.Add(SalesOrderFields.OrdreNo);
SalesOrderEntity sales=adapter.FetchEntity(salesOrder, prefetchPath, null, fieldList);
然後會應用讀到的值DueDay和OrdreNo，如果SalesOrderEntity還有一個字段是SalesMan(string,varchar(50)),
在這種讀取模式下，這個值永遠是string.Empty.
