Properties

This section is about properties: key properties, unique properties, collection key properties and computed properties.

Each entity is defined by a set of properties, by default properties are persistent but you can declare computed properties whose values are dynamically computed on the base of other properties values.

Most of the time, you have an entity with one key property and many other secondary properties, among those secondary properties you can declare unique properties that are unique in the Persistence layer and collection key properties that compose an indexer for the entity set in the Business Object Model layer.

Property Type

Each property has a name and a type, for example you can declare an Employee entity with two properties Id (of type int) and Name (of type string). The property type is a CodeModeler type, associated with the underlying target type systems:

• the .NET type, based on the Common Language Runtime type system,

• the Persistence type based on the .NET standard System.Data.DbType enumeration,

• any other type supported by custom producers or generators.

Regarding key properties, CodeModeler supports the following types:

• guid

• string

• int

• short

• long

• uint

• ushort

• ulong

• enumeration type (only as part of a composite key)

• entity type (this is especially useful for defining an association entity, using two entity keys).

The following image demonstrates an Employee entity with the following properties:

• Id key property with the System.Int32 .NET type (and by default the DbType.Int32 persistence type)

• FirstName and LastName properties with the System.String .NET type (and by default the DbType.String persistence type)

• BirthDate property with the System.DateTime .NET type (and by default the DbType.DateTime persistence type)

• RestDay property with the System.DayOfWeek .NET type (and by default the DbType.Int32 persistence type).

Key Properties

To set the key attribute on properties:

• to declare a single primary key on an entity, set the “Is Key” attribute on one of its properties

• to declare a composite primary key on an entity, set the “Is Key” attribute on several of its properties.

If you don't explicitly set the key attribute on any of an entity property, the first property whose type is key compatible will have the key attribute set implicitly.

EntityKey in the Business Object Model

Every .NET object generated by the CodeDom Producer will have the set of keys defined as properties of the class with their corresponding type in the .NET typespace, as all other properties, but will also have a property named EntityKey, of System.String .NET type, added.

If the entity key set has only one key property of the string type, the EntityKey value will be defined as this property, otherwise, it will be a concatenation of the values of all the key properties. The character used for concatenation is, by default, the '|' character.

The CodeModeler object model has two .NET utilities methods for dealing with entity keys: CodeModeler.Runtime.CodeModelerPersistence.BuildEntityKey and CodeModeler.Runtime.CodeModelerPersistence.ParseEntityKey.

This entity key is especially useful when dealing with UI element such as Combo Boxes or List Boxes, in User Interface applications, that require a unique value as the key to their items list.

Nullable Properties

CodeModeler considers nullability independently from layers. A property can be nullable in the database and not nullable in .NET and vice versa. When a property is not nullable in .NET (for example, it’s of int type) but nullable in the database it will use its default value to represent the database null value (for example, 0 by default for int type).

To declare a nullable property:

• in the Persistence layer with the “Is Persistence Nullable” attribute

• in the Business Object Model layer with the “Is Model Nullable” attribute.

Note: The “Is Model Nullable” attribute will only have effect on value types such as int, float, etc. It is not applicable to reference types like string that are nullable by default.

You can use the “Auto Model Nullable Properties” attribute (“Advanced Properties” tab) at project level, so that all (value type) properties declared in this project are automatically model nullable.

Unique Properties

To set the unique and/or constraint names attributes on properties:

• to declare a single unique constraint on an entity, set the “Is Unique” attribute on one of its properties

• to declare a composite unique constraint on an entity, set the “Unique Constraint Names” (“Advanced Properties” tab) attribute on several of its properties.

Note: The “Unique Constraint Names” attribute value is a free form field and can contain a list of comma-separated constraint values applied to the property.

Sortable Properties

Sortable properties are used to sort entities. In the persistence layer, sortable properties influence the ORDER BY clause of stored procedures generated for Load and Search methods.

By default, all properties are non-sortable. You can set the “Is Sortable” attribute to True to set a property as being sortable:

By setting the “Default Sortable” attribute value of an entity, you are setting to the default value on the sortable attribute for all its properties.

For example, let’s consider the following entity, with a FirstName and LastName defined as sortable and a Search method defined:

Here is the SQL Server output for the LoadAll and SearchAll stored procedures. As you can see the SQL Server producer generates an ORDER BY clause that compares the @_orderBy0 procedure argument with '[Employee].[FirstName]' and with '[Employee].[LastName]' but not with '[Employee].[Id]'.

Note that the @_orderByDirection0 procedure argument is always compared with 0 or with 1 for ascending or descending order.

CREATE PROCEDURE [Commerce].[Employee_LoadAll]
(
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
SELECT [Commerce].[Employee].[Employee_Id], [Commerce].[Employee].[Employee_FirstName], [Commerce].[Employee].[Employee_LastName], [Commerce].[Employee].[_trackLastWriteTime], [Commerce].[Employee].[_trackCreationTime], [Commerce].[Employee].[_trackLastWriteUser], [Commerce].[Employee].[_trackCreationUser], [Commerce].[Employee].[_rowVersion] 
    FROM [Commerce].[Employee] 
    ORDER BY CASE
        WHEN @_orderBy0 = '[Employee].[LastName]' AND @_orderByDirection0 = 0 THEN [Commerce].[Employee].[Employee_LastName]
    END ASC,
    CASE
        WHEN @_orderBy0 = '[Employee].[LastName]' AND @_orderByDirection0 = 1 THEN [Commerce].[Employee].[Employee_LastName]
    END DESC,
    CASE
        WHEN @_orderBy0 = '[Employee].[FirstName]' AND @_orderByDirection0 = 0 THEN [Commerce].[Employee].[Employee_FirstName]
    END ASC,
    CASE
        WHEN @_orderBy0 = '[Employee].[FirstName]' AND @_orderByDirection0 = 1 THEN [Commerce].[Employee].[Employee_FirstName]
    END DESC
 
CREATE PROCEDURE [Commerce].[Employee_SearchAll]
(
 @Id [int] = NULL,
 @FirstName [nvarchar] (256) = NULL,
 @LastName [nvarchar] (256) = NULL,
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max), @paramlist nvarchar(max)
 
SELECT @sql=
'SELECT DISTINCT [Commerce].[Employee].[Employee_Id], [Commerce].[Employee].[Employee_FirstName], [Commerce].[Employee].[Employee_LastName], [Commerce].[Employee].[_trackLastWriteTime], [Commerce].[Employee].[_trackCreationTime], [Commerce].[Employee].[_trackLastWriteUser], [Commerce].[Employee].[_trackCreationUser], [Commerce].[Employee].[_rowVersion] 
    FROM [Commerce].[Employee] 
    WHERE ((1 = 1) AND (1 = 1))'
SELECT @paramlist = '@Id int, 
    @FirstName nvarchar (256), 
    @LastName nvarchar (256), 
    @_orderBy0 nvarchar (64), 
    @_orderByDirection0 bit'
IF @Id IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_Id] = @Id))'
IF @FirstName IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_FirstName] = @FirstName))'
IF @LastName IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_LastName] = @LastName))'
DECLARE @orderbyadded int
SELECT @orderbyadded = 0
IF @_orderBy0 = '[Employee].[LastName]'
BEGIN
 IF @orderbyadded = 0
     SELECT @sql = @sql + ' ORDER BY '
 IF @orderbyadded > 0
     SELECT @sql = @sql + ', '
 SELECT @orderbyadded = @orderbyadded + 1
 SELECT @sql = @sql + '[Employee].[Employee_LastName] '
 IF @_orderByDirection0 = 1
     SELECT @sql = @sql + 'DESC '
 ELSE
     SELECT @sql = @sql + 'ASC '
END
IF @_orderBy0 = '[Employee].[FirstName]'
BEGIN
 IF @orderbyadded = 0
     SELECT @sql = @sql + ' ORDER BY '
 IF @orderbyadded > 0
     SELECT @sql = @sql + ', '
 SELECT @orderbyadded = @orderbyadded + 1
 SELECT @sql = @sql + '[Employee].[Employee_FirstName] '
 IF @_orderByDirection0 = 1
     SELECT @sql = @sql + 'DESC '
 ELSE
     SELECT @sql = @sql + 'ASC '
END
EXEC sp_executesql @sql, @paramlist,
    @Id, 
    @FirstName, 
    @LastName, 
    @_orderBy0, 
    @_orderByDirection0
 
RETURN

By default, the SQL Server producer has generated one @_orderBy0 and one @_orderByDirection0 stored procedure arguments to sort entities by one property. If you need to sort entities by N properties, set the “Persistence Order By Parameters Count” attribute value of a method to N and the producer will generate the @_orderBy0, @_orderBy1, @_orderBy2, ....., @_orderByN-1 stored procedure arguments and also the necessary SQL code in the stored procedure body.

And here is the BOM output:

public static System.Data.IDataReader PageDataLoadAll(CodeModeler.Runtime.PageOptions pageOptions)
{
    CodeModeler.Runtime.CodeModelerPersistence persistence = CodeModelerContext.Get(Commerce.Constants.CommerceStoreName).Persistence;
    persistence.CreateStoredProcedureCommand("Commerce", "Employee", "LoadAll");
    if ((pageOptions != null))
    {
        System.Collections.IEnumerator enumerator = pageOptions.OrderByArguments.GetEnumerator();
        bool b;
        int index = 0;
        for (b = enumerator.MoveNext(); b; b = enumerator.MoveNext())
        {
            CodeModeler.Runtime.OrderByArgument argument = ((CodeModeler.Runtime.OrderByArgument)(enumerator.Current));
            persistence.AddParameter(string.Format("@_orderBy{0}", index), argument.Name);
            persistence.AddParameter(string.Format("@_orderByDirection{0}", index), ((int)(argument.Direction)));
            index = (index + 1);
        }
    }
    System.Data.IDataReader reader = CodeModelerContext.Get(Commerce.Constants.CommerceStoreName).Persistence.ExecuteReader();
    return reader;
}
...
public static System.Data.IDataReader PageDataSearchAll(CodeModeler.Runtime.PageOptions pageOptions, int id, string firstName, string lastName)
{
    CodeModeler.Runtime.CodeModelerPersistence persistence = CodeModelerContext.Get(Commerce.Constants.CommerceStoreName).Persistence;
    persistence.CreateStoredProcedureCommand("Commerce", "Employee", "SearchAll");
    persistence.AddParameter("@Id", id, ((int)(-1)));
    persistence.AddParameter("@FirstName", firstName, default(string));
    persistence.AddParameter("@LastName", lastName, default(string));
    if ((pageOptions != null))
    {
        System.Collections.IEnumerator enumerator = pageOptions.OrderByArguments.GetEnumerator();
        bool b;
        int index = 0;
        for (b = enumerator.MoveNext(); b; b = enumerator.MoveNext())
        {
            CodeModeler.Runtime.OrderByArgument argument = ((CodeModeler.Runtime.OrderByArgument)(enumerator.Current));
            persistence.AddParameter(string.Format("@_orderBy{0}", index), argument.Name);
            persistence.AddParameter(string.Format("@_orderByDirection{0}", index), ((int)(argument.Direction)));
            index = (index + 1);
        }
    }
    System.Data.IDataReader reader = CodeModelerContext.Get(Commerce.Constants.CommerceStoreName).Persistence.ExecuteReader();
    return reader;
}

In the PageDataLoadAll and PageDataSearchAll methods of the EmployeeCollection class, the Business Object Model producer generates the code that add the necessary @_orderByN and @_orderByDirectionN arguments to the Employee_LoadAll stored procedure by parsing the OrderByArguments field of the pageOptions method parameter:

Searchable Properties

Searchable properties are used in the filter criteria when searching for entities in the persistence layer, you retrieve searchable properties in the arguments of stored procedures generated automatically for Search methods without explicit parameters.

By default, properties of an entity are searchable if they are persistent and if their type is a scalar one (like int, string, etc.). You can change that using the “Is Searchable” attribute of the Visual Studio property grid:

For example, let’s define this entity:

By default, Age, FirstName and LastName are searchable, and we have defined manually that Id is not. This is the SQL Server output for the SearchAll method:

CREATE PROCEDURE [Commerce].[Employee_SearchAll]
(
 @FirstName [nvarchar] (256) = NULL,
 @LastName [nvarchar] (256) = NULL,
 @Age [int] = NULL,
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max), @paramlist nvarchar(max)
 
SELECT @sql=
'SELECT DISTINCT [Commerce].[Employee].[Employee_Id], [Commerce].[Employee].[Employee_FirstName], [Commerce].[Employee].[Employee_LastName], [Commerce].[Employee].[Employee_Age], [Commerce].[Employee].[_trackLastWriteTime], [Commerce].[Employee].[_trackCreationTime], [Commerce].[Employee].[_trackLastWriteUser], [Commerce].[Employee].[_trackCreationUser], [Commerce].[Employee].[_rowVersion] 
    FROM [Commerce].[Employee] 
    WHERE ((1 = 1) AND (1 = 1))'
SELECT @paramlist = '@FirstName nvarchar (256), 
    @LastName nvarchar (256), 
    @Age int, 
    @_orderBy0 nvarchar (64), 
    @_orderByDirection0 bit'
IF @FirstName IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_FirstName] = @FirstName))'
IF @LastName IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_LastName] = @LastName))'
IF @Age IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_Age] = @Age))'
EXEC sp_executesql @sql, @paramlist,
    @FirstName, 
    @LastName, 
    @Age, 
    @_orderBy0, 
    @_orderByDirection0

Now, we can also use set the “Is Auto Starts With” attribute (“Advanced Properties” tab) on the method:

And here is the modified SQL Server Output:

CREATE PROCEDURE [Commerce].[Employee_SearchAll]
(
 @FirstName [nvarchar] (256) = NULL,
 @LastName [nvarchar] (256) = NULL,
 @Age [int] = NULL,
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max), @paramlist nvarchar(max)
 
SELECT @sql=
'SELECT DISTINCT [Commerce].[Employee].[Employee_Id], [Commerce].[Employee].[Employee_FirstName], [Commerce].[Employee].[Employee_LastName], [Commerce].[Employee].[Employee_Age], [Commerce].[Employee].[_trackLastWriteTime], [Commerce].[Employee].[_trackCreationTime], [Commerce].[Employee].[_trackLastWriteUser], [Commerce].[Employee].[_trackCreationUser], [Commerce].[Employee].[_rowVersion] 
    FROM [Commerce].[Employee] 
    WHERE ((1 = 1) AND (1 = 1))'
SELECT @paramlist = '@FirstName nvarchar (256), 
    @LastName nvarchar (256), 
    @Age int, 
    @_orderBy0 nvarchar (64), 
    @_orderByDirection0 bit'
IF @FirstName IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_FirstName] LIKE (@FirstName + ''%'')))'
IF @LastName IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_LastName] LIKE (@LastName + ''%'')))'
IF @Age IS NOT NULL
    SELECT @sql = @sql + ' AND (([Commerce].[Employee].[Employee_Age] = @Age))'
EXEC sp_executesql @sql, @paramlist,
    @FirstName, 
    @LastName, 
    @Age, 
    @_orderBy0, 
    @_orderByDirection0

As you can see, a LIKE % operation has automatically been added on all columns that can handle it.

Collection Key Properties

When you set the “Is Collection Key” attribute on a given property XXX, the generated layers are impacted:

• a unique constraint and a stored procedure LoadByXXX are added in the Persistence Layer

• a method LoadByXXX and a .NET indexer this[XXX] are added in the Business Layer.

You can set this attribute on a property only if the parent entity has the “Collection Type” attribute set to Collection or ListCollection.

For example, let’s define the following entity:

With the “Is Collection Key” set to True for the EmailAddress property:

Then, the SQL Server will create this constraint and stored procedure:

ALTER TABLE [Commerce].[Customer] ADD CONSTRAINT [IX_Cus_Cum_Cus] UNIQUE NONCLUSTERED ([Customer_EmailAddress] ASC)
...
CREATE PROCEDURE [Commerce].[Customer_LoadByEmailAddress]
(
 @EmailAddress [nvarchar] (256)
)
AS
SET NOCOUNT ON
SELECT DISTINCT [Commerce].[Customer].[Customer_Id], [Commerce].[Customer].[Customer_FirstName], [Commerce].[Customer].[Customer_LastName], [Commerce].[Customer].[Customer_EmailAddress], [Commerce].[Customer].[_trackLastWriteTime], [Commerce].[Customer].[_trackCreationTime], [Commerce].[Customer].[_trackLastWriteUser], [Commerce].[Customer].[_trackCreationUser], [Commerce].[Customer].[_rowVersion] 
    FROM [Commerce].[Customer] 
    WHERE ([Commerce].[Customer].[Customer_EmailAddress] = @EmailAddress)

And the BOM producer will output this:

// Customer methods
public static bool Delete(Customer customer);
public static bool Insert(Customer customer);
public static Customer Load(int id);
public static Customer LoadByEMailAddress(string eMailAddress);
public static bool Save(Customer customer);
 
// CustomerCollection properties
public Customer this[int index];
public Customer this[string eMailAddress];

Computed Properties

This topic explains how to set computed attribute for a property.

When you set the “Is Computed” attribute on a property, the following events and attributes are automatically affected:

• “Is persistent” attribute is set to False as the property is not persisted in the data layer.

• Relations are removed.

• An OnGet rule will be added to the property. The property must be computed by some piece of code.

The following image shows a Customer entity with a computed Name property:

An OnGetName snippet method has been added that will be added at generation time that compute the property at runtime.

Display Properties

Every entity has a display property that is only useful for UI purposes, the display property defines the text representation of an entity in UI applications.

By default, the display property of an entity is the property with the “Is Collection Key” attribute if this property is of string type, or the first property of string type. However, you can set the “Is Entity Display” attribute on any property:

Note: The display property is also used to display instances in the entity shape. See the Instance Editor and Grid chapter for more on that.

Property Default Values

Each property has two default values:

• The default value, used by default in all layers

• The default value in the Persistence layer, which is the same as the common default value, by default.

To set a property’s default value, set the “Default Value” attribute. If you want to specify a default value for the persistence layer, use the “Persistence Default Value” attribute (“Advanced Properties” tab).

Note: The default value can also be used as the value in .NET that will correspond to null in a database for a non nullable type. For example, 0 is, by default, the default value for an int type property read from a null database record.