Tables with Integer Keys and the .NET Backend
A question I've seen asked a few times on our internal email lists "how to expose tables that have integer keys in the database through the .NET Backend?". The key is to use AutoMapper (see Mapping between Database Types and Client Types in the .NET Backend using AutoMapper an introduction). For example, the other day a customer commented:
- If you are trying to integrate with an existing database or creating your tables from scratch with int or bigint id columns, how can we use those?
Wintelect's John Garland had some good feedback, which I will elaborate on here.
Note: Typically, when starting from an Entity Framework Code-First model in your .NET Backend, you would use string ids. However, generate your EF data model from from an existing database, you might have to deal with integer, long integer, or even GUID-valued keys.
Sample Setup
To make things concrete, let's start with the Mobile Service .NET backend Todo quickstart project.
Add a new entity data type to your model.
public partial class Customer { public long CustomerId { get; set; } public string Name { get; set; } }Add the new Customer type to your data context, stored in Models/YourServiceContext.cs.
public DbSet<Customer> Customers { get; set; }At this point, the issue is that the Customer type cannot be exposed directly through the TableController because there is no Id property and the key value is not a string.
Add a new customer-facing data transfer object (DTO), CustomerDto, which can be used by the TableController.
public class CustomerDto : EntityData { public string Name { get; set; } }(The base EntityData type defines the string Id property.)
Now we have to define the mapping between Customer and CustomerDto, and the controller that uses that mapping to expose CustomerDto.
SimpleMappedEntityDomainManager
The domain manager that we used in the previous mapping post is almost what we need, which is a clear sign that we should refactor the code into a reusable class. The things we need to change are:
- The database and client types => we need a generic domain manager
- The way we identified the database type's key as a string => we will require a function for getting the key.
We end up with (updated on August 12, 2014 to correct limitations, removed code is strikethrough, new code is underlined):
public class SimpleMappedEntityDomainManager<TData, TModel>
: MappedEntityDomainManager<TData, TModel>
where TData : class, ITableData
where TModel : class
{
private Func<TModel, string> keyString;
public SimpleMappedEntityDomainManager(DbContext context,
HttpRequestMessage request, ApiServices services,
Func<TModel, string> keyString)
: base(context, request, services)
{
this.keyString = keyString;
}
public override SingleResult<TData> Lookup(string id)
{
return this.LookupEntity(p => this.keyString(p) == id);
}
public override Task<TData> UpdateAsync(string id, Delta<TData> patch)
{
return this.UpdateEntityAsync(patch, id);
}
public override Task<bool> DeleteAsync(string id)
{
return this.DeleteItemAsync(id);
}
}
public class SimpleMappedEntityDomainManager<TData, TModel>
: MappedEntityDomainManager<TData, TModel>
where TData : class, ITableData, new()
where TModel : class
{
private Expression<Func<TModel, object>> dbKeyProperty;
public SimpleMappedEntityDomainManager(DbContext context,
HttpRequestMessage request, ApiServices services,
Expression<Func<TModel, object>> dbKeyProperty)
: base(context, request, services) {
this.dbKeyProperty = dbKeyProperty;
}
public override SingleResult<TData> Lookup(string id) {
return this.LookupEntity(GeneratePredicate(id));
}
public override Task<TData> UpdateAsync(string id, Delta<TData> patch) {
return this.UpdateEntityAsync(patch, ConvertId(id));
}
public override Task<bool> DeleteAsync(string id) {
return this.DeleteItemAsync(ConvertId(id));
}
private static Expression<Func<TModel, bool>> GeneratePredicate(string id) {
var m = Mapper.FindTypeMapFor<TModel, TData>();
var pmForId = m.GetExistingPropertyMapFor(new 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 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 CustomerController uses this domain manager:
public class CustomerController : TableController<CustomerDto>
{
protected override void Initialize(HttpControllerContext controllerContext)
{
base.Initialize(controllerContext);
YourDatabaseContext context = new YourDatabaseContext();
//DomainManager = new EntityDomainManager<CustomerDto>(context, Request, Services);
DomainManager = new SimpleMappedEntityDomainManager<CustomerDto, Customer>(
context, Request, Services, p => p.CustomerId.ToString());
context, Request, Services, p => p.CustomerId);
}
public IQueryable<CustomerDto> GetAllCustomerDto()
{
return Query();
}
public SingleResult<CustomerDto> GetCustomerDto(string id)
{
return Lookup(id);
}
public Task<CustomerDto> PatchCustomerDto(string id, Delta<CustomerDto> patch)
{
return UpdateAsync(id, patch);
}
public async Task<IHttpActionResult> PostCustomerDto(CustomerDto item)
{
CustomerDto current = await InsertAsync(item);
return CreatedAtRoute("Tables", new { id = current.Id }, current);
}
public Task DeleteCustomerDto(string id)
{
return DeleteAsync(id);
}
}
Define the Mappings
We need to define a mapping in App_Start\WebApiConfig.cs. In the line following the one where config is defined, add this:
AutoMapper.Mapper.Initialize(cfg =>
{
// Define a map from the database type Customer to
// client type CustomerDto. Used when getting data.
// The long-valued CustomerId is converted to a string,
// trimmed to remove leading spaces, then assigned
// to the string-valued Id property.
cfg.CreateMap<Customer, CustomerDto>()
.ForMember(customerDto => customerDto.Id, map => map.MapFrom(
customer => MySqlFuncs.LTRIM(MySqlFuncs.StringConvert(customer.CustomerId))));
// Define a map from the client type to the database
// type. Used when inserting and updating data.
// The string-valued Id property is converted to a long integer,
// then assigned to the long-valued CustomerId property.
cfg.CreateMap<CustomerDto, Customer>()
.ForMember(customer => customer.CustomerId, map => map.MapFrom(
customerDto => MySqlFuncs.LongParse(customerDto.Id)));
});
Note: If you get an error The name 'AutoMapper' does not exist in the current context, then add references to AutoMapper.dll and AutoMapper.Net4.dll from \packages\AutoMapper.3.1.1\lib\net40. This was an issue with older quickstarts, but it should be fixed soon.
The simple mapping, just using ToString and TryParse, cannot be used for two reasons. First, when mapping to the DTO from the database type, the function must be transformable by Entity Framework to a SQL expression. Second, only expressions can be used as the mapping functions. The helper conversion functions are defined like this:
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;
}
}
The DbFunction attribute informs Entity Framework what SQL function the call should be converted to. It is only needed on functions appearing in the map from the database type. Coming up with these function definitions is somewhat tricky, and it is an area where the Mobile Service and Entity Framework teams are looking to provide better in-product support.
I hope this has been helpful!