Insert/Update data with 1:n relationship using .NET backend Azure Mobile Services

In the earlier post I have shown how you can read 1:n data from a mobile service. In this post I will show how to insert and update that data. Write operations need more control because you need to protect your back-end database. Following the concepts explained in the post: Mapping between Database Types and Client Types in the .NET Backend using AutoMapper, I will

  • Define the new types that will be exposed to the client
  • Create mappings between the database types and the client types using AutoMapper
  • Update the table controller to use new client types and update PATCH method to handle related data

Service Setup

Example in this post extends the service implementation introduced in the post: Retrieving data from 1:n relationship using .NET backend Azure Mobile Services. To get started get the code from: Read related data from client sample code

Define client types and create mappings

Database types defined the earlier example  follow Entity Framework Fluent Api naming conventions in order to establish 1:n relationship between TodoItem and Item. But this will cause issues while serializing and deserializing POST/PATCH requests due to circular dependency between the database types. To avoid this without changing the shape of the database types, add new client types that will be exposed via table controllers to the client. 

Add following new classes in Service\DataObjects folder

 public class TodoItemDTO : EntityData
{
    public string Text { get; set; }
    public bool Complete { get; set; }
    public virtual ICollection<ItemDTO> Items { get; set; }
}
 public class ItemDTO : EntityData
{
    public string ItemName { get; set; }
}

Classes TodoItem and Item represent the database types. Update these classes to not inherit from EntityData as they are not going to be exposed via controllers

 public class TodoItem
{
    public TodoItem()
    {
        this.Items = new List<Item>();
    }
    public string Text { get; set; }
    public string Id { get; set; }
    public bool Complete { get; set; }
    public virtual ICollection<Item> Items { get; set; }
}
 public class Item
{
    public string ItemName { get; set; }
    public string Id { get; set; }
    public string TodoItemId { get; set; }
    public virtual TodoItem TodoItem { get; set; }
}

Now, define mappings between the database types and the client types.  Add following code in App_Start\WebApiConfig.cs in the line following the config definition

 AutoMapper.Mapper.Initialize(cfg =>
{
    cfg.CreateMap<TodoItem, TodoItemDTO>()
        .ForMember(todoItemDTO => todoItemDTO.Items,
                            map => map.MapFrom(todoItem => todoItem.Items));
    cfg.CreateMap<TodoItemDTO, TodoItem>()
            .ForMember(todoItem => todoItem.Items, 
                        map => map.MapFrom(todoItemDTO => todoItemDTO.Items));
 
    cfg.CreateMap<Item, ItemDTO>();
    cfg.CreateMap<ItemDTO, Item>();
});

Add mappings between database types and client types

Add a new class SimpleMappedEntityDomainManager as described in the post Bring Your Own Database with the .NET Backend.

Table controller in Controllers/TodoItemController.cs needs be updated to use the new client type TodoItemDTO. In the rest of the post I will show code snippets from the TodoItemController.cs and explain the changes made for handling related entities. You can get complete code for the example in this post from my blog sample repo in github.

Let's take a look at the PostTodoItem method on TodoItemController

 public async Task<IHttpActionResult> PostTodoItem(TodoItemDTO todoItemDTO)
{
    //Entity Framework inserts new TodoItem and any related entities
    //sent in the incoming request
    TodoItemDTO current = await InsertAsync(todoItemDTO);
    return CreatedAtRoute("Tables", new { id = current.Id }, current);
}

Default implementation of the post method in the table controller takes care of inserting new entities along with their related entities in to the database. Calling base method InsertAsync, converts the client types TodoItemDTO, ItemDTO in the incoming request to the database types TodoItem, Item and inserts them in to the database.

See how Entity Framework saves changes to understand how Entity Framework keeps track of entity states and saves the changes to the database.

Now, let's take a look at the PatchTodoItem method

 public async Task<TodoItemDTO> PatchTodoItem(string id,
    Delta<TodoItemDTO> patch)
{
    //Look up TodoItem from database so that EntityFramework updates
    //existing entry
    TodoItem currentTodoItem = this.context.TodoItems.Include("Items")
                            .First(j => (j.Id == id));
 
    TodoItemDTO updatedpatchEntity = patch.GetEntity();
    ICollection<ItemDTO> updatedItems;
 
    //Check if incoming request contains Items
    bool requestContainsRelatedEntities = patch.GetChangedPropertyNames()
                            .Contains("Items");
 
    if (requestContainsRelatedEntities)
    {
        //Remove related entities from the database. Comment following for loop if you do not
        //want to delete related entities from the database
        for (int i = 0; i < currentTodoItem.Items.Count 
            && updatedpatchEntity.Items != null; i++)
        {
            ItemDTO itemDTO = updatedpatchEntity.Items.FirstOrDefault(j =>
                            (j.Id == currentTodoItem.Items.ElementAt(i).Id));
            if (itemDTO == null)
            {
                this.context.Items.Remove(currentTodoItem.Items.ElementAt(i));
            }
        }
 
        //If request contains Items get the updated list from the patch
        Mapper.Map<TodoItemDTO, TodoItem>(updatedpatchEntity, currentTodoItem);
        updatedItems = updatedpatchEntity.Items;
    }
    else
    {
        //If request doest not have Items, then retain the original association
        TodoItemDTO todoItemDTOUpdated = Mapper.Map<TodoItem, TodoItemDTO>
                                        (currentTodoItem);
        patch.Patch(todoItemDTOUpdated);
        Mapper.Map<TodoItemDTO, TodoItem>(todoItemDTOUpdated, currentTodoItem);
        updatedItems = todoItemDTOUpdated.Items;
    }
 
    if (updatedItems != null)
    {
        //Update related Items
        currentTodoItem.Items = new List<Item>();
        foreach (ItemDTO currentItemDTO in updatedItems)
        {
            //Look up existing entry in database
            Item existingItem = this.context.Items
                        .FirstOrDefault(j => (j.Id == currentItemDTO.Id));
            //Convert client type to database type
            existingItem = Mapper.Map<ItemDTO, Item>(currentItemDTO,
                    existingItem);
            existingItem.TodoItem = currentTodoItem;
            currentTodoItem.Items.Add(existingItem);
        }
    }
 
    await this.context.SaveChangesAsync();
 
    //Convert to client type before returning the result
    var result = Mapper.Map<TodoItem, TodoItemDTO>(currentTodoItem);
    return result;
}

Entity Framework does not update related entities by default. To have more control over how updates are applied in the database, you need to use database context directly. For updating any existing entities in the database,

  • Find the entity in the database. This is required as Entity Framework needs to know that you are working an existing item in the database
  • Convert the client type to the database type. In this case TodoItemDTO to TodoItem and ItemDTO to Item
  • Apply updates in the PATCH request to the database entry
  • Save changes in the database

In the code above, removing a child entity from the associated list will remove the association and deletes the associated Items from the database. If you choose not to delete related Items from database comment following code

 //Remove related entities from the database. Comment following for loop if you do not
//want to delete related entities from the database
for (int i = 0; i < currentTodoItem.Items.Count 
    && updatedpatchEntity.Items != null; i++)
{
    ItemDTO itemDTO = updatedpatchEntity.Items.FirstOrDefault(j =>
                    (j.Id == currentTodoItem.Items.ElementAt(i).Id));
    if (itemDTO == null)
    {
        this.context.Items.Remove(currentTodoItem.Items.ElementAt(i));
    }
}

Test updated service

To test the updates made to the service, build and run the service locally. Click on the try this out button on the help page.

Now, click on Post tables/TodoItem then click try this out button on the top.

Paste following json in the sample body:

 {
    "text": "Stationery",
    "id": "2",
    "complete": false,
    "items":[
        { "itemName" : "pen", "id" : "3" },
        { "itemName" : "pencil", "id" : "4" }
    ]
}

and then click the send button. You should see 201/created in the response. This POST request inserted new TodoItem: Stationery along with the related Items: pen, pencil into the database. You can verify the updates made to the database by querying the TodoItem table. To send GET request to the TodoItemController, go back to the main help page. Click on GET tables/TodoItem and then click on the try this out button on the top of the page. Update GET uri to

 tables/TodoItem/?$expand=items

and then click send button. Response contains list of TodoItems along with their related entities as the GET request included $expand query operator.

Now, let's try updating the new TodoItem: Stationery that was inserted earlier. Go back to the main help page, click on PATCH tables/TodoItem/{id} and then click on try this out button on the top of the page. In the Uri Parameters section enter 2 for {id}. Paste following json in the sample body

 {
    "text": "Stationery updated",
    "id": "2",
    "complete": false,
    "items":[
        { "itemName" : "marker", "id" : "3" },
        { "itemName" : "pencil", "id" : "4" }
    ]
}

and then click send button. This is the PATCH request to update text for the TodoItem and associated child entities. Response body contains updated TodoItem. Here is the sample response body:

 {
    "text": "Stationery updated",
    "id": "2",
    "complete": false,
    "items":[
        { "itemName" : "marker", "id" : 3 },
        { "itemName" : "pencil", "id" : 4 }
    ]
}

Try out different patch requests to see how updates work.