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!
Comments
Anonymous
December 09, 2014
Hi Jason,This is great work thanks, although one small issue is when we are using this with GUIDs as a primary key. It fails to update using the "UpdateEntityAsync"? - All the other overrides seem to work? What am I doing wrong?public override Task<TData> UpdateAsync(string id, Delta<TData> patch) { return this.UpdateEntityAsync(patch, ConvertId(id)); }Regards,GavinAnonymous
December 10, 2014
Okay after a few hours I found out that for some reason if I capitalised the first letter in my property name(s) in the DTO then it picked up the patch changes (even though the model and db was all lowercase?!) Uggg......E.g. public class tblUserDTO : EntityData { public int Client_id { get; set; } public string First_name { get; set; } public Guid Supplier_guid { get; set; }etc....}Anonymous
January 04, 2015
Hi Jason, The Class PropertyAccessor 's structure is protected ,so in the SimpleMappedEntityDomainManager i can't access it , i have an error with it ,how can I solve it ?Anonymous
January 27, 2015
Hi, thank you so much for a post!One question about:'Typically, when starting from an Entity Framework Code-First model in your .NET Backend, you would use string ids.'What is benefits of this?And what is best practice to make insert operation with string id?What about performance, etc?Anonymous
July 21, 2015
So, you don't have the Version, or Last Updated your back end database? Will it still work?Anonymous
September 06, 2015
I don't understand why the use o string for a database id. This also happens on Identity Framework, at least there you can change it because they use generics, so you can extend the classes and change the id type.