Skip to main content

Introducing Formengine - The New Formbuilder, try for FREE formengine.io.

Using ORM

DWKit uses its own ORM. Here, we will take a more detailed look at its program API on server.

Metadata​

In DWKit ORM, it is crucial to have an object of the EntityModel type to make database requests for data change and selection. There are three ways to get this object:

  • By the name of Data Model object:

    EntityModel model = await MetadataToModelConverter.GetEntityModelByModelAsync("ModelName", 1);

    Absolutely all attributes are moved from DataModel to EntityModel. The second function parameter specifies depth to which a model will be assembled by references. For example, if this parameter equals:

    • 0 - only attributes, located directly in Data Model, will appear in EntityModel.
    • 1 - if there are attributes with Type = Reference in DataModel, attributes of the entities (another referenced Data Models), which DataModel attributes refer to, are included into EntityModel.
    • 2 and more - the larger the value, the greater the depth of the models included by reference.

  • by Form name:

    EntityModel model = MetadataToModelConverter.GetEntityModelByFormAsync("FormName").Result;

    In this case information from Form and data mapping is used to form EntityModel. Those attributes that have Load checkbox checked, are moved to EntityModel from DataModel specified in the form just like Main Entity in the Mapping data model section of the admin interface. Depth by references (joins) is determined by the same Load attribute. Besides, the so-called Collections are also included in the EntityModel. In general, Collections represent the EntityModel plus filter that selects records based on the parent record. EntityModel for a collection is obtained from the information in the Mapping data model section of the Collections subsection. Attributes for the EntityModel collections are selected based on the Load attribute. An example of a collection - main EntityModel - invoice and the collection - invoice breakdown that is tied to it. Learn more about data binding to form mechanism in action here.

  • Metadata can also be obtained from the DbObject<T> inheritor. We cover this topic further in this section.

The EntityModel class contains the following information:

  • model.Name - name of a model.
  • model.SchemaName - name of the scheme containing the main table of a model.
  • model.TableName - name of the main table of a model.
  • model.PlainAttributes - attributes located in the main table of a model. They are selected without joins, and their changes are tracked.
  • model.JoinedAttributes - requested using joins by references, updates are neither tracked nor applied.
  • model.Attributes - all attributes combined.
  • model.Collections - a list of collections bound to model, which are requested and changed together with the model. Changes take place in the transaction.
  • model.PrimaryKeyAttribute - a primary key attribute.
  • model.ExtensionsContainerAttribute - attribute which stores extension attributes.

Attribute is represented by the AttributeModel class and contains the following information:

  • attribute.Name - name of an attribute.
  • attribute.ColumnName - name of a column.
  • attribute.PropertyName - name of a property in the resulting data object.
  • attribute.Type - an attribute type, an object that can transform types. Is an AttributeType object, which contains information on attribute type, its nullability, and additional methods of type casting and parsing strings.
  • attribute.IsReference - determines whether an attribute is a reference to another Data model.
  • attribute.ReferencedDataModel - data model (name + scheme + table name), which the attribute refer to.
  • attribute.Children - child attributes, i.e. attributes from ReferencedDataModel.
  • attribute.IsCalculated - if true - this attribute is equal to a calculated column in the database.
  • attribute.IsExtension - if true - this attribute is an extension attribute.

This collection is described by the CollectionModel class and contains the following information:

  • collection.Name - name that will be the name of a property in the resulting object the collection is bonded to.
  • collection.Model - EntityModel which describes collection items model.
  • Filter collection.GetCollectionFilter(List<DynamicEntity> parentEntities) - builds a filter which is used when requesting collection data from database.
  • List<DynamicEntity> collection.GetCollection(DynamicEntity parentEntity, List<DynamicEntity> collectionEntities) - selects records related to the passed parentEntity.

Getting data via ORM and DynamicEntity​

If you have EntityModel, you can use it to request data from the database. Requesting all data without a filter and sorting will look like this:

List<DynamicEntity> data = await model.GetAsync(Filter.Empty);
List<DynamicEntity> data = await DynamicRepository.GetAsync(model, Filter.Empty);

When requesting data, you can specify Filter, Order and Paging.

Filter​

An object filter. You can create complex nested filters, using Filter class. First, specify which operator (AND or OR) combines conditions in the root expression of the filter:

var filter = Filter.And;
var filter = Filter.Or;

Then specify a certain number of conditions - predicates and, probably, nested filters:

var filter = Filter.And.Equal(10, "Amount").LikeRightLeft("Document", "Name");

This expression represents the following filter Amount = 10 AND Name LIKE ('%Document%').

You can create any nested filters, for example ... AND (... OR (... AND ...) OR ...) AND .... To do that, use the following methods:

  • NestOr() - starts a nested filter which conditions are connected by OR operator.
  • NestAnd() - starts a nested filter which conditions are joined by AND operator.
  • Parent() - returns to parent filter to which you can add new conditions.
var filter = Filter.And.Equal(10, "Amount").NestOr()
    .LikeRightLeft("Document", "Name").LikeRightLeft("Deed", "Name")
    .Parent().GreaterOrEqual(DateTime.Today, "Date");

You are going to get the following filter Amount = 10 AND (Name LIKE ('%Document%') OR Name LIKE ('%Deed%')) AND Date >= '1982-11-05'.

Filters can be merged, using Merge function:

var filter = Filter.And.Equal(10, "Amount");
var filter1 = Filter.And.LikeRightLeft("Document", "Name");
filter = filter.Merge(filter1);

You are going to get the same expression as in the first case Amount = 10 AND Name LIKE ('%Document%').

To represent the NULL value, use Null.Value or null, for example:

var filter = Filter.And.Equal(Null.Value, "Amount");
var filter = Filter.And.Equal(null, "Amount");

Correspondence of the filter functions to SQL expressions:

FunctionExpression
f.LikeRight(v, n)n LIKE ('v%')
f.LikeLeft(v, n)n LIKE ('%v')
f.LikeRightLeft(v, n)n LIKE ('%v%')
f.NotLikeRight(v, n)n NOT LIKE ('v%')
f.NotLikeLeft(v, n)n NOT LIKE ('%v')
f.NotLikeRightLeft(v, n)n NOT LIKE ('%v%')
f.Equal(v, n)n = v
f.NotEqual(v, n)n <> v
f.Greater(v, n)n > v
f.Less(v, n)n < v
f.GreaterOrEqual(v, n)n >= v
f.LessOrEqual(v, n)n <= v
f.In(List<> {v1, v2, ...}, n)n IN (v1, v2, ...)
f.NotIn(List<> {v1, v2, ...}, n)n NOT IN (v1, v2, ...)
f.Custom(subquery, parameters)user's parameterized subquery

Order​

Sets database sorting order in the database.

var order1 = Order.StartAsc("Name").Desc("Amount");
var order2 = Order.StartDesc("Amount").Asc("Name");

Both expressions will give us a Name ASC, Amount DESC sorting.

Pagination​

Sets data page for the selection:

var skip = 100;
var take = 10;
var paging = Paging.Create(skip, take);

Skips 100 records and returns 10 records.

DynamicEntity​

You can use Filter, Order and Paging in a request. For example:

List<DynamicEntity> data = await model.GetAsync(filter, order, paging);

At the same time, data corresponds to List<DynamicEntity>. DynamicEntity is a transformed ORM record with specified properties. You can get and change property values via indexers:

List<DynamicEntity> data = await model.GetAsync(filter, order, paging);
DynamicEntity document = data.First();
var name = document["Name"];
var amount = document["Amount"];
document["Amount"] = amount + 10;
var id = document.GetId();

Or you can cast DynamicEntity to dynamic and manipulate data dynamically:

List<DynamicEntity> data = await model.GetAsync(filter, order, paging);
dynamic document = data.First() as dynamic;
var name = document.Name;
document.Amount = document.Amount + 10;

Usually, choice of DynamicEntity type depends on a particular task.

Changing data (insert, update, delete)​

As in the case of data requesting, you need EntityModel to change data. Two options are available. First, you can use EntityModel extension methods or DynamicRepository. For example, you receive a JSON with the changed form data from the client, and you need to perform insert or update operations for the main record and insert, update or delete operations for the collections. Let's assume that you receive the data as a request.Data JSON string:

var model = await MetadataToModelConverter.GetEntityModelByFormAsync("FormName");
var data = new DynamicEntityDeserializer(model).DeserializeSingle(request.Data, resetIdsFromClient: true, initMissingFields: false);
await model.UpdateSingleAsync(data);

This means that you first get EntityModel for the form, then deserialize JSON into DynamicEntity and call the UpdateSingleAsync method that updates data in the database itself. Besides, in case there are any collections in data, they will be updated together with the main record.

You should use another method to delete the main record. You receive the IDs of the records to be deleted in request.Data, and even in case you receive records as a whole, you can get IDs from them.

var model = await MetadataToModelConverter.GetEntityModelByFormAsync("FormName");
var ids = JsonConvert.DeserializeObject<List<object>>(request.Data).Select(c => model.PrimaryKeyAttribute.Type.ParseToCLRType(c));
await model.DeleteAsync(ids);

Second, you can request data from the database and get them as a special ObservableEntityContainer object that not only contains data, but also tracks changes in data, and sends them to the database.

var container = await DynamicRepository.GetObservableEntitiesAsync(model, filter);
IEnumeralble<DynamicEntity> data = container.Entities;
var document = data.First() as dynamic;
document.Amount = document.Amount + 10;
await container.ApplyAsync();

The container tracks changes of DynamicEntity located in it. You can also add new DynamicEntity and delete the existing ones. All the changes will be applied and saved in the database. Adding a new record:

var newEntity = await model.NewAsync();
newEntity.Name = "New";
container.Merge(new List<DynamicEntity>{newEntity});
await container.ApplyAsync();

Deleting records:

IEnumeralble<DynamicEntity> data = container.Entities;
var document = data.First() as dynamic;
container.Remove(new List<DynamicEntity>{document});
await container.ApplyAsync();

You can also change records using the Merge function.

List<DynamicEntity> externalData = ...;
var filter = Filter.Empty;
foreach (DynamicEntity en in externalData)
{
    if (en.HasPrimaryKey) // it was a record from the database, need to update
    {
        filter.Equal(en.GetPrimaryKey(), idProperty);
    }
    else // it is new record, need to insert
    {}
}

var container = await DynamicRepository.GetObservableEntitiesAsync(model, filter); // getting existing records from the database
container.Merge(externalData);
await container.ApplyAsync(); // the records will be inserted and updated

Typed ORM objects​

DynamicEntity is a not strongly typed and dynamic object. Using it, you can write shorter generalized algorithms of business logic. However, in case there are multiple unique algorithms for a certain object, using DynamicEntity, you will have to write a lot of repetitious noisy code. In DWKit ORM, you can inherit from a special DbObject<T> class and obtain all ORM functions as a typed object. First you should declare a class:

public class TypedOrmEntity : DbObject<TypedOrmEntity>
{
    [DbObjectModel(IsKey = true)]
    public Guid Id
    {
        get => _entity.Id;
        set => _entity.Id = value;
    }
    [DbObjectModel]
    public byte Type
    {
        get => _entity.Type;
        set => _entity.Type = value;
    }
    [DbObjectModel]
    public string Name
    {
        get => _entity.Name;
        set => _entity.Name = value;
        }
}

Here you can see a declared entity stored in the TypedOrmEntity table with the Id, Name and Type columns. These class and table in the database are all you need to request, change, insert and delete data. As you can notice, the TypedOrmEntity class is a wrapper over DynamicEntity, that's why you have to read and update properties in the inner _entity object. However, DbObject<T> also contains EntityModel and ObservableEntityContainer. All properties uploaded from the database should be marked with the DbObjectModel attribute and should transmit changes to the inner _entity object. Here's an example of working with data:

var entity1 = new TypedOrmEntity() {Id = Guid.NewGuid(), Name = "Entity1", Type = 1};
var entity2 = new TypedOrmEntity() {Id = Guid.NewGuid(), Name = "Entity2", Type = 2};
await TypedOrmEntity.ApplyAsync(entity1, entity2); // two records will be inserted
entity1.Type = 3;
await TypedOrmEntity.ApplyAsync(entity1); // one record will be updated
await TypedOrmEntity.DeleteAsync(entity1, entity2); // two records will be deleted

An example of requesting data:

var data = await TypedOrmEntity.SelectAsync(filter, order);

Refer to the previous section to find out how to build filter and order. In case you want to create another typed entity referring to the TypedOrmEntity and include an attribute from the TypedOrmEntity in it, you should also do it using the attributes.

public class AnotherTypedOrmEntity : DbObject<AnotherTypedOrmEntity>
{
    [DbObjectModel(TableType = typeof(TypedOrmEntity), ParentPropertyName = nameof(TypedOrmEntityId), ColumnName = "Name")]
    public string TypedOrmEntityName
    {
        get => _entity.TypedOrmEntityName;
        set => _entity.TypedOrmEntityName = value;
    }
    [DbObjectModel]
    public byte Type
    {
        get => _entity.Type;
        set => _entity.Type = value;
    }
    [DbObjectModel(IsKey = true)]
    public Guid Id
    {
        get => _entity.Id;
        set => _entity.Id = value;
    }
    [DbObjectModel]
    public string Name
    {
        get => _entity.Name;
        set => _entity.Name = value;
    }
    [DbObjectModel(ColumnName = "OrmEntityId")] // use column name if property names differ from the column names
    public Guid TypedOrmEntityId
    {
        get => _entity.TypedOrmEntityId;
        set => _entity.TypedOrmEntityId = value;
    }
}

In this case, DWKit ORM will build a join between the objects itself.

  • [DbObjectModel(TableType = typeof(TypedOrmEntity), ParentPropertyName = nameof(TypedOrmEntityId), ColumnName = "Name")] - sets a join by the TypedOrmEntity.Id = TypedOrmEntityName.OrmEntityId columns.
  • [DbObjectModel(ColumnName = "OrmEntityId")] - in case the ColumnName is specified, it is not necessary to name a property a same name with a column.
warning

If you received a typed ORM object from the database and want to change it, you need to call the StartTracking() method before change this object:

var entity = (await TypedOrmEntity.SelectAsync(filter)).FirstOrDefault();
if (entity != null)
{
    entity.StartTracking();
    entity.Type = 100;
    await entity.ApplyAsync();
}

DWKit ORM and transactions​

In DWKit ORM, you can open transactions implicitly in the using block, and all nested operations will be performed in this transaction. It is somewhat similar to TransactionScope, however, it does not use the distributed transactions mechanism.

using (var shared = new SharedTransaction())
{
    await shared.BeginTransactionAsync();
    await TypedOrmEntity.ApplyAsync(entity1, entity2);
    await AnotherTypedOrmEntity.ApplyAsync(anotherEntity1, anotherEntity2);
    shared.Commit();
}

In case when the exception will be thrown in the using block, the transaction will rollback itself. You should call Commit explicitly.

It is OK if one transaction will be opened inside another one.

using (var shared1 = new SharedTransaction())
{
    await shared1.BeginTransactionAsync();
    ....
    using (var shared2 = new SharedTransaction())
    {
        await shared2.BeginTransactionAsync();
        ...
        shared2.Commit();
    }
    ....
    shared1.Commit();
}

In case shared1 was opened and started, shared2 won't be opened and started, and all expressions inside the second using will use shared1. Thus, you can use SharedTransaction regardless of whether the transaction has opened earlier or not.

info

However, you should be careful because a transaction cannot be created again, and database management systems cannot increase (or decrease) the transaction isolation level after a transaction is being opened. The isolation level inside a nested transaction will remain the same as inside an external one.

You can change the level of isolation in the parameters of the BeginTransactionAsync method.

await shared.BeginTransactionAsync(IsolationLevel.ReadCommitted);