Using DTO's with OData & Web API

Question

Using Web API and OData, I have a service which exposes Data Transfer Objects instead of the Entity Framework entities.

I use AutoMapper to transform the EF Entities into their DTO counter parts using ProjectTo():

public class SalesOrdersController : ODataController
{
    private DbContext _DbContext;

    public SalesOrdersController(DbContext context)
    {
        _DbContext = context;
    }

    [EnableQuery]
    public IQueryable<SalesOrderDto> Get(ODataQueryOptions<SalesOrderDto> queryOptions)
    {
        return _DbContext.SalesOrders.ProjectTo<SalesOrderDto>(AutoMapperConfig.Config);
    }

    [EnableQuery]
    public IQueryable<SalesOrderDto> Get([FromODataUri] string key, ODataQueryOptions<SalesOrderDto> queryOptions)
    {
        return _DbContext.SalesOrders.Where(so => so.SalesOrderNumber == key)
                            .ProjectTo<SalesOrderDto>(AutoMapperConfig.Config);
    }
}

AutoMapper (V4.2.1) is configured as follows, note the ExplicitExpansion() which prevents serialisation auto expanding navigation properties when they are not requested:

cfg.CreateMap<SalesOrderHeader, SalesOrderDto>()                
            .ForMember(dest => dest.SalesOrderLines, opt => opt.ExplicitExpansion());

cfg.CreateMap<SalesOrderLine, SalesOrderLineDto>()
            .ForMember(dest => dest.MasterStockRecord, opt => opt.ExplicitExpansion())
            .ForMember(dest => dest.SalesOrderHeader, opt => opt.ExplicitExpansion());

ExplicitExpansion() then creates a new problem where the following request throws an error:

/odatademo/SalesOrders('123456')?$expand=SalesOrderLines

The query specified in the URI is not valid. The specified type member 'SalesOrderLines' is not supported in LINQ to Entities

The navigation property SalesOrderLines is unknown to EF so this error is pretty much what I expected to happen. The question is, how do I handle this type of request?

The ProjectTo() method does have an overload that allows me to pass in an array of properties that require expansion, I found & modified the extension method ToNavigationPropertyArray to try and parse the request into a string array:

[EnableQuery]
public IQueryable<SalesOrderDto> Get([FromODataUri] string key, ODataQueryOptions<SalesOrderDto> queryOptions)
{
    return _DbContext.SalesOrders.Where(so => so.SalesOrderNumber == key)
            .ProjectTo<SalesOrderDto>(AutoMapperConfig.Config, null, queryOptions.ToNavigationPropertyArray());
}

public static string[] ToNavigationPropertyArray(this ODataQueryOptions source)
{
    if (source == null) { return new string[]{}; }

    var expandProperties = string.IsNullOrWhiteSpace(source.SelectExpand?.RawExpand) ? new List<string>().ToArray() : source.SelectExpand.RawExpand.Split(',');

    for (var expandIndex = 0; expandIndex < expandProperties.Length; expandIndex++)
    {
        // Need to transform the odata syntax for expanding properties to something EF will understand:

        // OData may pass something in this form: "SalesOrderLines($expand=MasterStockRecord)";                
        // But EF wants it like this: "SalesOrderLines.MasterStockRecord";

        expandProperties[expandIndex] = expandProperties[expandIndex].Replace(" ", "");
        expandProperties[expandIndex] = expandProperties[expandIndex].Replace("($expand=", ".");
        expandProperties[expandIndex] = expandProperties[expandIndex].Replace(")", "");
    }

    var selectProperties = source.SelectExpand == null || string.IsNullOrWhiteSpace(source.SelectExpand.RawSelect) ? new List<string>().ToArray() : source.SelectExpand.RawSelect.Split(',');

    //Now do the same for Select (incomplete)          
    var propertiesToExpand = expandProperties.Union(selectProperties).ToArray();

    return propertiesToExpand;
}

This works for expand, so now I can handle a request like the following:

/odatademo/SalesOrders('123456')?$expand=SalesOrderLines

or a more complicated request like:

/odatademo/SalesOrders('123456')?$expand=SalesOrderLines($expand=MasterStockRecord)

However, more complicated request that try to combine $select with $expand will fail:

/odatademo/SalesOrders('123456')?$expand=SalesOrderLines($select=OrderQuantity)

Sequence contains no elements

So, the question is: am I approaching this the right way? It feels very smelly that I would have to write something to parse and transform the ODataQueryOptions into something EF can understand.

It seems this is a rather popular topic:

• odata-expand-dtos-and-entity-framework
• how-to-specify-the-shape-of-results-with-webapi2-odata-with-expand
• web-api-queryable-how-to-apply-automapper
• how-do-i-map-an-odata-query-against-a-dto-to-another-entity

While most of these suggest using ProjectTo, none seem to address serialisation auto expanding properties, or how to handle expansion if ExplictExpansion has been configured.

Classes and Config below:

Entity Framework (V6.1.3) entities:

public class SalesOrderHeader
{
    public string SalesOrderNumber { get; set; }
    public string Alpha { get; set; }
    public string Customer { get; set; }
    public string Status { get; set; }
    public virtual ICollection<SalesOrderLine> SalesOrderLines { get; set; }
}

public class SalesOrderLine
{
    public string SalesOrderNumber { get; set; }
    public string OrderLineNumber { get; set; }        
    public string Product { get; set; }
    public string Description { get; set; }
    public decimal OrderQuantity { get; set; }

    public virtual SalesOrderHeader SalesOrderHeader { get; set; }
    public virtual MasterStockRecord MasterStockRecord { get; set; }
}

public class MasterStockRecord
{        
    public string ProductCode { get; set; }     
    public string Description { get; set; }
    public decimal Quantity { get; set; }
}

OData (V6.13.0) Data Transfer Objects:

public class SalesOrderDto
{
    [Key]
    public string SalesOrderNumber { get; set; }
    public string Customer { get; set; }
    public string Status { get; set; }
    public virtual ICollection<SalesOrderLineDto> SalesOrderLines { get; set; }
}

public class SalesOrderLineDto
{
    [Key]
    [ForeignKey("SalesOrderHeader")]
    public string SalesOrderNumber { get; set; }

    [Key]
    public string OrderLineNumber { get; set; }
    public string LineType { get; set; }
    public string Product { get; set; }
    public string Description { get; set; }
    public decimal OrderQuantity { get; set; }

    public virtual SalesOrderDto SalesOrderHeader { get; set; }
    public virtual StockDto MasterStockRecord { get; set; }
}

public class StockDto
{
    [Key]
    public string StockCode { get; set; }        
    public string Description { get; set; }        
    public decimal Quantity { get; set; }
}

OData Config:

var builder = new ODataConventionModelBuilder();

builder.EntitySet<StockDto>("Stock");
builder.EntitySet<SalesOrderDto>("SalesOrders");
builder.EntitySet<SalesOrderLineDto>("SalesOrderLines");

Answer 1

I have created an Automapper explicit navigation expansion utility function that should work with N-deph expands. Posting it here since it might help someone.

public List<string> ProcessExpands(IEnumerable<SelectItem> items, string parentNavPath="")
{
    var expandedPropsList = new List<String>();
    if (items == null) return expandedPropsList;

    foreach (var selectItem in items)
    {
        if (selectItem is ExpandedNavigationSelectItem)
        {
            var expandItem = selectItem as ExpandedNavigationSelectItem;
            var navProperty = expandItem.PathToNavigationProperty?.FirstSegment?.Identifier;

            expandedPropsList.Add($"{parentNavPath}{navProperty}");                    
            //go recursively to subproperties
            var subExpandList = ProcessExpands(expandItem?.SelectAndExpand?.SelectedItems, $"{parentNavPath}{navProperty}.");
            expandedPropsList =  expandedPropsList.Concat(subExpandList).ToList();
        }
    }
    return expandedPropsList;
}

You can call it with :

var navExp = ProcessExpands(options?.SelectExpand?.SelectExpandClause?.SelectedItems)

it will return a list with ["Parent" ,"Parent.Child"]

Answer 2

I never really managed to work this one out. The ToNavigationPropertyArray() extension method helps a little, but does not handle infinite depth navigation.

The real solution is to create Actions or Functions to allow clients to request data requiring a more complicated query.

The other alternative is to make multiple smaller/simple calls then aggregate the data on the client, but this isn't really ideal.

Answer 3

When you want to mark something for explicit expansion in AutoMapper, you need to also opt-back-in when calling ProjectTo<>().

// map
cfg.CreateMap<SalesOrderHeader, SalesOrderDto>()                
   .ForMember(dest => dest.SalesOrderLines, opt => opt.ExplicitExpansion());

// updated controller
[EnableQuery]
public IQueryable<SalesOrderDto> Get()
{
    return _dbContext.SalesOrders
        .ProjectTo<SalesOrderDto>(
            AutoMapperConfig.Config, 
            so => so.SalesOrderLines,
            // ... additional opt-ins
        );
}

While the AutoMapper wiki does state this, the example is perhaps a little misleading by not including the paired ExplicitExpansion() call.

To control which members are expanded during projection, set ExplicitExpansion in the configuration and then pass in the members you want to explicitly expand: