Retrieving data from 1:n relationship using .NET backend Azure Mobile Services
The .NET backend for Azure Mobile Services makes it easy to expose related tables via Entity Framework. However, related entities are not returned by default - for example, if you retrieve the list of orders from your orderController, it will not return the order items unless you explicitly ask for it. The way to request related entities is done via the OData $expand query operator
In this article I will walk through a sample showing how to retrieve related entities from client and service
- Setup data models with 1:n relationship on the service
- Update data models on the client to match new data models on the service
- Example on how to add expand handler on the client to query for related entities
- Example on how to update service to use ActionFilterAttribute to include related entities
Service Setup
Example in this post will use two database entities: TodoItem and Item. Each TodoItem has a list of dependent Items. For example TodoItem: Grocery has a list of Items: Milk, Eggs etc
To get started, download the Quick Start Windows Store App from the Azure Portal. Then follow steps below
Add Item class in DataObjects folder in the service project
public class Item : EntityData
{
public string ItemName { get; set; }
public string TodoItemId { get; set; }
public virtual TodoItem TodoItem { get; set; }
}
then, update TodoItem class to include list of associated Items
public class TodoItem : EntityData
{
public TodoItem()
{
Items = new List<Item>();
}
public string Text { get; set; }
public bool Complete { get; set; }
public virtual ICollection<Item> Items { get; set; }
}
Note: Entity Framework establishes one-to-many relationship between TodoItem and Item as the classes follow Code First Fluent API naming conventions
Open App_Start/WebApiConfig.cs in service project and update Seed method to populate database with sample data
List<Item> items = new List<Item>
{
new Item { Id = "1", ItemName = "Milk" },
new Item { Id = "2", ItemName = "Eggs" }
};
List<TodoItem> todoItems = new List<TodoItem>
{
new TodoItem { Id = "1", Text = "Grocery", Complete = false, Items=items }
};
Now you can build and run service project to host the service locally. You should able to query related entities on TodoItem table with http GET request: https://localhost:<port>/tables/todoItem/?$expand=items
Retrieving related entities from client
Following steps show how to update Windows Store App to query for related entities
To update data models on the client open MainPage.Xaml.cs in Windows Store App project and add the Item class as defined below
public class Item
{
public string ItemName { get; set; }
public string TodoItemId { get; set; }
}
then, update the TodoItem class to include list of associated items
public class TodoItem
{
public string Id { get; set; }
public string Text { get; set; }
public bool Complete { get; set; }
public ICollection<Item> Items { get; set; }
}
Currently, Mobile Services SDK does not have support to use $expand. So we need to add following DelegatingHandler to append $expand operator on get requests to TodoItem table. Open App.xaml.cs file in Windows Store App project and add following class
public class TodoItemExpandHandler : DelegatingHandler
{
protected override async Task<HttpResponseMessage>
SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
{
bool requestToTodoTable = request.RequestUri.PathAndQuery
.StartsWith("/tables/todoItem", StringComparison.OrdinalIgnoreCase)
&& request.Method == HttpMethod.Get;
if (requestToTodoTable)
{
UriBuilder builder = new UriBuilder(request.RequestUri);
string query = builder.Query;
if (!query.Contains("$expand"))
{
if (string.IsNullOrEmpty(query))
{
query = string.Empty;
}
else
{
query = query + "&";
}
query = query + "$expand=items";
builder.Query = query.TrimStart('?');
request.RequestUri = builder.Uri;
}
}
var result = await base.SendAsync(request, cancellationToken);
return result;
}
}
then, configure MobileServiceClient to use TodoItemExpandHandler as shown below
public static MobileServiceClient client = new MobileServiceClient(
"MobileServiceUrl", "applicationKey", new TodoItemExpandHandler()
);
The result from GET requests to the TodoItem table will now include the items associated for each todoItem entity.
Including related entities from service
Above example showed how the client can choose whether results from the service include related entities or not using OData $expand query operator on GET requests to table controller. While this approach gives the client choice on expanding the results, you have to update client on each platform include $expand on GET requests. In this section of the post I will walk through an example on how to update the service to always include associated entities. This is useful when a scenario requires to always include related entities. For example, if you retrieve the list of customers you might always want to include address for each customer. For such cases, instead of client expanding on the results, it is helpful to update the service to always return related entities. Downside of this approach is service will always include child entities even if client does not use the expanded results.
To make it easier to include OData $expand query operator on a table controller, add following ActionFilterAttribute in the service project
public class QueryableExpandAttribute : ActionFilterAttribute
{
private const string ODataExpandOption = "$expand=";
public QueryableExpandAttribute(string expand)
{
this.AlwaysExpand = expand;
}
public string AlwaysExpand { get; set; }
public override void OnActionExecuting(HttpActionContext actionContext)
{
HttpRequestMessage request = actionContext.Request;
string query = request.RequestUri.Query.Substring(1);
var parts = query.Split('&').ToList();
bool foundExpand = false;
for (int i = 0; i < parts.Count; i++)
{
string segment = parts[i];
if (segment.StartsWith(ODataExpandOption, StringComparison.Ordinal))
{
foundExpand = true;
parts[i] += "," + this.AlwaysExpand;
break;
}
}
if (!foundExpand)
{
parts.Add(ODataExpandOption + this.AlwaysExpand);
}
UriBuilder modifiedRequestUri = new UriBuilder(request.RequestUri);
modifiedRequestUri.Query = string.Join("&",
parts.Where(p => p.Length > 0));
request.RequestUri = modifiedRequestUri.Uri;
base.OnActionExecuting(actionContext);
}
}
To use QueryableExpand attribute in TodoItemContorller, open Controllers/TodoItemController.cs and update GetAllTodoItems as shown below
[QueryableExpand("Items")]
public IQueryable<TodoItem> GetAllTodoItems()
{
return Query();
}
You can now build and run Service project to host the service locally. You should able to query related entities with http GET request: https://localhost:<port>/tables/todoItem
Note: GET request does not have $expand in the query. Adding this attribute will add $expand to the query on the service which is similar to using TodoItemExpandHandler on the client
Now, remove TodoItemExpandHandler in MobileServiceClient as shown below
public static MobileServiceClient client = new MobileServiceClient(
"MobileServiceUrl", "applicationKey"
);
GET requests to TodoItemController to get all items will now include child entities
For POST and PATCH requests, see follow up post on how to insert/update related data.