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 { getset; }
    public bool Complete { getset; }
    public virtual ICollection<ItemDTO> Items { getset; }
}
public class ItemDTO : EntityData
{
    public string ItemName { getset; }
}

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 { getset; }
    public string Id { getset; }
    public bool Complete { getset; }
    public virtual ICollection<Item> Items { getset; }
}
public class Item
{
    public string ItemName { getset; }
    public string Id { getset; }
    public string TodoItemId { getset; }
    public virtual TodoItem TodoItem { getset; }
}

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<TodoItemTodoItemDTO>()
        .ForMember(todoItemDTO => todoItemDTO.Items,
                            map => map.MapFrom(todoItem => todoItem.Items));
    cfg.CreateMap<TodoItemDTOTodoItem>()
            .ForMember(todoItem => todoItem.Items, 
                        map => map.MapFrom(todoItemDTO => todoItemDTO.Items));
 
    cfg.CreateMap<ItemItemDTO>();
    cfg.CreateMap<ItemDTOItem>();
});

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.

Update table controller to handle related entities

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<TodoItemDTOTodoItem>(updatedpatchEntity, currentTodoItem);
        updatedItems = updatedpatchEntity.Items;
    }
    else
    {
        //If request doest not have Items, then retain the original association
        TodoItemDTO todoItemDTOUpdated = Mapper.Map<TodoItemTodoItemDTO>
                                        (currentTodoItem);
        patch.Patch(todoItemDTOUpdated);
        Mapper.Map<TodoItemDTOTodoItem>(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<ItemDTOItem>(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<TodoItemTodoItemDTO>(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.


Comments (20)

  1. Alberto says:

    Nice write-up! How would you go about mapping many to many relationships though?

    I have a scenario where users can belong to multiple organizations.

    The following code causes a stack overflow at return Query() in the controller:

    //User to UserDTO

                   cfg.CreateMap<User, UserDTO>()

                                       .ForMember(UserDTO => UserDTO.Clinics,

                                                           map => map.MapFrom(User => User.Clinics))

                                       .ForMember(UserDTO => UserDTO.Organizations,

                                                           map => map.MapFrom(User => User.Organizations))

                                       .ForMember(UserDTO => UserDTO.Roles,

                                                           map => map.MapFrom(User => User.Roles))

                                       .ForMember(UserDTO => UserDTO.CurrentClinic,

                                                           map => map.MapFrom(User => User.CurrentClinic))

                                       .ForMember(UserDTO => UserDTO.CurrentOrganization,

                                                           map => map.MapFrom(User => User.CurrentOrganization));

                   cfg.CreateMap<UserDTO, User>()

                                      .ForMember(User => User.Clinics,

                                                          map => map.MapFrom(UserDTO => UserDTO.Clinics))

                                      .ForMember(User => User.Organizations,

                                                          map => map.MapFrom(UserDTO => UserDTO.Organizations))

                                      .ForMember(User => User.Roles,

                                                          map => map.MapFrom(UserDTO => UserDTO.Roles))

                                       .ForMember(User => User.CurrentClinic,

                                                           map => map.MapFrom(UserDTO => UserDTO.CurrentClinic))

                                       .ForMember(User => User.CurrentOrganization,

                                                           map => map.MapFrom(UserDTO => UserDTO.CurrentOrganization));

                   cfg.CreateMap<Organization, OrganizationDTO>();

                   cfg.CreateMap<OrganizationDTO, Organization>();

                   cfg.CreateMap<User, UserDTO>();

                   cfg.CreateMap<UserDTO, User>();

    V/r

    Alberto

  2. Pragna Gopa says:

    Make sure there are no circular dependencies between parentDTO and childDTO. Also, remove following lines:  

    cfg.CreateMap<User, UserDTO>();

    cfg.CreateMap<UserDTO, User>();

    as you have already created mappings for these above.

  3. Pragna Gopa says:

    For more details on how to implement many to many relationship, please see following E2E samples:

  4. pfFredd says:

    Has anyone been able to make a many-to-many relationship like this that works? I can't seem to get around the circular reference problem as there has to be an ICollection<> in each of the classes containing the related classes from the other one. I've tried the different fixes suggested here (code.msdn.microsoft.com/…/Reviews) with no luck.

    Any suggestions?

  5. Pragna Gopa says:

    Following data models in FieldEngineer sample :

    -Job: code.msdn.microsoft.com/…/sourcecode

    Have many-many relationship. Can you please take a look at these and see if it helps? Also, it would help if can share an example of data models you are using.

  6. SteveS says:

    Hey great post here, I've been following your series. I'm a bit confused at the moment as I can't seem to get your AzureMobile.BlogSamples.Service project to execute a GET request by id. I keep getting "System.Reflection.TargetInvocationException". Was wondering if you had any words of insight here. I am new to this framework and would really like to become familiar with it. Your post seems to be the only one on the internet that is worthwhile and uses real world examples…

  7. pfFredd says:

    Hi again Pragna,

    Thanks for the response. After connecting the mobile service to an existing sql database and following the FieldEngineer sample it seems to work fine! Thanks 🙂

  8. Ebsan says:

    What is the point of the SimpleMappedEntityDomainManager? Is it required for mapping using AutoMapper? Also, SMEDM only has Lookup, Update, and DeleteAsync methods. Should we add an InsertAsync method?

  9. error with query .../table/todoitem/1 says:

    with debug tool, find the error inside:  Message = "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."  Just download the github source code and run it.  Any idea?

  10. also getting "The LINQ expression node type..." error message says:

    I also got the LINQ expression type 'Invoke' is not supported in LINQ to Entities" error, what is causing that?

  11. Uriel says:

    When dealing with many-to-many, how should I add a new entry? What I mean, say we have Class has many Students and Student has many Classes, if I want to send a POST request to add Student with Id 1a2b to Class with Id aabbcc, at which endpoint should I send the request? How should my request body look like?

  12. Attiqe Ur Rehman says:

    How can we do the same thing with Node.js based Mobile Service?

  13. @Attique, you'll need to update the logic on the server scripts (insert / update / delete / read) to deal with the relationships. The posts at blogs.msdn.com/…/complex-types-and-azure-mobile-services.aspx and blogs.msdn.com/…/supporting-complex-types-in-azure-mobile-services-clients-implementing-1-n-table-relationships.aspx have some examples of how this can be implemented.

  14. Chris says:

    Hi Carlos,

    When trying to fetch a TodoItem by ID, an exception is being thrown. This has also been reported by several other users above, do you know why this is happening?

  15. @Chris, without more details it's really hard to know what is going on. I'd suggest you post your problem in the MSDN forums (social.msdn.microsoft.com/…/azuremobile) or stack overflow (stackoverflow.com/…/azure-mobile-services) and you'll likely get better responses.

  16. Chris says:

    Basically all I'm doing is running the sample solution in the InsertUpdateRelatedData folder in debug mode, and peforming a GET on /tables/todoitem/1. No changes have been made to the solution.

    This results in an exception: The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

    Anyway I'll post this question on stackoverflow and the MSDN forums as well.

  17. Christian says:

    When i try to either call Query() or LookUp() I get:

    An unhandled exception of type 'System.StackOverflowException' occurred in mscorlib.dll

    Any clues?

  18. Patrick says:

    Why is it not necessary that the database types do not inherit from entitydata? how does the system detect conflicts without a version column for example?

    thx and best regards

    Patrick

  19. Patrick says:

    I researched this further. It looks like it is absolutely necessary to implement ITableData or better inherit from EntityData. This is stated here: azure.microsoft.com/…/mobile-services-dotnet-backend-use-existing-sql-database

    The Version and ID Fields are for identifying (ID) the EntityFFramework Entities and for Concurrency Checking (Version). For this the Version is decorated with the Timestamp Attribute that is automatically used for concurrency checking by Entity Framework. see here: msdn.microsoft.com/…/gg193958.aspx (Timestamp).

    Best Regards

    Patrick

  20. Alan says:

    Hi, like some others on this thread, I'm getting 'The LINQ expression node type 'Invoke' is not supported in LINQ to Entities' when trying to get a single result using  /tables/todoitem/1 with the stock downloaded solution.

    Has anyone found the solution to this?  I've been getting nowhere for days.