$select and $expand support


The $select system query option allows clients to requests a limited set of information for each entity or complex type identified by the ResourcePath and other System Query Options like $filter, $top, $skip etc. The $select query option is often used in conjunction with the $expand query option, to first increase the scope of the resource graph returned ($expand) and then selectively prune that resource graph ($select).

The $expand system query option allows clients to request related resources when a resource that satisfies a particular request is retrieved.

The following model would be used for the rest of the scenarios,

public class Customer
{
    public int ID { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Order> Orders { get; set; }
}

public class SpecialCustomer : Customer
{
    public int SpecialCustomerProperty { get; set; }

    public virtual ICollection<SpecialOrder> SpecialOrders { get; set; }
}

public class Order
{
    public int ID { get; set; }

    public int Amount { get; set; }

    public Customer Customer { get; set; }
}

public class SpecialOrder : Order
{
    public int SpecialOrderProperty { get; set; }

    public virtual SpecialCustomer SpecialCustomer { get; set; }
}

private static IEdmModel GetModel()
{
    ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
    builder.EntitySet<Customer>("Customers");
    builder.EntitySet<Order>("Orders");
    return builder.GetEdmModel();
}

For example, assuming that a service exposes two entity sets ‘Customers’ and ‘Orders’ and a customer has 1..* (one-to-many) relationship with order, the following URL’s are valid,

Supported scenarios

http://server/Customers?$select=Id,Name
Response should contain only the properties Id and Name for each customer. Navigation link for the property Orders must not be present in the response.

http://server/Customers?$expand=Orders
Response should contain all the properties of Customer for each customer. It should also contain the expanded Orders for each customer along with the navigation link (depending on the metadata level though). The expanded Orders should contain all structural properties of Orders and navigation link for each navigation property on Order.

http://server/Customers?$select=Id,Name,Orders&$expand=Orders
Response should contain properties Id and Name for each customer and expanded Orders. Each expanded Order should contain all the structural properties of order and a navigation link for each navigation property of order.

http://server/Customers?$select=Orders/Id&$expand=Orders
Selection on an expanded property.

http://server/Customers?$select=Id,NS.SpecialCustomer/SpecialId
Response should contain Id property for each customer and also includes the SpecialId property for each special customer. ODataLib uri parser doesn’t support this yet.

http://server/Customers?$expand=NS.SpecialCustomer/SpecialOrder
Response should contain all structural properties and expand the SpecialOrder property for each special customer. A normal customer would have his navigation links as deferred content in the response. ODataLib uri parser doesn’t support this yet.

Not supported scenarios

http://server/Customers(42)/Address?$select=City
Selections of complex types is not supported. (OData V4 might have support for it though)

Samples

1) Supporting $select and $expand on collections through QueryableAttribute.

[Queryable]
public IQueryable<Customer> GetCustomers()
{
    return _dbContext.Customers;
}

2) Supporting $select and $expand on single entities through QueryableAttribute.

[Queryable]
public SingleResult<Customer> GetCustomer(int id)
{
    return SingleResult.Create(_dbContext.Customers.Where(c => c.ID == id);
}

3) Supporting $select and $expand on single entities when you don't have a db context (Linq2Objects).

[Queryable]
public Customer GetCustomer(int id)
{
    return _repository.Find(id);
}

4) Supporting $select and $expand with ODataQueryOptions<T>

This is more complex than usual as Request.CreateResponse doesn't have a non generic implementation. There is only Request.CreateResponse<T>. This is beign tracked with this bug
public class CustomersController : ODataController
{
    private CustomersContext _db = new CustomersContext();

    public HttpResponseMessage GetCustomers(ODataQueryOptions<Customer> query)
    {
        return CreateQueryableResponse(Request, query.ApplyTo(_db.Customers));
    }

    private static HttpResponseMessage CreateQueryableResponse(HttpRequestMessage request, IQueryable queryable)
    {
        MethodInfo mi = typeof(CustomersController).GetMethod("CreateQueryableResponseImpl", BindingFlags.Static | BindingFlags.NonPublic);
        mi = mi.MakeGenericMethod(queryable.ElementType);
        return mi.Invoke(null, new object[] { request, queryable }) as HttpResponseMessage;
    }

    private static HttpResponseMessage CreateQueryableResponseImpl<T>(HttpRequestMessage request, IQueryable<T> response)
    {
        return request.CreateResponse(response as IQueryable<T>);
    }
}

5) Supporting only select.

[Queryable(AllowedQueryOptions = AllowedQueryOptions.Select)]
public IQueryable<Customer> GetCustomers()
{
    return _dbContext.Customers;
}

6) Controlling maximum expansion depth.

[Queryable(MaxExpansionDepth = 1)]
public IQueryable<Customer> GetCustomers()
{
    return _dbContext.Customers;
}

7) Controlling allowed expandable properties.

public class CustomSelectExpandQueryValidator : SelectExpandQueryValidator
{
    private HashSet<string> _allowedExpandedProperties;

    public CustomSelectExpandQueryValidator(params string[] allowedExpandedProperties)
    {
        _allowedExpandedProperties = new HashSet<string>(allowedExpandedProperties);
    }

    public override void Validate(SelectExpandQueryOption selectExpandQueryOption, ODataValidationSettings validationSettings)
    {
        base.Validate(selectExpandQueryOption, validationSettings);

        SelectExpandClause clause = selectExpandQueryOption.SelectExpandClause;
        foreach (ExpandItem item in clause.Expansion.ExpandItems)
        {
            NavigationPropertySegment navigationPropertySegment = item.PathToNavigationProperty.LastOrDefault() as NavigationPropertySegment;
            if (navigationPropertySegment != null)
            {
                string propertyName = navigationPropertySegment.NavigationProperty.Name;
                if (!_allowedExpandedProperties.Contains(propertyName))
                {
                    throw new ODataException(String.Format("Navigation property '{0}' cannot be expanded.", propertyName));
                }
            }
        }
    }
}

Considerations and Open questions

There are several open questions that have to be discussed and concluded.

Support for other formatters

  1. Should we support $select with json and xml formatters? $expand is not important as these formatters expand by default.
  2. Should we change the behavior of these formatters to not expand by default if the incoming query contains a $expand clause?

Query optimizations

1. Should $select be optimized?
  • $select is a shape changing query. From a web API perspective, this would mean that the ObjectContent<T> has to be reconstructed. What would happen to the content headers on the ObjectContent? Should we just do a content-negotiation again and say that any content-type headers set after the action finished and before the QueryableAttribute runs would be lost?
  • An un-optimized $select would mean more memory allocation on the database.
design meeting@Mar 28th: First cut, actions returning IQueryable will support $select and $expand as it won't be a shape changing query anymore. $select will be optimized.
2. $expand has to be optimized.
  • Without an optimized $expand no-one would ever use our $expand support unless they are building on top of in-memory IQueryable.
  • How would the generated query look like? Should we just support EF or try be compatible with some other providers? (Scoping question).
  • If we do it only for EF, what is the extensibility story for custom providers?
design meeting@Mar 28th: work item: update with the queries
3. $select and $expand can be done on a single entity. In these cases there is no IQueryable returned by the action. How can we do query manipulation in this scenario? For example, http://server/Customers(42)?$select=Name maps to an web API action
public Customer GetCustomer(int key).

PageSize for expanded feeds.

  1. How can one specify page size for expanded feeds? WCF DS has a per entity-set configuration knob for page size. Should we follow their model?

Implementation details

The idea here is that a selection ($select) results in a LINQ .Select query using a wrapper class (kind of an static anonymous type) as the response. The wrapper class contains the properties that have to be included in the selection. For example, a $select involving a selection having one property uses a wrapper class that looks like this,
public class SelectExpandWrapper<TElement> 
{
    public PropertyContainer Container { get; set; }
}
internal abstract class PropertyContainer
{
        public Dictionary<string, object> ToDictionary(bool includeAutoSelected = true)
}

Also, we wanted to free the OData formatter from having to look at the $select and $expand options. To support that, the wrapper class should contain information about three things,
  1. what structural properties/navigation properties have to be serialized.
  2. what actions have to be included in the response.
  3. what navigation properties have to be expanded.

To support that the wrapper should also implement the IEdmStructuredObject interface.
public interface IEdmStructuredObject : IEdmObject
{
        bool TryGetValue(string propertyName, out object value);
}

public interface IEdmObject
{
        IEdmTypeReference GetEdmType();
}

The IEdmObject interface is just a way to get the EDM type name required for odata serialization and also provides a dynamic property accessor to separate the implementation of the Wrapper from the formatter. Users who don't like our Wrapper implementation (geared towards EF and Linq2Objects) could consider doing their own implementations.

Generated queries

There are two things to consider here.
  1. OData formatter (in the default case), requires the key properties to generate navigation links. So, even if the client did not ask for key properties explicitly in the request, they have to be fetched from the database. We should probably have an extension point here, so that people who customize link generation can fetch extra properties if they want them for link generation.
  2. OData formatter requires the type name of the entity while writing responses. If we are getting a partial object from the database (wrapper class), we have to include the type name in the wrapper class.


I am listing out the possible generated LINQ queries here for some scenarios for reference. The generated SQL queries (using EF) would be too complex in some cases. If you really want to look at them, refer to the following dump files,
  1. using Table per hierarchy strategy (TPH) which is code first default - tph_dump.txt
  2. using table per type strategy (TPT) - tpt_dump.txt
Also, if you are interested in playing around with the sample, I have the cs file here.
simple $select
uri = "~/Customers?$select=ID";
query = customers.Select<Customer, Wrapper>(c => new Wrapper<int>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    Name1 = "ID",
    Value1 = c.ID
});
Response:
{
  "__type": "NS.Customer",
  "ID": 1
}
{
  "__type": "NS.SpecialCustomer",
  "ID": 2
}
simple $expand
uri = "~/Customers?$expand=Orders";
query = customers.Select(c => new SelectExpandWrapper<Customer>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    Instance = c,
    PropertyContainer = new NamedProperty<Order>
    {
        Name = "Orders",
        Value = c.Orders.Select(o => new SelectExpandWrapper
        {
            TypeName = o is SpecialOrder ? typeNameSpecialOrder : typeNameOrder,
            Instance = o
        })
    }
});
Response:
{
  "__type": "NS.Customer",
  "ID": 1,
  "Name": "Raghu",
  "Orders": [
    {
      "__type": "NS.Order",
      "ID": 1,
      "Amount": 400
    }
  ]
}
{
  "__type": "NS.SpecialCustomer",
  "ID": 2,
  "Name": "Ram",
  "Orders": [
    {
      "__type": "NS.Order",
      "ID": 2,
      "Amount": 1000
    }
  ]
}
simple $select and $expand
uri = "~/Customers?$select=Name,Orders&$expand=Orders";
query = customers.Select(c => new SelectExpandWrapper<Customer>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    PropertyContainer = new NamedProperty<string> 
    {
        Name = "Name",
        Value = c.Name,
        Next = new NamedProperty<IEnumerable<Order>>
        {
            Name = "Orders",
            Value = c.Orders.Select<Order, Wrapper>(o => new Wrapper
            {
                TypeName = o is SpecialOrder ? typeNameSpecialOrder : typeNameOrder
                Instance = o
            }
        })
    }
});
Response:
{
  "__type": "NS.Customer",
  "Name": "Raghu",
  "Orders": [
    {
      "__type": "NS.Order",
      "ID": 1,
      "Amount": 400
    }
  ]
}
{
  "__type": "NS.SpecialCustomer",
  "Name": "Ram",
  "Orders": [
    {
      "__type": "NS.Order",
      "ID": 2,
      "Amount": 1000
    }
  ]
}
selecting properties from expanded results
uri = "~/Customers?$select=Name,Orders/ID,Orders/Amount&$expand=Orders";
query = customers.Select(c => new SelectExpandWrapper<Customer>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    PropertyContainer = new NamedProperty<string>
    {
        Name = "Name",
        Value = c.Name,
        Next = new NamedProperty<IEnumerable<SelectExpandWrapper<Order>>>
        {
            Name = "Orders",
            Value = c.Orders(o => new SelectExpandWrapper<Order>
            {
                TypeName = o is SpecialOrder ? typeNameSpecialOrder : typeNameOrder,
                PropertyContainer = new NamedProperty<int>
                {
                    Name = "ID",
                    Value = o.ID,
                    Next = new NamedProperty<int> 
                    {
                        Name = "Amount",
                        Value = o.Amount
                    }
                }
            }
    })
});
Response:
{
  "__type": "NS.Customer",
  "Name": "Raghu",
  "Orders": [
    {
      "__type": "NS.Order",
      "ID": 1,
      "Amount": 400
    }
  ]
}
{
  "__type": "NS.SpecialCustomer",
  "Name": "Ram",
  "Orders": [
    {
      "__type": "NS.Order",
      "ID": 2,
      "Amount": 1000
    }
  ]
}
expanding properties on derived types
uri = "~/Customers?$expand=NS.SpecialCustomer/SpecialOrders";
query = customers.Select(c => new SelectExpandWrapper<Customer>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    Instance = c,
    PropertyContainer = new NamedProperty<SpecialCustomer>
    {
        Name = c is SpecialCustomer ? "SpecialOrders" : null,
        Value = (c as SpecialCustomer).Orders.Select(o => new SelectExpandWrapper<Order>
        {
            TypeName = o is SpecialOrder ? typeNameSpecialOrder : typeNameOrder,
            Instance = o
        }) //requires null propagation. also don't confuse null and non-existence
    }
});
Response:
{
  "__type": "NS.Customer",
  "ID": 1,
  "Name": "Raghu"
}
{
  "__type": "NS.SpecialCustomer",
  "ID": 2,
  "Name": "Ram",
  "SpecialOrders": [
    {
      "__type": "NS.Order",
      "ID": 2,
      "Amount": 1000
    }
  ]
}
selecting properties on derived types
uri = "~/Customers?$select=Name,NS.SpecialCustomer/SpecialCustomerProperty";
query = customers.Select(c => new SelectExpandWrapper<Customer>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    PropertyContainer = new NamedProperty<string>
    {
        Name = "Name",
        Value = c.Name,
        Next = new PropertyContainer<SpecialCustomer>
        {
            Name = c is SpecialCustomer ? "SpecialCustomerProperty" : null,
            Value = (c as SpecialCustomer).SpecialCustomerProperty // requires null propagation.
        }
    }
});
Response:
{
  "__type": "NS.Customer",
  "Name": "Raghu"
}
{
  "__type": "NS.SpecialCustomer",
  "Name": "Ram",
  "SpecialCustomerProperty": 42
}
expanding properties on expanded derived types
uri = "~/Customers?$select=Name&$expand=Orders,Orders/NS.SpecialOrder/SpecialCustomer";
query = customers.Select<Customer, Wrapper>(c => new SelectExpandWrapper<Customer>
{
    TypeName = c is SpecialCustomer ? typeNameSpecialCustomer : typeNameCustomer,
    PropertyContainer = new NamedProperty<string>
    {
        Name = "Name",
        Value = c.Name,
        Next = new NamedProperty<IEnumerable<SelectExpandWrapper<Order>>>
        {
            Name = "Orders",
            Value = c.Orders.Select(o => new SelectExpandWrapper<Order>
            {
                Instance = o,
                Name = o is SpecialOrder ? "SpecialCustomer" : null, // optional property
                Value = new Wrapper
                {
                    Instance = (o as SpecialOrder).SpecialCustomer
                }
            }
        }
    })
});
Response:
{
  "__type": "NS.Customer",
  "Name": "Raghu",
  "Orders": [
    {
      "__type": null,
      "ID": 1,
      "Amount": 400
    }
  ]
}
{
  "__type": "NS.SpecialCustomer",
  "Name": "Ram",
  "Orders": [
    {
      "__type": null,
      "ID": 2,
      "Amount": 1000,
      "SpecialCustomer": {
        "__type": "NS.SpecialCustomer",
        "ID": 2,
        "Name": "Ram",
        "SpecialCustomerProperty": 42
      }
    }
  ]
}

Last edited May 9, 2013 at 9:04 PM by raghuramn, version 27

Comments

TristanKazu Aug 21, 2013 at 9:19 AM 
Expanding properties on derived types is exactly what I need for a few controllers. If I understand properly, it is already supported by Web API OData, but the ODataLib package still lacks a proper URI parser in order to cope with these URI's? Is there some workaround for this problem, until it is properly released?

Assam Jul 29, 2013 at 1:07 PM 
BTW are these types accessible any more i.e. Wrapper and SelectExpandWrapper<T>. I am using vs2013 web preview version

Assam Jul 29, 2013 at 1:04 PM 
I had the following method for converting ViewModel query to Model query and results vice versa. All works fine but unfortunately select and expand fails to work. Can you please help to make the following method work in all cases of ODataQueryOptions
public PageResult<TEntity> WebSearch<TEntity>(ODataQueryOptions opts) where TEntity : class
{
EdmDalModel model = new EdmDalModel();
ODataQueryContext queryContext = new ODataQueryContext(model.GetEdmModel(), ModelType);

var mappedQuery = new ODataQueryOptions(queryContext, opts.Request);
var results = new List<TEntity>();
//TODO: Make expand work
var r = mappedQuery.ApplyTo(ModelQueryable);

//SelectExpandWrapper
foreach (var result in r)
{
AutoMapper.Mapper.CreateMap(result.GetType(), typeof(TEntity));
results.Add(AutoMapper.Mapper.Map<TEntity>(result));
}
PageResult<TEntity> pr = new PageResult<TEntity>(results.AsEnumerable<TEntity>(), mappedQuery.Request.GetNextPageLink(), mappedQuery.Request.GetInlineCount());
return pr;
}