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.
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<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.
Comments
Anonymous
June 26, 2014
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/rAlbertoAnonymous
July 01, 2014
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.Anonymous
July 01, 2014
For more details on how to implement many to many relationship, please see following E2E samples:code.msdn.microsoft.com/.../Field-Engineer-501df99d code.msdn.microsoft.com/.../Sales-Assistant-465dc8b5Anonymous
July 10, 2014
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?Anonymous
August 06, 2014
Following data models in FieldEngineer sample :Equipment: code.msdn.microsoft.com/.../sourcecode-Job: code.msdn.microsoft.com/.../sourcecodeHave 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.Anonymous
August 10, 2014
The comment has been removedAnonymous
August 11, 2014
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 :)Anonymous
August 22, 2014
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?Anonymous
December 24, 2014
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?Anonymous
January 08, 2015
I also got the LINQ expression type 'Invoke' is not supported in LINQ to Entities" error, what is causing that?Anonymous
January 09, 2015
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?Anonymous
February 06, 2015
How can we do the same thing with Node.js based Mobile Service?Anonymous
February 09, 2015
@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.Anonymous
March 24, 2015
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?Anonymous
March 25, 2015
@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.Anonymous
March 25, 2015
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.Anonymous
May 04, 2015
When i try to either call Query() or LookUp() I get: An unhandled exception of type 'System.StackOverflowException' occurred in mscorlib.dll Any clues?Anonymous
July 27, 2015
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 PatrickAnonymous
July 29, 2015
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 PatrickAnonymous
December 19, 2015
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.