解析大型.NET ERP系统数据访问对象关系映射框架LLBL Gen Pro

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();

<!–
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
–>
<!–
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
–>

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 BETWEEN field2 AND 4	FieldBetweenPredicate
Field = Field2 Field < (Field2 * 4)	FieldCompareExpressionPredicate
Field Is NULL	FieldCompareNullPredicate
Field IN (1, 2, 3, 5)	FieldCompareRangePredicate
Field IN ( SELECT Field FROM Foo WHERE …)	FieldCompareSetPredicate
Field = 3 Field != “Foo”	FieldCompareValuePredicate
Field LIKE “Foo%”	FieldLikePredicate

再来看一下关系，如果是在数据库中有建立表之间的主从关系，则可直接用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);