November Happy Hour will be moved to Thursday December 5th.

Dynamic Data Store

Product version:

EPiServer CMS 6.0

Document version:

1.0

Document last saved:

 

The Dynamic Data Store is a new component offering an API and infrastructure for the saving, loading and searching of both compile time data types (.NET object instances) and runtime data types (property bags). The component is shipped as part of the EPiServer Framework package (first released with EPiServer CMS 6) and is intended to be used by all EPiServer products.

NOTE: This document refers to examples in the Dynamic Data Store download which can be downloaded in project form.

Contents

Overview

The Dynamic Data Store is a new component offering an API and infrastructure for the saving, loading and searching of both compile time data types (.NET object instances) and runtime data types (property bags).

Alternative technologies include Microsoft’s Entity Framework 2.0 and NHibernate for .NET. However, the Dynamic Data Store has been specifically designed with EPiServer CMS and its flexible user driven data in mind.

Assembly and Namespaces

The EPiServer.Data assembly contains the following namespaces:

EPiServer.Data

Contains important classes used by in the Dynamic Data Store, most notably the Identity class.

EPiServer.Data.Configuration

Contains the configuration classes for the Dynamic Data Store.

EPiServer.Data.Dynamic

Contains the DynamicDataStoreFactory and DynamicDataStore classes as well as their support classes and data structures.

EPiServer.Data.Dynamic.Provider

Contains the SqlServerDataStoreProvider, OracleDataStoreProvider as well as their base classes and other database specific classes for LINQ support.

Managing stores

Stores are created, obtained and deleted using the DynamicDataStoreFactory class. The class has a single instance which can be obtained from the static Instance property.

See the UsingStores class in the Dyanamic Data Store download for examples creating, obtaining and deleting stores.

Saving and Loading data

Data can be saved and loaded using compile time data types (.NET classes) and runtime data types via the EPiServer.Data.PropertyBag class. The Dynamic Data Store is divided into logical stores which are identified by name. Stores are not polymorphic which means only one property set may be saved in a store although it is possible to re-map stores and achieve a level of polymorphism though the use of interfaces and template types.

See the LoadSaveType and LoadSavePropertyBag  classes in the Dyanamic Data Store download for examples of loading and saving data.

Searching Data

Searching data in the Dynamic Data Store comes in two flavors:

  • Simple Find method: Find data structures by matching one or more name-value pairs with data in the store
  • LINQ: Find data structures using Microsoft’s Language Integrated Query technology

See the UsingLinq and UsingFind  classes in the Dyanamic Data Store download for examples of searching for data.

 

Under The Covers

The Dynamic Data Store is essentially an Object-Relational mapper. When used with compile time data types (.NET classes), all properties that have a public ‘getter’ and a ‘setter’ (setter does not need to be public)  are mapped to a column in a database table.  For runtime data types, each property added to a PropertyBag is also mapped in the same way.

The Dynamic Data Store uses the ‘big table’ approach to storing data. That is by default, all data types are stored in one database table. This table contains many columns, several of each data type that the Dynamic Data Store supports.

When a data structure is saved, the .NET CLR type of each property is mapped against an internal list of supported types. There are 3 types of mapping supported:

Inline Mapping

Inline mapping is where a property of a class or PropertyBag can be mapped directly against one of the supported ‘big table’ database columns. The following types can be mapped inline:

  • System.Byte (and arrays of)
  • System.Int16
  • System.Int32
  • System.Int64
  • System.Enum
  • System.Single
  • System.Double
  • System.DateTime
  • System.String
  • System.Char (and arrays of)
  • System.Boolean
  • System.Guid
  • EPiServer.Data.Identity

Collection Mapping

A property is mapped as a collection if it implements the System.IEnumerable interface. In this case all elements of the collection (both keys and values in the case of System.IDictionary) are stored in a special reference table.

Note that even though the EPiServer.Data.Dynamic.PropertyBag implements System.IEnumerable is will actually be treated as a reference type (see below).

Reference Mapping

All properties that cannot be mapped inline or as a collection (plus the EPiServer.Data.Dynamic.PropertyBag type) are mapped as references. This means that their properties are mapped in-turn as a sub-type and a link row is added in the reference table to link the parent data structure with the child data structure. This allows for complex trees of data structures (object graphs) to be saved in the Dynamic Data Store.

Big Table

The default Dynamic Data Store ‘big table’ is called tblBigTable. This table contains 4 fixed columns (i.e. mandatory columns) which are:

  • pkId – The store id and primary key of each data structure stored
  • Row – The row index. Each structure may span 1 or more rows in the ‘big table’
  • StoreName – The name of the store the data structure belongs to
  • ItemType – The .NET CLR Type that contained the properties saved to the current row

The default ‘big table’ also contains the following optional columns:

  • BooleanXX (where XX is 01 through to 05) x 5
  • IntegerXX (where XX is 01 through to 10) x 10
  • LongXX (where XX is 01 through to 05) x 5
  • DateTimeXX (where XX is 01 through to 05) x 5
  • GuidXX (where XX is 01 through to 03) x 3
  • FloatXX (where XX is 01 through to 07) x 7
  • StringXX (where XX is 01 through to 10) x 10
  • BinaryXX (where XX is 01 through to 05) x 5      
  • Indexed_Boolean01 
  • Indexed_IntegerXX (where XX is 01 through to 03) x 3
  • Indexed_LongXX (where XX is 01 through to 02) x 2
  • Indexed_DateTime01
  • Indexed_Guid01
  • Indexed_FloatXX (where XX is 01 through to 03) x 3
  • Indexed_StringXX (where XX is 01 through to 03) x 3
  • Indexed_Binary01 (not Oracle)

The columns whose name starts with 'Indexed' have database indexes created on them.

You may add and remove columns in this table to suit the type of data you are saving. This may be particularly useful if you know you are going to store a data type with more than say 10 strings for example. By default the 11th to 20th strings would be stored in a 2nd row for the type which means a join has to be done at runtime when reading the data. By adding String11, String12 etc to the ‘big table’ you limit the chance of a row overspill and therefore increase performance. If you require more indexes then add columns with names starting with 'Indexed' and ensure an index is created on them.

You may also add your own ‘big table’ if you wish. This may be particularly useful if you know you will be storing a type that only contains strings for example. Then, along with the mandatory columns (pkId, Row, StoreName, ItemType) you could add say 20 StringXX columns.

The following tables lists the database columns types in the default ‘big table’ and the .NET CLR ‘inline’ types they are mapped to:

SQL Server Mappings

Database Column Type

.NET CLR ‘Inline’ Types

varbinary(max)

varbinary(900)

System.Byte[]

int

System.Byte, System.Int16, System.Int32, System.Enum

bigint

System.Int64

float

System.Single, System.Double

datetime

System.DateTime

uniqueidentifier

System.Guid

nvarchar(max)

nvarchar(450)

System.String, System.Char, System.Char[], EpiServer.Data.Identity

bit

System.Boolean

Oracle Mappings

Database Column Type

.NET CLR ‘Inline’ Types

BLOB

System.Byte[]

NUMBER(11)

System.Byte, System.Int16, System.Int32, System.Enum

NUMBER(38)

System.Int64

BINARY_DOUBLE

System.Single, System.Double

TIMESTAMP(3)

System.DateTime

RAW(16)

System.Guid

NCLOB

NVARCHAR2(2000) /

System.String, System.Char, System.Char[], EpiServer.Data.Identity

NUMBER(1)

System.Boolean

Store Database Views

Each store is actually represented in the database by a view. The views can be used as normal including cross joining with other tables and views in the database. 

Identity Management

Each data structure that is saved in the Dynamic Data Store is given an identity. This identity is represented by the EPiServer.Data.Identity class. The identity contains 2 parts, an external id which is a System.Guid and a store id which is a System.Int64.

The External Id can either be supplied by the user of the Dynamic Data Store or generated dynamically. The Store Id is always generated by the store.

Specific Identity Management

The implementer of a .NET class that is to be stored in the Dynamic Data Store can choose to explicitly to manage the Id their objects get when stored. This can be done in two ways:

  1. Implement the EPiServer.Data.Dynamic.IDynamicData interface
  2. Implement a property called ‘Id’ which is of type System.Guid

See the UsingManagedIdentity class in the Dyanamic Data Store download for examples of specific identity management.

Implicit Identity Management (POCO Support)

The Dynamic Data Store supports POCO objects. For a description of what POCO is see http://en.wikipedia.org/wiki/Plain_Old_CLR_Object.

When POCO objects are stored in the Dynamic Data Store, special care needs to be taken when saving (updating) existing objects. Because the Dynamic Data Store does not have an Id it can use to determine if an object is new or existing, it relies on state information held for objects that have been previously loaded through the same instance of the Dynamic Data Store when saving them back.

See the UsingImplicitidentity class in Dyanamic Data Store download for examples of implicit identity management.

 

LINQ Support

The Dynamic Data Store has extensive support for Microsoft’s Language Integrated Query (LINQ). The LINQ support is the same for both typed stores and for property bags.

Conditions

Where is supported on inline types, independent if the inline types is directly on the queried object or nestled inside another object.
var query = (from person in _personStore.Items<Person>() where person.Address.City == “Stockholm” select person);

Ordering

“Order by” and "Then by" are supported for inline types, independent if the inline types is directly on the queried object or nestled inside another object.

var query = (from person in _personStore.Items<Person>() orderby person.Address.City select person);

Selecting

To receive the whole object in the store, the “select object” can be used.
var query = (from person in _personStore.Items<Person>() select person);

Anonym type

It’s also possible to receive an anonym type from the store by using the “new” keword.
var query = (from person in _personStore.Items<Person>() select new { person.FirstName, person.LastName });

Enumerations

If an object contains an enumeration of an inline type, some operations on the enumeration are supported. “Max()”, “Min()”, and “Average()” are supported without predicates, “Count()” are supported both with and without predicates, and “Contains()” are supported with predicate. The predicates are only supported when the predicate queries an inline type.
var queryMax = (from person in _personStore.Items<Person>() select person.List.Max());

var queryMin = (from person in _personStore.Items<Person>() where person.List.Min() < 10 select person);

var queryAverage = (from person in _personStore.Items<Person>() select person.List.Average());

var queryCount = (from person in _personStore.Items<Person>() where person.List.Count == 2 select person);

var queryCountWithPredicate = (from person in _personStore.Items<Person>() select person.Address.Count(p => p.Street == “testar”));

var queryContains = (from person in _personStore.Items<Person>() select person.List.Countains(p => p == “testar”));

Grouping

Group by is supported for inline types. If the query has been grouped, some operations are supported for the grouped data. “Sum()”, “Max()”, “Min()”, and “Average()” are supported with predicates, and “Count()” are supported without predicate. The predicates are only supported when the predicate queries an inline type.
var query = _personStore.Items<Person>().GroupBy(p => p.Age).Select(m => new { Count = m.Count(), Sum = m.Sum(s => s.Friends.ShoeSize), Max = m.Max(s => s.Friends.ShoeSize), Min = m.Min(s => s.Friends.ShoeSize), Average = m.Avergage(s => s.Friends.ShoeSize) });

Skip, take, and reverse

Skip(x), Take(y) and Reverse() are also supported methods. Those can be helpful when developing paging.

query.Reverse();

query.Skip(10).Take(20);

String operations

Supported string operations are:
• StartsWith
• Contains
• EndsWith
• SubString(x)
• Trim
• IsNullOrEmpty
• ToUpper
• ToLower
• Length

var startsWith = (from person in _personStore.Items<Person>() where person.Address.City.StartsWith(“St”) select person);

var contains = (from person in _personStore.Items<Person>() where person.Address.City.Contains(“St”) select person);

var EndsWith = (from person in _personStore.Items<Person>() where person.Address.City.EndsWith(“holm”) select person);

var SubString = (from person in _personStore.Items<Person>() where person.Address.City.SubString(2)  == “ockholm” select person);

var trim = (from person in _personStore.Items<Person>() where person.Address.City.Trim() == “Stockholm” select person);

var isNullOrEmpty = (from person in _personStore.Items<Person>() where string.IsNullOrEmpty(person.Address.City) select person);

var toUpper = (from person in _personStore.Items<Person>() where person.Address.City.ToUpper() == “STOCKHOLM” select person);

var toLower = (from person in _personStore.Items<Person>() where person.Address.City.ToLower() == “stockholm” select person);

DateTime operations

Supported DateTime operations are:
• AddYear
• AddMonths
• AddDays
• AddMinutes
• AddSeconds
• AddMilliseconds
• Add
• Subtract
var addYears = (from person in _personStore.Items<Person>() where person.DateOfBirth.AddYears(4) < DateTime.Now select person);

var addMonths = (from person in _personStore.Items<Person>() where person.DateOfBirth.AddMonth(4) < DateTime.Now select person);

var addDays = (from person in _personStore.Items<Person>() where person.DateOfBirth.AddDays(4) < DateTime.Now select person);

var addMinutes = (from person in _personStore.Items<Person>() where person.DateOfBirth.AddMinutes(4) < DateTime.Now select person);

var addSeconds = (from person in _personStore.Items<Person>() where person.DateOfBirth.AddSeconds(4) < DateTime.Now select person);

var addMilliseconds = (from person in _personStore.Items<Person>() where person.DateOfBirth.AddMilliseconds(4) < DateTime.Now select person);

var add = (from person in _personStore.Items<Person>() where person.DateOfBirth.Add(new TimeSpan(1,2,3,4,5) < DateTime.Now select person);

var subtract = (from person in _personStore.Items<Person>() where person.DateOfBirth.Subtract (DateTime.Now) < new TimeSpan(1,2,3,4,5);

Executing queries

The queries are not executed until an execution method gets called. This means that it’s possible to work with the query until an execution method gets called without the overhead of going to the database all the time. Two execution method types are supported, “ToList” and “Count”.
var resultEnumerable = query.ToList();
var resultCount = query.Count();

Working with a query

Sometimes it’s necessary to have a condition in the code that should render the query in different ways depending on the condition. This is an example how to work with a query.
var query = (from person in _personStore.Items<Person>() select person);
if (myCondition)
{
   query = query.where(person.LastName.StartsWith(“a”);
}
var result = query.AsEnumerable();

See the UsingLinq class in the Dyanamic Data Store download for examples of Linq support.

Compile time Type Mapping

When instances of a compile time data type (.NET classes excluding EPiServer.Data.Dynamic.PropertyBag and classes implementing System.IEnumerable) are saved in the Dynamic Data Store, their ‘inline’ properties are mapped to columns in the ‘big table’. This is known logically as a store.

Default Mapping

The default algorithm for mapping .NET classes (excluding EPiServer.Data.Dynamic.PropertyBag and classes implementing System.IEnumerable) to as store is as follows:

  • Property must have a getter and setter
  • Property must be marked public (although the setter can marked non-public if desired)
  • All other properties are ignored and not saved in the Dynamic Data Store

Custom Mapping

It is possible to override the default mapping behavior. This is useful if you do not want certain public properties to be mapped or do want certain non-public properties to be mapped.

To use custom mapping you need to add the System.Runtime.Serialization.DataContactAttribute to your class definition. In this case, ONLY properties marked with the System.Runtime.Serialization.DataMemberAttribute will be mapped and saved in the Dynamic Data Store regardless of the accessibility status. They must still however have both a getter and setter.

See the MappingWithDataContract class in the Dyanamic Data Store download for examples of this.

You may wish to save an object of an existing class that has already been marked with DataContactAttribute and its member properties with DataMemberAttribute. One problem might be that the use of these properties does not match the desired behavior that you want when an object instance is saved in the Dynamic Data Store. In these cases you can also add the EPiServerDataContractAttribute to the class definition and EPiServerDataMemberAttribute to the properties to be saved. The Dynamic Data Store will use these attributes in preference to the Microsoft ones to resolve the conflict.

See the MappingWithEPiServerDataContract class in the Dyanamic Data Store download for examples this.

Type Handlers

Some classes in the .NET Framework do not have properties that the Dynamic Data Store could use to extract the value and save to the database and then re-inflate an instance with the value from the database. You may wish to use such a type as a property on a class that is to be saved to the Dynamic Data Store and therefore in this case you will need to register a Type Handler for it.

A good example of this is the System.Uri class. This class only has read-only properties and therefore saving an instance to the Dynamic Data Store without a Type Handler is meaningless as no data will be stored for it.

See the MappingWithTypeHandler class in the Dyanamic Data Store download for examples of this.

 

Runtime data type (PropertyBag) Mapping

Properties that are saved using PropertyBags are mapped as if the properties were public members properties on a normal .NET class. PropertyBags may be mapped in two ways:

  • Implicit – the first time a PropertyBag is saved to a new store the store mappings are inferred from the properties in the PropertyBag
  • Explicit – a mapping dictionary is passed to the DynamicDataStoreFactory.CreateStore method detailing the store mapping. The main advantage of this is that all properties that can be potentially saved in this store are mapped as opposed to the 1st time a PropertyBag is saved which may not have all potential values.

See the ImplicitDynamicMapping and ExplicitDynamicMapping classes in the Dyanamic Data Store SDK for examples of this. 

Store Re-mapping

From time to time you may need to change the structure of your data. This may mean adding, removing or changing properties on your .NET classes or PropertyBags.

The Dynamic Data Store is quite flexible when it comes to accepting changes to types that have been saved in a store.

To re-map a store obtain the store definition of a store either via the StoreDefinition property of a DynamicDataStore instance or via the StoreDefinition.Get method. You can then call the Rename and Remap methods to update the store's mappings. Note that you should call Rename before Remap otherwise properties that have been renamed in the data type will be treated as one property removed and one added. Finally call CommitChanges method of StoreDefinition to update the store's meta information in the database. If a DynamicDataStore instance is held then it's Refresh method should be called to align it in memory copy of the store definition with the one committed to disk.

The following rules are followed when re-mapping stores:

  1. Properties removed from the type definition are removed from the store. Important: The data itself for the property removed remains intact in the big table. It is only the ‘view’ of the data that is changed.
  2. Properties added to the type definition are added to the store.
  3. Properties with the same name but different data types are checked for compatibility. In the case of both old and new property data types being 'inline' then the database being used must be able to convert from the old database data type to the new database data type. In the case of a collection or reference type being changes then the new property type must either be assignable (using System.Type.IsAssignableFrom) or the old type must be convertible to the new type (the old type supports System.IConvertible) and the System.Convert.ChangeType succeeds in converting an instance of the old type to an instance of the new type.

See the StoreReMapping class for examples of store re-mapping and the PropertyReName class for how to update mappings when a property has been renamed on a type, both in the Dyanamic Data Store SDK.

 

Mapping Types to specific stores

It may be convenient to be able to always save instances of a Type in the same store, regardless of where those instances are in an object graph. There are two ways of doing this:

  1. Global Mapping- Use the EPiServer.Data.Dynamic.GlobalTypeToStore.Instance methods to add and remove a mapping or add an EPiServer.Data.Dynamic.EPiServerDataStoreAttribute to the .NET class. When this is used, all instances of the Type registered will be saved in the store with the specified name*
  2. Local Mapping – A delegate is passed to the DynamicDataStore or DynamicDataStore<T> Save method. This delegate will be called for all ‘reference’ properties and collection items that are references*. The delegate should return the name of the store to save the item in. This allows extra flexibility to steer instances of a Type into different stores depending on the context and how they are used.

* The store the top level item (the object passed to the DynamicDataStore Save method) is ALWAYS saved in the current store (the store Save is being called on) regardless of the name of that store. This effectively overrides the Type to Store mapping mechanism.

E.g. 

  1. A global Type to Store mapping is added so all instances of the Type ‘Person’ are saved in a store called “People”.
  2. A store is created/obtained with the name “MyPeople”.
  3. An instance of a Person Type is saved in the store obtained in step 2. This instance has properties that are also Person instances

Result: The top level Person will be saved in the “MyPeople” store BUT all other instances of the Person Type in the object graph will be saved in the “People” store (because of the global mapping).

In order to adhere to the global Type to Store mappings you should create/obtain your top level store by calling DynamicDataStoreFactory.Create or GetStore with just the typeof your type and not a store name.

See the UsingGlobalTypeToStoreMapping and UsingLocalTypeToStoreMapping classes in the Dynamic Data Store download for more details.

 

Mapping stores to custom big tables

In the same way as it may be convenient to map a Type to a Store, it may also prove convenient to map a Store to a custom Big Table. See the Big Table section for more details.

The UsingGlobalStoreToTableMapping class in the Dynamic Data Store download demonstrates this technique. 

Indexing Properties

Properties saved in store can be indexed for faster searching. When an index is set on a property it will automatically be mapped to a indexed column in the big table.

The UsingIndexes class in the Dynamic Data Store download demonstrates this technique.

Do’s and Don’ts

  1. DO create/obtain instances of the DynamicDataStore on the stack, use them and then discard them. The Dynamic Data Store is NOT thread safe and if an instance is used and shared between multiple threads it should be protected with thread locking techniques.
  2. DO implement EPiServer.Data.IDynamicData or a Guid property called Id on your objects to be saved when you want to control the external identity of your objects.
  3. DO use the same instance of a Dynamic Data Store to load and then update a POCO object (object without identity)