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.
Comments
- Anonymous
May 31, 2014
Excellent post. What would you recommend to implement an hybrid scenario e.g a scenario in which some data is always expanded and some in only expanded on demand? To make my question clearer: In some cases I'd like to expand data directly on the server (customer and customer orders), while sometimes I want to specifically have to request expanded data due to size (images of customer radiographs for instance). Is there a good way to implement an on demand switch or would you recommend to just code everything to be expanded by the client on demand?Cheers,Alberto - Anonymous
June 02, 2014
Yes. You can implement hybrid scenario where the service will include associated entities you always need and on the client you can choose to expand on demand. For example on the client you can use two instances for MobileServiceClient one with an expandHandler and the other without:public static MobileServiceClient client = new MobileServiceClient( "MobileServiceUrl", "applicationKey");public static MobileServiceClient clientWithExpandHandler = new MobileServiceClient( "MobileServiceUrl", "applicationKey", new <DelegatingExpandHandler>);When you need to expand on the client use MobileServiceClient that has expand handler :var todoTable = clientWithExpandHandler.GetTable<TodoItem>(); - Anonymous
June 05, 2014
Ok for retrieving relational data.But what if you want to Post it like just like you retrieve it?I mean sending:var todoItem = new TodoItem{ Id = "1", Text = "Grocery", Complete = false, Items=new List<Item> { new Item { Id = "1", ItemName = "Milk" }, new Item { Id = "2", ItemName = "Eggs" } }};todoItemTable.InsertAsync(todoItem);Does it insert both TodoItem and Item? - Anonymous
June 11, 2014
Thank you Pragna - Anonymous
June 23, 2014
For POST and PATCH requests, see follow up post on how to insert/update related data: blogs.msdn.com/.../insert-update-related-data-with-1-n-relationship-using-net-backend-azure-mobile-services.aspx - Anonymous
July 17, 2014
Hi Pragna, I've managed to implement your code (QueryableExpandAttribute ) in a test project against 2 tables, but when I add a third, the related data isn't loading. Eg, Questionnaire >> Questions >> AnswersDo you know if this is possible? Thanks - Anonymous
November 12, 2014
Hi, Any ideas about Craig Barrie problem?, I have the same scenario, I need retrieve third o more levels of child relations - Anonymous
January 22, 2015
Been fighting this all day:The type or namespace name 'QueryableExpandAttribute' could not be found (are you missing a using directive or an assembly reference?)Where can this be found!? - Anonymous
January 23, 2015
It's defined in the post above, in the section "Including related entities from service"