Adding a Relationship to the Azure Mobile Service Entity Model

After removing the TodoItem table and replacing it with a simple RealEstateProperty table (see previous post), the next step is to look at how I can add another related entity. I’m going to leverage the default behaviour exposed by Entity Framework using the Fluent API (see more on this in the article Code First Relationships Fluent API). To this end my entities will look like:

public class RealEstateProperty : EntityData
{
    public string Address { get; set; }

    public virtual ICollection<Inspection> Inspections { get; set; }
}

public class Inspection : EntityData
{
    public string InspectedBy { get; set; }

    public string RealEstatePropertyId { get; set; }
    public RealEstateProperty RealEstateProperty { get; set; }
}

And I’ll of course need to update the realestateinspectorContext:

public DbSet<RealEstateProperty> RealEstateProperties { get; set; }
public DbSet<Inspection> Inspections { get; set; }

The last code change I need to make is to add the InspectionController required to expose the data for this new table. To do this I could have duplicated the RealEstatePropertyController, renamed it and changed the generic type but this leads to quite a lot of unnecessary duplicate code. Instead with a little bit of refactoring I can abstract nearly all the code into a base controller:

public class RealEstateBaseTableController<TEntity> : TableController<TEntity>
    where TEntity : class, ITableData
{
    protected override void Initialize(HttpControllerContext controllerContext)
    {
        base.Initialize(controllerContext);
        var context = new realestateinspectorContext();
        DomainManager = new EntityDomainManager<TEntity>(context, Request, Services);
    }

    // GET tables/TEntity
    public IQueryable<TEntity> GetAll()
    {
        return Query();
    }

    // GET tables/TEntity/48D68C86-6EA6-4C25-AA33-223FC9A27959
    public SingleResult<TEntity> Get(string id)
    {
        return Lookup(id);
    }

    // PATCH tables/TEntity/48D68C86-6EA6-4C25-AA33-223FC9A27959
    public Task<TEntity> Patch(string id, Delta<TEntity> patch)
    {
        return UpdateAsync(id, patch);
    }

    // POST tables/TEntity
    public async Task<IHttpActionResult> Post(TEntity item)
    {
        TEntity current = await InsertAsync(item);
        return CreatedAtRoute(“Tables”, new { id = current.Id }, current);
    }

    // DELETE tables/TEntity/48D68C86-6EA6-4C25-AA33-223FC9A27959
    public Task Delete(string id)
    {
        return DeleteAsync(id);
    }
}

All I then need to do is inherit from this base class for my two controllers:

public class RealEstatePropertyController : RealEstateBaseTableController<RealEstateProperty> { }

public class InspectionController : RealEstateBaseTableController<Inspection> { }

With the updates to my data model I will of course need to add the appropriate migration by invoking the following in the Package Manager Console:

Add-Migration AddedInspections

I can now run the service project and when I invoke a GET on either controller the database will be upgraded using the new migration code.

One mistakes I came across in writing this post is that I had made the RealEstatePropertyId an int instead of a string. This of course meant my initial migration code setup a column that was an int which clearly couldn’t accept the guid id of the RealEstateProperty. There are two ways to handle this. I could have corrected the data model and run Add-Migration to create another migration. I find this to be a bit messy as the migration code will forever keep a record of my mistake. At this point, since I haven’t pushed this migration into production, I can easily undo the update to the database (essentially a backward/downward migration) and regenerate the AddedInspection migration. To undo the database update I have to invoke the following in the Package Manager Console:

Update-Database -TargetMigration 201501051134554_AddingRealEstatePropertiesToSchema

Here I’m specifying the last migration I want the database to roll back to, which in this case is the migration I created in my previous post. Once I’ve done this I call Add-Migration again:

Add-Migration AddedInspections –Force

Note that I’m including the –Force switch which will force the migration code to be fully regenerated, rather than just the designer code.

That’s all – I can re-run the application and it will apply the new migration (or you can just run Update-Database to migrate the database forward with the corrected migration).

Lastly, to create a Inspection I can again use Fiddler to invoke a POST method:

POST: http://realestateinspector.azure-mobile.net/tables/inspection
Content-Type: application/json
X-ZUMO-APPLICATION: wpxaIplpeXtkn————————-cg12

{“InspectedBy”:”Fred”,”RealEstatePropertyId”:”a7217f7e90b242f99f55cdef6b674c5d”}

This includes the RealEstatePropertyId of a property that exists in the database (since it’s a relationship) – if you attempt to use a value that doesn’t exist, you’ll get a 400 error back with an error which effectively says you’re attempting to violate the database schema.

Leave a comment