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

  1. Setup data models with 1:n relationship on the service
  2. Update data models on the client to match new data models on the service
  3. Example on how to add expand handler on the client to query for related entities
  4. 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 { getset; }
    public string TodoItemId { getset; }
    public virtual TodoItem TodoItem { getset; }
}

then, update TodoItem class to include list of associated Items

public class TodoItem : EntityData
{
    public TodoItem()
    {
        Items = new List<Item>();
    }
    public string Text { getset; }
    public bool Complete { getset; }
    public virtual ICollection<Item> Items { getset; }
}

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: http://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 { getset; }
    public string TodoItemId { getset; }
}

then, update the TodoItem class to include list of associated items

public class TodoItem
{
    public string Id { getset; }
    public string Text { getset; }
    public bool Complete { getset; }
    public ICollection<Item> Items { getset; }
}

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 { getset; }
 
    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: http://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 (9)
  1. Alberto Bonfiglio says:

    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

  2. Pragna Gopa says:

    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>();

  3. Jeremy BP says:

    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?

  4. Alberto Bonfiglio says:

    Thank you Pragna

  5. Craig Barrie says:

    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 >> Answers

    Do you know if this is possible?  

    Thanks

  6. Ciani Afonso says:

    Hi, Any ideas about Craig Barrie problem?, I have the same scenario, I need retrieve third o more levels of child relations

  7. Brian says:

    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!?

  8. It's defined in the post above, in the section "Including related entities from service"

Comments are closed.

Skip to main content