Entity Model

The Entity Model is an Object / Relational Mapping framework that enables you to map Microsoft SQL database schemes by using MatchPoint configurations. With the Entity Model you can build user front-ends for databases. A database is represented by a Model Configuration file that contains the various database elements such as Tables, Relations, Columns, Views, etc. You can then use this Model Configuration in MatchPoint components to access the database. The big advantage is, that you don't have to write SQL commands, since these are all generated by the Entity Model according to the Model Configuration.

The following mechanisms can be used to access a configured entity model:

  • The Model Data Provider can be used to aggregate entities from a model configuration
  • A Model Data Adapter can be used to view, edit or create an entity within a Form Web Part
  • Within a Data Grid Web Part, a ModelLookupColumn displays values that are aggregated through a relation between entities
  • A Model Choice Provider can be used as a ChoiceFieldProvider to view/edit the value of an entity relation within a Form- or Workspace Editor Web Part
  • Within the Refinement Web Part, a ModelLookupRefinementColumn allows refining search results based on the values of an entity relation
  • The Models expression variable can be used to access entity model configurations within MatchPoint expressions
  • On a SharePoint list or -document library, a MatchPoint Model Lookup Field can be added to save values from an entity model configuration

The Entity Model also allows you to specify dynamic permissions on entities. You can for instance define that the edit permission is dependent on a column value. See Entity.Permissions Field for more information.

Configuration

An entity model is configured within a ModelConfiguration file. Within a ModelConfiguration, the connection to the external database is specified within the ConnectionString parameter. The configuration does also contain Entities which are the core of a ModelConfiguration.

Entities

An entity in the model configuration represents a table or view in the database schema. Entities are configured using an Entity element within the model configuration. It specifies the following configuration parameters:

  • TableName specifies the table within the database
  • The Name property defines how this entity is accessed

  • The PrimaryKeyColumn property specifies the primary key of the database table

    It is not possible to specify multiple primary key columns with the MatchPoint entity model.

  • The property SqlDisplayNameExpression specifies the display name of the entity. You can define either a single column within the table to be used as the display name (i.e. [Title]), or you can specify an SQL expression to combine values from multiple columns into a display name (i.e. [Title] + ' (' + [ProjectId] + ')'). Please note that column names are referenced within square brackets.

Fields

Within an entity configuration, the Fields element can be used to configure fields which can be used to view/edit field values within a Form Web Part. The following fields are available:

  • BooleanField
  • ChoiceField
  • CompositeField
  • DateField
  • ExpressionField
  • NumberField
  • RichTextField
  • SectionField
  • TextField

Refer to the Form Web Part chapter for further details on the fields.

Forms

TheFormConfiguration element in the Forms property can be used to configure View/Edit/Create forms for entities. Each URL parameter allows specifying a page. This page is opened when the corresponding action is called for an entity:

  • By directly clicking an entity directly from a TitleColumn in a Data Grid Web part
  • By selecting an action from the context menu for an element within a Data Grid Web Part
  • By clicking a value displayed within a ModelLookupColumn

On the specified pages, the controls for viewing/editing/creating a model entity can be implemented using the Form Web Part and a ModelDataAdapter.

Permissions

To determine, which users are allowed to read/edit/delete/create entities, the EntityPermissionConfiguration element in the Permissions property can be used.

Item level permissions do have a significant influence on the performance of loading and displaying data. Permissions are evaluated on the SharePoint frontend server and not on the database.

Aliases

Within an FieldAlias configuration element, alias columns can be specified. Just like within the SqlDisplayNameExpression, columns can be specified with an SQL expression. This allows specifying alternate names for columns, if the database schema does not meet the requirements of the entity model.

Columns specified within an FieldAlias configuration are always included into the SQL query, regardless of whether the corresponding value is actually used within the control or Web Part.

Relations

To specify relations between entities of a model configuration, the Relations element can be used. A relation is always defined in the perspective of the entity ("source entity") where the relation is created There are three possible kinds of relations:

If a relation is configured for an entity, the relation is accessible via its name ...

Usage

Accessing data

The SQL queries for accessing data from a database using the entity model are generated automatically. This works both for reading and writing access, including creating new entities within the database. Within the SQL queries, only columns that are required are included:

  • The primary key column and the specified display name of the entity table.
  • Columns and SQL expressions that are specified by an Alias configuration element within the entity model configuration
  • Any columns that can be extracted from the data provider or data adapter. Some examples:
    • within a Data Grid, any database column referenced within a grid column is added
    • within a Composite, any columns referenced within a DataItem expression are added
    • within a Form Web Part, columns referenced within a field configuration are added

Please note that MatchPoint cannot automatically extract columns from relations. For instance, if a 'Customer' entity is used within a Model Data Provider and a column on an 'Address' relation is accessed, this will throw an exception:

DataItem.Address.AddressLine

This is because the AddressLine column cannot be resolved and is therefore not included into the SQL query. For accessing this column, there are multiple ways:

  • Within a Data Grid, all columns are automatically suggested for selection within a column configuration element
  • If you are accessing a ManyToOneRelation, you can use the following expression (i.e. within a Composite Web Part or a Composite column): DataItem["Address.AddressLine"]
  • For a ManyToManyRelation or OneToManyRelation, you need to specify an Alias for AddressLine on the target entity (Address)

When data from a model configuration is accessed directly from the expression engine (i.e. by using an ExpressionDataProvider), required columns have to be included by using the Select extension method:

 Models.ProductModel.Rows.Select({"ProductName", "ProductNumber"}).Where("ProductId", "=", 12345)

Data Provider

You can access the Entity Model in the Data Grid, Composite and Chart Web Part using the Model Data Provider.

The Data Provider supports views, sorting and conditions like for instance the List Data Provider. The use of conditions enables you to define complex data queries directly in configuration files. The support for conditions allows you to attach a Refinement Web Part via the Expression Condition.

Further the Entity Model allows you to create queries on related tables. Lets assume we have a many to one relation called "Customer" from the table Project to the table Customer. Your task is now to retrieve all projects belonging to a customer with 'Software' in the name. On the Model Data Provider for the entity Project you define a Field Condition as follows:

  • Name: Customer.Name
  • Expression: "Software"
  • Operator: Contains

The entities linked via an n:1 relation are accessible with the dot notation where the left hand part is built by the name of the relation. This is works for conditions, grid columns, refinement columns and sort fields. Please note that you need to apply the dot notation via the indexed property in expressions:

DataItem["Customer.Name"]

Form Web Part

By using a Model Data Adapter you can create a form that can be used to display and update database records. The Model Data Adapter provides the Form Configuration with information about the Model Configuration. For instance all mapped relations in the model will be suggested as field references. You can store files in the database by using the Model Attachments Field.

Expression Engine

You can access all the Model Configurations via the expression variable Models using their configuration name. An expression will return a ModelConfiguration object which provides access to all entities, relations etc. that are specified within the model. Assuming you have a model "PM" with an entity "Project" you can access the projects rows via Models.PM.Project.Rows. The Rows property is of type DbQuery which allows LINQ-To-SQL like queries:

Expression Engine entity model query

Models.PM.Project.Rows
  .Where("Budget", ">", "100000")
  .Select({"Title", "Budget"})
  .OrderBy("Title")

This expression will be translated to the SQL query:

SELECT Title, Budget FROM Project WHERE Budget > 100000

SharePoint

Within SharePoint lists and document libraries, a ModelLookupField can be configured. It allows saving values from the MatchPoint entity model on the list items within that list. The field can be added to a list like any other SharePoint field column. Once created, the field will be used to save two values from the entity model:

  • a "key" value (corresponds to the primary key column configured on the entity model)
  • a "display name" column (corresponds to the configured display name column)

The display name column will be displayed both within the view form of list items and within list views. Values are written on list items by using a Form Web Part with a ListItemDataAdapter. When displaying records from a entity model configuration in a Data Grid Web Part, the Model Lookup Column enables you to display up-to-date data without the need for manual synchronization.

Just like with a standard SharePoint "External Data" field, values from the entity model are not reflected automatically on list items. They are updated when the item itself is updated. However, if a Model Lookup Field is displayed within a Data Grid ModelLookupColumn, changes will be updated immediately.

results matching ""

    No results matching ""