We are developing azure mobile services using code first database approach with existing database. We have added Id, CreatedAt, UpdatedAt, Version column in require table.
In project, SimpleMappedEntityDomainManager.cs used for domain mapping class. See attached class file here. Initialize method of each controller, creating domain instance using this class, code as below.
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,
Expression<Func<TModel, object>> dbKeyProperty)
: base(context, request, true)
{
this.dbKeyProperty = dbKeyProperty;
//this.Context = context;
}
public override SingleResult<TData> Lookup(string id)
{
return this.LookupEntity(GeneratePredicate(id));
}
public override async Task<TData> UpdateAsync(string id, Delta<TData> patch)
{
return await this.UpdateEntityAsync(patch, id);
}
public override Task<bool> DeleteAsync(string id)
{
return this.DeleteItemAsync(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);
}
}
}
Existing table as below:
Table: tbl_Property_equip
- property_equip_id - identity column-primary key
- property_id
- equipment_id
- property_equip_serial_number property_equip_unit_number
Table: tbl_property_loc
- property_loc_id - identity column-primary key
- property_id
- property_loc_name
Table: tbl_property_equip_area_served
- property_equip_area_served_id - identity column-primary key
- property_equip_id - tbl_Property_equip table reference
- property_loc_id - tbl_property_loc table reference
Model Class genereated as below:
public partial class tbl_property_equip {
//[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int property_equip_id { get; set; }
public int property_id { get; set; }
public int equipment_id { get; set; }
[StringLength(150)]
public string property_equip_serial_number { get; set; }
[StringLength(150)]
public string property_equip_unit_number { get; set; }
public virtual ICollection<tbl_property_equip_area_served> tbl_property_equip_area_served { get; set; }
}
public partial class tbl_property_loc
{
//[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int property_loc_id { get; set; }
public int property_id { get; set; }
[StringLength(255)]
public string property_loc_name { get; set; }
public virtual ICollection<tbl_property_equip_area_served> tbl_property_equip_area_served { get; set; }
}
public partial class tbl_property_equip_area_served
{
//[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int property_equip_area_served_id { get; set; }
public int property_equip_id { get; set; }
public int property_loc_id { get; set; }
public virtual tbl_property_equip tbl_property_equip { get; set; }
public virtual tbl_property_loc tbl_property_loc { get; set; }
}
POST/PATCH method is working fine for Property Equipment and Property Location, due to removed [KEY] and DatabaseGeneratedOption.Identity. It was not consider ID as primary key here.
But Property Area Served is not working, due to removed [KEY] from property equipment and location class. so that is consider ID as primary key for each. Relationship with Parent table is not working properly. so below error shown in postman.
{
"message": "An error has occurred.",
"exceptionMessage": "One or more validation errors were detected during model generation:\r\n\r\ntbl_property_loc_tbl_property_equip_area_served_Source_tbl_property_loc_tbl_property_equip_area_served_Target: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'property_loc_id' on entity 'tbl_property_equip_area_served' does not match the type of property 'Id' on entity 'tbl_property_loc' in the referential constraint 'tbl_property_loc_tbl_property_equip_area_served'.\r\ntbl_property_equip_tbl_property_equip_area_served_Source_tbl_property_equip_tbl_property_equip_area_served_Target: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'property_equip_id' on entity 'tbl_property_equip_area_served' does not match the type of property 'Id' on entity 'tbl_property_equip' in the referential constraint 'tbl_property_equip_tbl_property_equip_area_served'.\r\n",
"exceptionType": "System.Data.Entity.ModelConfiguration.ModelValidationException",
"stackTrace": " at System.Data.Entity.Core.Metadata.Edm.EdmModel.Validate()\r\n at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)\r\n at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)\r\n at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)\r\n at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)\r\n at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()\r\n at System.Data.Entity.Internal.InternalContext.Initialize()\r\n at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)\r\n at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()\r\n at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()\r\n at System.Data.Entity.Internal.Linq.InternalSet`1.ActOnSet(Action action, EntityState newState, Object entity, String methodName)\r\n at System.Data.Entity.Internal.Linq.InternalSet`1.Add(Object entity)\r\n at System.Data.Entity.DbSet`1.Add(TEntity entity)\r\n at Microsoft.Azure.Mobile.Server.MappedEntityDomainManager`2.<InsertAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n at Microsoft.Azure.Mobile.Server.TableController`1.<InsertAsync>d__3.MoveNext()"
}
I tried to created Domain Mapper class for property area served table/controller as given in example as below link. But could not success. shall we use same approach for existing database as given example here.
