Update SQL CE 3.5 SP1 Change Tracking Metadata

Why

SqlCeClientSyncProvider does not populate change tracking information. This is reasonable as when it get data from server, there is no change. However, if you want to use SqlCeClientSyncProvider for peer-to-peer scenario, you need to populate the change tracking information, so the changes can be marked and pick up by other nodes. This is applicable for two step upload/check in process as well. Sometimes you may also want to clear the change tracking information, so they will not be sync back to server.

SQL Server Compact Edition 3.5 SP1 change trakcing metadata

Sync Framework Database Providers (known as Sync Services for ADO.NET) store change tracking information in following tables and columns for SQL CE 3.5 SP1:

• Each table contains these two columns that indicate changed or inserted records: __sysChangeTxBsn, __sysInsertTxBsn.
• Table __sysOcsDeletedRows stored deleted records. It has these columns: __sysTName, __sysRK, __sysDeleteTxBsn, __sysInsertTxCsn, __sysDeletedTime.
• Table __sysSyncArticles contains anchors. It has these columns: TableName, SentAnchor, ReceivedAnchor, ClientId.
• Table __sysSyncSubscriptions contains ClientId, ServerId, MachineId.

TxBsn stands for Transaction Begin Counter. TxCsn stands for Transaction Commit Counter. See details: SQL CHANGE TRACKING LAYER.

Change anchors

Anchors can be changed by these two methods:

• SqlCeClientSyncProvider.SetTableSentAnchor
• SqlCeClientSyncProvider.SetTableReceivedAnchor

This is the code to decode SentAnchor:

reader.GetBytes(0, 0L, buffer, 0, 8);
long num2 = BitConverter.ToInt64(buffer, 0);

ReceivedAnchor is returned by SelectNewAnchorCommand from server, and serialized in following way:

object _rawNewAnchor = newAnchor;
using (MemoryStream serializationStream2 = new MemoryStream())
{
  new BinaryFormatter().Serialize(serializationStream2, _rawNewAnchor);
  SyncAnchor newAnchor = (new SyncAnchor());
  newAnchor.Anchor = serializationStream.ToArray();
//...
}

newAnchor.Anchor is the value of __sysSyncArticles.ReceivedAnchor.

Change System data

Tables and columns with “__" prefix store system data that cannot be update by open APIs. Internal APIs only are exposed to friendly assemblies developed by Microsoft. But we can all them via reflection.

private PropertyInfo engineFlagsProperty;
private SqlCeTransaction transation;
private SeTransactionFlags engineFlags;
private int refCntSystemTx;

private void EnterSystemAPI()
{
    if (this.transation != null)
    {
        this.engineFlags = (SeTransactionFlags)this.engineFlagsProperty.GetValue(this.transation, null);
        engineFlagsProperty = typeof(SqlCeTransaction).GetProperty("EngineFlags", BindingFlags.NonPublic | BindingFlags.Instance);
        this.engineFlagsProperty.SetValue(this.transation, SeTransactionFlags.GENERATEROWGUID | SeTransactionFlags.GENERATEIDENTITY | SeTransactionFlags.SYSTEM, null);
    }

    this.refCntSystemTx++;
}       

private void LeaveSystemAPI()
{
    this.refCntSystemTx--;
    if (this.refCntSystemTx <= 0)
    {
        if (this.transation != null)
        {
            this.engineFlagsProperty.SetValue(this.transation, this.engineFlags, null);
        }

        this.refCntSystemTx = 0;
    }
}

[Flags]
internal enum SeTransactionFlags
{
    COMPRESSEDLVSTREAM = 0x40,
    DISABLETRIGGERS = 0x20,
    GENERATEIDENTITY = 2,
    GENERATEROWGUID = 4,
    NOFLAGS = 0,
    REPLACECOLUMN = 0x10,
    SYSTEM = 1,
    TRACK = 8,
    VALIDFLAGS = 0x7f
}

Using the code above, you will able to update the system data in this way:

try
{
    EnterSystemAPI();
   //run update system data command
}
finally
{
    LeaveSystemAPI();
}

Strongly Typed Programming by Lambda Expressions

public class StrongTypedHelper
{
    public static string GetPropertyOrFieldName<T>(Expression<Func<T, object>> lambdaExpression)
    {
        MemberExpression memberExpression = lambdaExpression.Body as MemberExpression;
        if (memberExpression == null)
        {
            UnaryExpression unaryExpression = lambdaExpression.Body as UnaryExpression;
            if (unaryExpression != null)
            {
                memberExpression = unaryExpression.Operand as MemberExpression;
            }
        }

        Debug.Assert(memberExpression == null, "This expression is not supported.");
        MemberExpressionexpression = memberExpression.Expression as MemberExpression;
        if (expression == null)
        {
            return memberExpression.Member.Name;
        }
        else
        {
            string memberName = null;
            do
            {
                memberName = String.Format("{0}.{1}", expression.Member.Name, memberName);
                expression = expression.Expression as MemberExpression;
            } while (expression != null);

            return memberName + memberExpression.Member.Name;
        }
    }
}

Use this helper class we can use strongly typed instead of hard coded strings in UI data binding or ObjectQury.Include mehtod, and other places. Ex.

Instead of: context.Orders.Include(“OrderDetails”).Where(x => x.OrderId = 123).FirstOrDefault();

Now we use use: context.Orders.Include(StrongTypedHelper<Order>.GetPropertyOrFieldName(x => x.OrderDetails).Where(x => x.OrderId = 123).FirstOrDefault();

Change Entity Data Model at Runtime

Why

• Use the same EDM in different environments (Dev, Test, Production etc.) that have different database schema.
• Use the same EDM for different database systems (both SQL Server Compact Edition and SQL Server)

How

Entity Framework ObjectContext stores metadata in MetadataWorkspace property. We can load the meta into a MetadataWorkspace when application start, and then create ObjectContext using these two constructors:

• EntityConnection(MetadataWorkspace, DbConnection)
• ObjectContext(EntityConnection)

Example

Following code demonstrates changing storage DB schema for using the EDM generated from SQL Server on SQL CE.

Assembly edmAssembly = Assembly.GetExecutingAssembly();

XmlReader metaReader = XmlReader.Create(edmAssembly.GetManifestResourceStream("EDM.ssdl"));

XElement ssdl = XElement.Load(metaReader);

//change Provider

ssdl.Attribute("Provider").Value = "System.Data.SqlServerCe.3.5";

ssdl.Attribute("ProviderManifestToken").Value = "3.5";
…

//EF for SQL CE 3.5 SP1 doesn’t support server generated column, location the XElement and change the attribute
…element.Attribute("StoreGeneratedPattern").Value = "None";

…


List<XmlReader> r= new List<XmlReader>();

r.Add(ssdl.CreateReader());

StoreItemCollection sic = new StoreItemCollection(r);

r[0] = XmlReader.Create(edmAssembly.GetManifestResourceStream("EDM.csdl"));

EdmItemCollection eic = new EdmItemCollection(r);

r[0] = XmlReader.Create(edmAssembly.GetManifestResourceStream("EDM.msl"));

StorageMappingItemCollection smic = new StorageMappingItemCollection(eic, sic, r);



MetadataWorkspace
workspace = new MetadataWorkspace();

workspace.RegisterItemCollection(eic);

workspace.RegisterItemCollection(sic);

workspace.RegisterItemCollection(smic);



Only one instance of MetadataWorkspace is needed for a EDM in a application.

References

• Change Entity Framework storage DB schema in runtime
• Tracing and Caching Provider Wrappers for Entity Framework