LLBL Gen Pro是一个为.NET开发人员设计的的对象关系映射(ORM)框架，与NHibernate,Entity Framework等框架一样，通过实体与数据表的映射，实现关系数据库持久化。
1 LLBL Gen Pro 入门 LLBL Gen Pro Basic
打开LLBL Gen Pro程序，在右边的数据库浏览器(Catelog Explorer)中根结点右键选择从关系数据库创建关系模型(
Add Relational Model Data from a Database)，然后根据SQL Server，并且填入登录帐号和密码。
最终的界面如下图的所示，点击工具栏按钮生成.NET项目文件和实体映射文件。
1.1 持久化类 Persistent classes
以数据库表销售合同为例，它的数据库表结构定义如下。
CREATE TABLE [dbo].[SLORDR] ( [RECNUM] [decimal] (18, 0) NOT NULL IDENTITY(1, 1), [CONTRACT_NO] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CUSTOMER_NO] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CUSTOMER_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CONTRACT_DATE] [datetime] NULL, [REMARK] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CREATED_DATE] [datetime] NULL, [CREATED_BY] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [REVISED_DATE] [datetime] NULL, [REVISED_BY] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CLOSED] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[SLORDR] ADD CONSTRAINT [PK_SLORDR] PRIMARY KEY CLUSTERED ([CONTRACT_NO]) ON [PRIMARY] GO
通过LLBL Gen Pro，生成的实体的代码量比较多，上面的销售合同数据表生成的实体类型定义源代码有600多行。作为入门的例子，先了解LLBL Gen Pro生成的构造方法和字段映射。先看销售合同实体的构造方法：
/// <summary> Static CTor for setting up custom property hashtables. Is executed before the first instance of this entity class or derived classes is constructed. </summary> static SalesContractEntity() { SetupCustomPropertyHashtables(); } /// <summary> CTor</summary> public SalesContractEntity():base("SalesContractEntity") { InitClassEmpty(null, null); } /// <summary> CTor</summary> /// <remarks>For framework usage.</remarks> /// <param name="fields">Fields object to set as the fields for this entity.</param> public SalesContractEntity(IEntityFields2 fields):base("SalesContractEntity") { InitClassEmpty(null, fields); } /// <summary> CTor</summary> /// <param name="validator">The custom validator object for this SalesContractEntity</param> public SalesContractEntity(IValidator validator):base("SalesContractEntity") { InitClassEmpty(validator, null); } /// <summary> CTor</summary> /// <param name="contractNo">PK value for SalesContract which data should be fetched into this SalesContract object</param> /// <remarks>The entity is not fetched by this constructor. Use a DataAccessAdapter for that.</remarks> public SalesContractEntity(System.String contractNo):base("SalesContractEntity") { InitClassEmpty(null, null); this.Fields[(int)SalesContractFieldIndex.ContractNo].CurrentValue = contractNo; } /// <summary> CTor</summary> /// <param name="contractNo">PK value for SalesContract which data should be fetched into this SalesContract object</param> /// <param name="validator">The custom validator object for this SalesContractEntity</param> /// <remarks>The entity is not fetched by this constructor. Use a DataAccessAdapter for that.</remarks> public SalesContractEntity(System.String contractNo, IValidator validator):base("SalesContractEntity") { InitClassEmpty(validator, null); this.Fields[(int)SalesContractFieldIndex.ContractNo].CurrentValue = contractNo; } /// <summary> Protected CTor for deserialization</summary> /// <param name="info"></param> /// <param name="context"></param> [EditorBrowsable(EditorBrowsableState.Never)] protected SalesContractEntity(SerializationInfo info, StreamingContext context) : base(info, context) { if(SerializationHelper.Optimization != SerializationOptimization.Fast) { _salesContractDetail = (EntityCollection<SalesContractDetailEntity>)info.GetValue("_salesContractDetail", typeof(EntityCollection<SalesContractDetailEntity>)); this.FixupDeserialization(FieldInfoProviderSingleton.GetInstance()); } // __LLBLGENPRO_USER_CODE_REGION_START DeserializationConstructor // __LLBLGENPRO_USER_CODE_REGION_END }
一共是7个构造方法，它们的作用说明如下：
1 静态构造方法 调用SetupCustomPropertyHashtables方法以初始化属性的自定义元数据。
2 默认无参数构造方法，以主键为参数的构造方法应用于实际开发过程。
3 以IEntityFields2为参数的构造方法，被框架使用。
4 以IValidator为参数的构造方法，用于自定义验证类型。
5 以SerializationInfo为参数的方法用于序列化传输。
1.2 映射 Mapping
LLBL Gen Pro框架工具会生成2个项目文件，实体定义放在项目DatabaseGeneric中，实体与数据表的映射放在DatabaseSpecific，也就是前者是数据库无关的，后者与数据库的具体特性相关联。比如生成存储过程调用，则代码生成会放到DatabaseSpecific项目中。来看一下上面的销售合同表是如何与实体映射的。
/// <summary>Inits SalesContractEntity's mappings</summary> private void InitSalesContractEntityMappings() { this.AddElementMapping( "SalesContractEntity", "dbEnterprise", @"dbo", "SLORDR", 11 ); this.AddElementFieldMapping( "SalesContractEntity", "Recnum", "RECNUM", false, "Decimal", 0, 0, 18, true, "SCOPE_IDENTITY()", null, typeof(System.Decimal), 0 ); this.AddElementFieldMapping( "SalesContractEntity", "ContractNo", "CONTRACT_NO", false, "NVarChar", 20, 0, 0, false, "", null, typeof(System.String), 1 ); this.AddElementFieldMapping( "SalesContractEntity", "CustomerNo", "CUSTOMER_NO", true, "NVarChar", 8, 0, 0, false, "", null, typeof(System.String), 2 ); this.AddElementFieldMapping( "SalesContractEntity", "CustomerName", "CUSTOMER_NAME", true, "NVarChar", 50, 0, 0, false, "", null, typeof(System.String), 3 ); this.AddElementFieldMapping( "SalesContractEntity", "ContractDate", "CONTRACT_DATE", true, "DateTime", 0, 0, 0, false, "", null, typeof(System.DateTime), 4 ); this.AddElementFieldMapping( "SalesContractEntity", "Remark", "REMARK", true, "NVarChar", 4000, 0, 0, false, "", null, typeof(System.String), 5 ); this.AddElementFieldMapping( "SalesContractEntity", "CreatedDate", "CREATED_DATE", true, "DateTime", 0, 0, 0, false, "", null, typeof(System.DateTime), 6 ); this.AddElementFieldMapping( "SalesContractEntity", "CreatedBy", "CREATED_BY", true, "NVarChar", 8, 0, 0, false, "", null, typeof(System.String), 7 ); this.AddElementFieldMapping( "SalesContractEntity", "RevisedDate", "REVISED_DATE", true, "DateTime", 0, 0, 0, false, "", null, typeof(System.DateTime), 8 ); this.AddElementFieldMapping( "SalesContractEntity", "RevisedBy", "REVISED_BY", true, "NVarChar", 8, 0, 0, false, "", null, typeof(System.String), 9 ); this.AddElementFieldMapping( "SalesContractEntity", "Closed", "CLOSED", true, "NVarChar", 1, 0, 0, false, "", new ISL.TypeConverters.BooleanStringConverter(), typeof(System.String), 10 ); }
与NHiberate不同，LLBL Gen Pro将对象关系映射直接存储在源代码中，数据表字段与实体属性的映射由代码生成工具维护。
1.3 数据读写 Data access
能过以下几行简单的代码例子，了解LLBL Gen Pro提供的数据访问接口，下面的代码适用于Adapter模式。
//读取实体 DataAccessAdapter adapter... SalesContractEntity salesContract = new SalesContractEntity("SC201507260001"); adapter.FetchEntity(salesContract, prefetchPath, null, fieldList); //保存实体 DataAccessAdapter adapter... SalesContractEntity salesContract =new SalesContractEntity("SC201507260001"); salesContract .Customer="FLEXTRONICS"; adapter.SaveEntity(salesContract , true, false); //删除实体 DataAccessAdapter adapter... SalesContractEntity salesContract... adapter.DeleteEntity(salesContract);
DataAccessAdapter 是LLBL Gen Pro生成的项目DatabaseSpecific中的一个类型定义，是Adapter模式下标准的数据访问接口。
2 数据访问接口 Data access adapter
DataAccessAdapter 对Adapter模式，这个类型是数据访问接口的全部。如果是自治(SelfServicing)模式，则数据的增删改方法会直接附加到实体类型定义中。
EntityCollection 对象集合 实体的容器，一般用于数据设计时绑定和实体集的表示。有泛型和非泛型两个版本。
泛型版本的创建例子如下代码所示：
EntityCollection collection = new EntityCollection(new SalesContractEntityFactory());
EntityState 对象的状态 参考下面的枚举类型定义，对象可处于4种状态，New表示对象在内存中刚刚创建，Fetched表示对象从数据库中刚刚取到内存，OutOfSync表示对象的值与数据库中的值不一致，Deleted表示对象已经被删除。
public enum EntityState { New, Fetched, OutOfSync, Deleted, }
事务 为保证数据操作中发生异常的回滚操作，事务的例子代码如下所示。
try { adapter.StartTransaction(IsolationLevel.ReadCommitted, "Post SalesContract"); adapter.DeleteEntity(SalesContract); adapter.Commit(); } catch { adapter.Rollback(); throw; }
存储过程 LLBL Gen Pro支持两种存储过程，一种需要返回值的定义为RetrievalProcedures，另一种是执行数据操作不返回结果的ActionProcedures。
数据库连接 类型DataAccessAdapter封装了数据访问接口，比如打开与关闭数据库连接，开启事务。
adapter.OpenConnection(); adapter.CloseConnection();
3 对象关系映射 Object relational mapping
3.1 属性映射 property mapping
以销售合同表(SLORDR)的客户编号(Customer_No)，客户名称(Customer_Name)，合同日期(Contract_Date)三个字段为例子，参考下面的属性与数据表字段定义的映射。
/// <summary> The CustomerNo property of the Entity SalesContract<br/> /// Mapped on table field: "SLORDR"."CUSTOMER_NO"<br/> /// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 8<br/> /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false<br/><br/></summary> /// <remarks>Mapped on table field: "SLORDR"."CUSTOMER_NO"<br/> /// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 8<br/> /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks> public virtual System.String CustomerNo { get { return (System.String)GetValue((int)SalesContractFieldIndex.CustomerNo, true); } set { SetValue((int)SalesContractFieldIndex.CustomerNo, value); } } /// <summary> The CustomerName property of the Entity SalesContract<br/> /// Mapped on table field: "SLORDR"."CUSTOMER_NAME"<br/> /// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 50<br/> /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false<br/><br/></summary> /// <remarks>Mapped on table field: "SLORDR"."CUSTOMER_NAME"<br/> /// Table field type characteristics (type, precision, scale, length): NVarChar, 0, 0, 50<br/> /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks> public virtual System.String CustomerName { get { return (System.String)GetValue((int)SalesContractFieldIndex.CustomerName, true); } set { SetValue((int)SalesContractFieldIndex.CustomerName, value); } } /// <summary> The ContractDate property of the Entity SalesContract<br/> /// Mapped on table field: "SLORDR"."CONTRACT_DATE"<br/> /// Table field type characteristics (type, precision, scale, length): DateTime, 0, 0, 0<br/> /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false<br/><br/></summary> /// <remarks>Mapped on table field: "SLORDR"."CONTRACT_DATE"<br/> /// Table field type characteristics (type, precision, scale, length): DateTime, 0, 0, 0<br/> /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks> public virtual System.DateTime ContractDate { get { return (System.DateTime)GetValue((int)SalesContractFieldIndex.ContractDate, true); } set { SetValue((int)SalesContractFieldIndex.ContractDate, value); } }
LLBL Gen Pro框架依据表的主键生成实体的构造方法，设置主键字段的元数据，而不像NHibernate那样，为每个实体生成一个Id主键，参考下面的代码，重写对象的ToString方法，返回主键值。
public override string ToString() { List<string> builder = new List<string>(); foreach (IEntityField2 field2 in Fields) { if (field2.IsPrimaryKey) builder.Add(Convert.ToString(field2.CurrentValue)); } return string.Join(",", builder.ToArray()); }
实体对象继承于CommonEntityBase，CommonEntityBase继承于EntityBase2，EntityBase2又继承于IEntity2。
public abstract class EntityBase2 : IEntity2, IEntityCore, IEditableObject, IActiveContextParticipant, ITransactionalElement, ISerializable, IXmlSerializable, INotifyPropertyChanged, IDataErrorInfo, IEntityCoreInternal { }
这里需要注意一下为什么有此类型的定义后面会加数字2，这是为了支持两种模式的实体类型定义。
SelfServicing 模式的实体定义接口是IEntity，Adapter模式的实体定义接口是IEntity2。在整个LLBL Gen Pro框架中，凡是与Adapter模式相关的基类会在类型后面加一个数字2，比如上面定义的EntityBase2 。
LLBL Gen Pro不支持实体对象的继承，数据库表生成的实体之间都是平行的关系。当然也可以用面向对象的方式编程，增加一个类型继承于实体类型定义，但不推荐这样的写法。
3.2 对象生命周期 Object lifecycle
事件 Events
- Entity: Initializing 实体初始化前
- Entity: Initialized 实体初始化完成
- Entity collection: EntityRemoving 实体集合中的实体删除前
- Entity collection: EntityRemoved 实体集合中的实体删除完成(Remove或RemoveAt)
- Entity collection: EntityAdding 实体集合增加实体前
- Entity collection: EntityAdded 实体集合增加实体完成
重写方法 Overridable methods
- Entity: OnFieldsCreated(仅适用于SelfServicing) 字段创建完成
- Entity: OnFieldValueChanged 实体值更改完成。
- Entity: OnGetValue 获取值。
- Entity: OnGetValueComplete 获取值完成 。
- Entity: OnInitializing 实体初始化
- Entity: OnInitialized 初始化完成.
- Entity: OnInitClassMembersComplete 初始化类成员完成。
- Entity: OnRelatedEntitySet 相关实体设置.
- Entity: OnRelatedEntityUnset 相关实体完成
- Entity: OnSetValue 属性设置值
- Entity: OnSetValueComplete 设置值完成.
- Entity (adapter): OnBeforeEntitySave 实体保存前
- Entity / entity collection: OnGetObjectData 序列化时
- Entity / entity collection: OnDeserialized 反序列化完成。
- Entity collection: OnEntityRemoving 被删除时。
- Entity collection: OnEntityRemoved 实体集合中的实体删除后。
- Entity collection: OnEntityAdding 实体集合中增加实体前。
- Entity collection: OnEntityAdded 实体集合中增加实体后.
- DataAccessAdapter: OnInsertPersistenceInfoObjects
- TypedList: OnResultsetBuilt
- TypedList: OnRelationSetBuilt
- TypedList: OnInitialized
- TypedView: OnInitialized
3.3 实体验证 Entity validate
在实体初始化类型定义中，指定验证类型，参考下面的代码。
this.Validator =new SalesContractValidator();
SalesContractValidator验证类型的完整代码，主要重写了保存前验证和客户编号值赋值验证。
[Serializable] public partial class SalesContractValidator : ValidatorBase { // Add your own validation code between the two region markers below. You can also use a partial class and add your overrides in that partial class. // __LLBLGENPRO_USER_CODE_REGION_START ValidationCode public override void ValidateEntityBeforeSave(IEntityCore involvedEntity) { base.ValidateEntityBeforeSave(involvedEntity); SalesContractEntity salesContract = (SalesContractEntity)involvedEntity; if (string.IsNullOrEmpty(salesContract.ContractNo)) throw new EntityValidationException("Contract No. is required"); if (string.IsNullOrEmpty(salesContract.CustomerNo)) throw new EntityValidationException("Customer No. is required"); if (salesContract.IsNew) { ISalesContractManager salesContractManager = ClientProxyFactory.CreateProxyInstance<ISalesContractManager>(); if (salesContractManager.IsSalesContractExist(Shared.CurrentUserSessionId, salesContract.ContractNo)) throw new RecordDuplicatedException(salesContract.ContractNo, "Cotract No. is already used"); } } public override bool ValidateFieldValue(IEntityCore involvedEntity, int fieldIndex, object value) { bool result = base.ValidateFieldValue(involvedEntity, fieldIndex, value); if (!result) return false; switch ((SalesContractFieldIndex) fieldIndex) { case SalesContractFieldIndex.CustomerNo: return this.ValidateCustomerNo((string) value); } return true; } private bool ValidateCustomerNo(string value) { if (!string.IsNullOrEmpty(value)) { ICustomerManager customerManager = ClientProxyFactory.CreateProxyInstance<ICustomerManager>(); customerManager.ValidateCustomerNo(Shared.CurrentUserSessionId, value); } return true; } // __LLBLGENPRO_USER_CODE_REGION_END
4 对象关系 Object relation
1 主从关系 Mater/Detail
LLBL Gen Pro根据表之间的主从关系，在生成源代码时已经构建好了对象之间的关系。从表会增加一个集合属性(EntityCollection)到主表所映射的类型中，同时从表也有一个主表的属性方便引用主表的字段(属性)。
以销售合同为例子，一个销售合同包含多个销售订单，销售合同与销售订单是一对多关系。销售合同的源代码定义文件：
[Serializable] public partial class SalesContractEntity : CommonEntityBase // __LLBLGENPRO_USER_CODE_REGION_START AdditionalInterfaces // __LLBLGENPRO_USER_CODE_REGION_END { #region Class Member Declarations private EntityCollection<SalesContractDetailEntity> _salesContractDetail; // __LLBLGENPRO_USER_CODE_REGION_START PrivateMembers // __LLBLGENPRO_USER_CODE_REGION_END #endregion
在查询时，要查询出销售合同和它的销售订单明细表，可参考下面的代码。
IPrefetchPath2 prefetchPath = new PrefetchPath2((int) EntityType.SalesContractEntity); prefetchPath.Add(SalesContractEntity.PrefetchPathSalesContractDetail); SalesContractEntity salesContract = _salesContractEntityManager.GetSalesContract("SC201507270001", prefetchPath);
IPrefetchPath2用于LLBL Gen Pro关系查询，再来看一个三层结构的查询例子：
IPrefetchPathElement2 prefetchElement = prefetchPath.Add(SalesOrderEntity.PrefetchPathSalesOrderDetails); prefetchElement.SubPath.Add(SalesOrderDetailEntity.PrefetchPathSalesOrderOrderLots);
第一层表是销售订单SalesOrder，第二层是销售订单明细(物料编号，数量，单价)，第三层是销售订单物料明细下的批号(Lot)。对于数据关系中已经建立好的关系，LLBL Gen Pro都会为我们生成关系的类型定义，也支持通过代码构建关系，参考下面的查询语句。
IEntityRelation entityRelation = new EntityRelation(OrderLinkFields.OrderNo, JobOrderFields.JobNo, RelationType.OneToMany);
IRelationPredicateBucket filterBucket.Relations.Add(entityRelation);
2 关系类型 Entity relation
先来看一下LLBL Gen Pro定义的关系类型，参考下面的枚举定义。
public enum RelationType { OneToMany, OneToOne, ManyToOne, ManyToMany, }
实际应用中多对多关系的例子比较少，多对多的关系应该要考虑增加数据表，分解成一对多的关系。
5 数据查询 Query
5.1 SELECT子句
认识两个类型ExcludeFieldsList和IncludeFieldsList，及以ExcludeIncludeFieldsList。从名称中可以推测出类型的含义，IncludeFieldsList是包含要选择的字段，ExcludeFieldsList是排除不读取的字段，ExcludeIncludeFieldsList通过传入构造方法参数，简化以上两个类型的的使用。比如下面的代码，是读取指定的字段：
ExcludeIncludeFieldsList custSepcFieldList = new ExcludeIncludeFieldsList(false); custSepcFieldList.Add(CustomerSpecificationFields.Description); custSepcFieldList.Add(CustomerSpecificationFields.SalesUom); custSepcFieldList.Add(CustomerSpecificationFields.LotSize); custSepcFieldList.Add(CustomerSpecificationFields.CustItemNo); custSepcFieldList.Add(CustomerSpecificationFields.Specifications);
翻译成SQL语句，要选择的字段(IncludeFieldsList)，比如读取客户编号和客户名称:
SELECT CustomerNo,CustomerName
排除不读取的字段，比如物料主档中图片比较大，SELECT语句中不包含图片字段，就需要用这个类型的写法。
除非真的需要，尽量不要写SELECT * 读取所有字段。
5.2 FROM子句
LLBL Gen Pro根据读取的数据类型(Entity,TypeList) 自动生成FROM部分。参考读取一个实体的代码：
CustomerSpecificationEntity customerSpec = new CustomerSpecificationEntity(customerNo, itemNo); adapter.FetchEntity(customerSpecificationEntity, prefetchPath, null, fieldList);
再看下面读取一个列表(TypeList)的写法，返回一个结果集DataTable。
ResultsetFields fields = new ResultsetFields(1); fields.DefineField(JobOrderFields.JobNo, 0); IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.PredicateExpression.Add(JobOrderFields.Posted == true); bucket.PredicateExpression.Add(JobOrderFields.Finished == true); bucket.PredicateExpression.Add(JobOrderFields.Closed == false); System.Data.DataTable table = queryManager.GetQueryResult(fields, bucket, null, null, true, false);
5.3 WHERE 子句与条件表达式
IRelationPredicateBucket类型是SQL中WHERE语句部分的面向对象封装，IRelationPredicateBucket包含条件和关系，IPredicateExpression 只包含条件，IEntityRelation只包含关系。
IPredicateExpression 举例如下，
IPredicateExpression B = ((Table1Fields.Foo == "One") & (Table1Fields.Bar == "Two")) // A | (Table2Fields.Bar2 == "Three");
LLBL Gen Pro官方帮助文件中列举了一个详细的SQL语句与查询类型中的映射关系，摘要如下。
|
SQL 语句 |
Predicate派生类型 |
|
Field BETWEEN 3 AND 5 |
|
|
Field = Field2
|
|
|
Field Is NULL |
|
|
Field IN (1, 2, 3, 5) |
|
|
Field IN (
|
|
|
Field = 3
|
|
|
Field LIKE "Foo%" |
再来看一下关系，如果是在数据库中有建立表之间的主从关系，则可直接用LLBL Gen Pro生成的关系类型，比如一个客户可对应多个联系地址，客户与联系地址是一对多的关系：
CustomerEntity.Relations.CustomerContactEntityUsingCustomerNo
如果没有在数据库中建立关系，则需要通过IEntityRelation类型创建关系，参看下面的例子代码。
IEntityRelation entityRelation = new EntityRelation(JobOrderMaterialLedgerFields.JobNo, JobOrderMaterialFields.JobNo, RelationType.OneToMany);
5.4 数据排序 ORDER BY
ISortExpression接口用于封装排序相关的操作，参考下面的例子。
ISortExpression sortExpression = new SortExpression();
sortExpression.Add(SalesOrderFields.OrderNo | SortOperator.Ascending);
支持多个字段排序，排序优先级与排序字段的增加顺序有关。
5.5 数据分组 GROUP BY
IGroupByCollection接口封装分组相关的操作。来看一个例子代码，创建分组接口的实例。
IGroupByCollection groupBy = new GroupByCollection(SalesOrderFields.OrderNo);
支持多个字段分组，可以借用下面的代码来创建多个字段分组的查询。
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(JobOrderFields.JobNo);
groupByClause.Add(JobOrderFields.BomNo);
5.6 聚合函数 Aggregate functions
LLBL Gen Pro帮助文档中有一篇《Generated code - Field expressions and aggregates》是专门讲解聚合函数的，参考下面简单的例子。
ResultsetFields fields = new ResultsetFields(2); fields.DefineField(CustomerFieldIndex.Country, 0, "Country"); fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers"); fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;
也可以直接读取数据，求一个聚合函数的返回值，例子代码如下。
DataAccessAdapter adapter = new DataAccessAdapter(); decimal orderPrice = (decimal)adapter.GetScalar(OrderDetailsFields.OrderId, (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum, (OrderDetailsFIelds.OrderId == 10254));
5.7 子查询 Subquery
先看SQL语句，读取客户编号和它的订单数量。
SELECT CustomerID, ( SELECT COUNT(*) FROM Orders WHERE CustomerID = Customers.CustomerID ) AS NumberOfOrders FROM Customers
用LLBL Gen Pro Adapter模式实现，代码如下：
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);