Azure Mobile App using existing database

Question

I'm at my first attempt in using Azure Mobile App, and I should connect it to an existing and already populated SQL Azure DB. As far as I understand, one must add to the tables the Version, CreatedAt, UpdatedAt, and Deleted columns, and most important there must be and id column set as identity.

The problem is that on some tables I already have an identity column (eg. ItemID), wich I cannot rename without breaking existing third party applications that connects to the data.

The question is: Is there any way to avoid using the identity Id field, maybe someway mapping the original identity?

[edit] I've already looked at the samples on the web, like that:

https://blogs.msdn.microsoft.com/azuremobile/2014/05/22/tables-with-integer-keys-and-the-net-backend/

but there seems to exist some differences between mobile service and mobile app, and I'd really be happy if someone point me out to the right direction, maybe with an usable example

Kiske1 Kiske1 · Accepted Answer · 2016-03-17T12:00:03

With some adaptations the sample worked!

Taking as starting point the tutorial here:

https://blogs.msdn.microsoft.com/wsdevsol/2014/07/17/walkthrough-attaching-an-azure-sql-database-to-your-net-backend/

here's what I've done:

*Added to my table the ITableData mandatory fields:

[Version] ROWVERSION NOT NULL,
[CreatedAt] DATETIMEOFFSET (7) DEFAULT (sysutcdatetime()) NOT NULL,
[UpdatedAt] DATETIMEOFFSET (7) NULL,
[Deleted] BIT DEFAULT ((0)) NOT NULL
Created the model with EF Code First From database
Created the DTO class with only the data fields and inherited from EntityData

*Created the custom MappingDomaninManager like this:

public class MSSEntityDomainManager<TData, TModel>
        : MappedEntityDomainManager<TData, TModel>
        where TData : class, ITableData, new()
        where TModel : class
{
private Expression<Func<TModel, object>> dbKeyProperty;

public MSSEntityDomainManager(MssContext context, 
                              HttpRequestMessage request, 
                              Expression<Func<TModel, object>> dbKeyProperty):base(context, request)
{
    this.dbKeyProperty = dbKeyProperty;
}

public override Task<bool> DeleteAsync(string id)
{
    return this.DeleteItemAsync(ConvertId(id));
}

public override SingleResult<TData> Lookup(string id)
{
    return this.LookupEntity(GeneratePredicate(id));
}

public override Task<TData> UpdateAsync(string id, Delta<TData> patch)
{
    return await this.UpdateEntityAsync(patch, ConvertId(id));
}

private static Expression<Func<TModel, bool>> GeneratePredicate(string id)
{
    var m = Mapper.FindTypeMapFor<TModel, TData>();
    var pmForId = m.GetExistingPropertyMapFor(new AutoMapper.Impl.PropertyAccessor(typeof(TData).GetProperty("Id")));
    var keyString = pmForId.CustomExpression;
    var predicate = Expression.Lambda<Func<TModel, bool>>(
        Expression.Equal(keyString.Body, Expression.Constant(id)),
        keyString.Parameters[0]);
    return predicate;
}

private object ConvertId(string id)
{
    var m = Mapper.FindTypeMapFor<TData, TModel>();
    var keyPropertyAccessor = GetPropertyAccessor(this.dbKeyProperty);
    var pmForId = m.GetExistingPropertyMapFor(new AutoMapper.Impl.PropertyAccessor(keyPropertyAccessor));
    TData tmp = new TData() { Id = id };
    var convertedId = pmForId.CustomExpression.Compile().DynamicInvoke(tmp);
    return convertedId;
}

private PropertyInfo GetPropertyAccessor(Expression exp)
{
    if (exp.NodeType == ExpressionType.Lambda)
    {
        var lambda = exp as LambdaExpression;
        return GetPropertyAccessor(lambda.Body);
    }
    else if (exp.NodeType == ExpressionType.Convert)
    {
        var convert = exp as UnaryExpression;
        return GetPropertyAccessor(convert.Operand);
    }
    else if (exp.NodeType == ExpressionType.MemberAccess)
    {
        var propExp = exp as System.Linq.Expressions.MemberExpression;
        return propExp.Member as PropertyInfo;
    }
    else {
        throw new InvalidOperationException("Unexpected expression node type: " + exp.NodeType);
    }
}
}

The differences with the original sample are the complete removal of the ApiServices reference from the constructor, and the addition of the AutoMapper.Impl namespace to PropertyAccessor, otherwise by default it will use the System.Web.Http.OData one.

*Created the SQL utility class exactly like the one in the example

public static class MySqlFuncs
{
    [DbFunction("SqlServer", "STR")]
    public static string StringConvert(long number)
    {
        return number.ToString();
    }
    [DbFunction("SqlServer", "LTRIM")]
    public static string LTRIM(string s)
    {
        return s == null ? null : s.TrimStart();
    }
    // Can only be used locally.
    public static long LongParse(string s)
    {
        long ret;
        long.TryParse(s, out ret);
        return ret;
    }
}

This one I've decided to put in a separate "utility" file

*Created the mapping in Startup.MobileApp.cs file, as the WebApiConfig.cs mentioned in the samples don't exist in the Mobile App. The Automapper initialize code is working as-is, and I've put it just after HttpConfiguration config = new HttpConfiguration(); in ConfigureMobileApp function. For reference:

AutoMapper.Mapper.Initialize(cfg =>
        {
            // Mapping from database type to client type
            cfg.CreateMap<StuffToGet, StuffToGetDto>()
                .ForMember(dst => dst.Id, map => map.MapFrom(src => MySqlFuncs.LTRIM(MySqlFuncs.StringConvert(src.ID))));
            // Mapping from client type to database type
            cfg.CreateMap<StuffToGetDto, StuffToGet>()
                .ForMember(dst => dst.ID, map => map.MapFrom(src => MySqlFuncs.LongParse(src.Id)));

        });

Marked with the "*" are the points that are different from the original msdn post. Hope someone found that helpful!

Azure Mobile App using existing database

1 Answers