Navigational DTO properties using Entity Framework with OData Queries

Question

Development environment

• ASP.NET Core 3.1
• Microsoft.EntityFrameworkCore 3.1.9
• Microsoft.AspNetCore.OData 7.5.1

Models

public class Computer
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public ICollection<Disk> Disks { get; set; }
}

public class Disk
{
    public int Id { get; set; }
    public string Letter { get; set; }
    public float Capacity { get; set; }
    
    public int? ComputerId { get; set; }
    public virtual Computer Computer { get; set; }
}

Dtos

public class ComputerDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<DiskDto> Disks { get; set; }
}

public class DiskDto
{
    public string Letter { get; set; }
    public float Capacity { get; set; }
}

EF Core Context

public class ComputerContext : DbContext
{
    public DbSet<Computer> Computers { get; set; }
    public DbSet<Disk> Disks { get; set;}
    
    public ComputerContext(DbContextOptions<ComputerContext> options)
        : base(options)
    {
        
    }   
}

OData EDM Model

private static IEdmModel GetEdmModel()
{
    var builder = new ODataConventionModelBuilder();
    
    builder.EntitySet<Computer>("Computers");   
    builder.EntitySet<Disk>("Disks");
        
    builder.ComplexType<ComputerDto>();
    builder.ComplexType<DiskDto>();

    return builder.GetEdmModel();
}

ASP.NET Core Controller

[Route("api/[controller]")]
[ApiController]
public class ComputersController : ControllerBase
{
    private readonly ComputerContext context;
    
    public ComputersController(ComputerContext context)
    {
        this.context = context;
    }
    
    [HttpGet]
    [EnableQuery]
    public IQueryable<ComputerDto> GetComputers()
    {
        return this.context.Computers.Select(c => new ComputerDto
        {
            Id = c.Id,
            Name = c.Name,
            Disks = c.Disks.Select(d => new DiskDto
            {
                Letter = d.Letter,
                Capacity = d.Capacity
            }).ToList()
        });
    }
}

This query works but Disks is already expanded because I'm manually creating the list.

https://localhost:46324/api/computers?$filter=startswith(name,'t')

and output

{
  "@odata.context": "https://localhost:46324/api/$metadata#Collection(ODataPlayground.Dtos.ComputerDto)",
  "value": [
    {
      "Id": 14,
      "Name": "TestComputer1",
      "Disks": [
        {
          "Letter": "C",
          "Capacity": 234.40
        },
        {
          "Letter": "D",
          "Capacity": 1845.30
        }
      ]
    },
    {
      "Id": 15,
      "Name": "TestComputer2",
      "Disks": [
        {
          "Letter": "C",
          "Capacity": 75.50
        },
        {
          "Letter": "D",
          "Capacity": 499.87
        }
      ]
    }
  ]
}

If I then try to expand "Disks" with the following query I get an error:

https://localhost:46324/api/computers?$filter=startswith(name,'t')&$expand=disks

error

{
    "error": {
        "code": "",
        "message": "The query specified in the URI is not valid. Property 'disks' on type 'ODataPlayground.Dtos.ComputerDto' is not a navigation property or complex property. Only navigation properties can be expanded.",
        "details": [],
        "innererror": {
            "message": "Property 'disks' on type 'ODataPlayground.Dtos.ComputerDto' is not a navigation property or complex property. Only navigation properties can be expanded.",
            "type": "Microsoft.OData.ODataException",
            "stacktrace": "...really long stack trace removed for compactness..."
        }
    }
}

Question

• I seem to be able to return the top level class as a dto, only exposing the properties a client might need but is it also possible to expose and return a dto as a navigation property?

Non-dto output

{
  "@odata.context": "https://localhost:46324/api/$metadata#Collection(ODataPlayground.Dtos.ComputerDto)",
  "value": [
    {
      "Id": 14,
      "Name": "TestComputer1",
      "Disks": [
        {
          "Id": 16,
          "ComputerId": 14,
          "Letter": "C",
          "Capacity": 234.40
        },
        {
          "Id": 17,
          "ComputerId": 14,
          "Letter": "D",
          "Capacity": 1845.30
        }
      ]
    }
  ]
}

Desired output (with the $filter and $expand query above)

{
  "@odata.context": "https://localhost:46324/api/$metadata#Collection(ODataPlayground.Dtos.ComputerDto)",
  "value": [
    {
      "Id": 14,
      "Name": "TestComputer1",
      "Disks": [
        {
          "Letter": "C",
          "Capacity": 234.40
        },
        {
          "Letter": "D",
          "Capacity": 1845.30
        }
      ]
    }
  ]
}

Update #1

If I add Automapper into the mix and try using the ProjectTo method with the following code:

    //// Inject context and mapper
    public ComputersController(ComputerContext context, IMapper mapper)
    {
        this.context = context;
        this.mapper = mapper;
    }

    [HttpGet]
    [EnableQuery]
    public IQueryable<ComputerDto> GetComputers()
    {
        return this.context.Computers.ProjectTo<ComputerDto>(mapper.ConfigurationProvider);
    }

I get a different error:

    InvalidOperationException: When called from 'VisitLambda', rewriting a node of type
    'System.Linq.Expressions.ParameterExpression' must return a non - null value of the same type.
    Alternatively, override 'VisitLambda' and change it to not visit children of this type.

Answer 1

I seem to be able to return the top level class as a dto, only exposing the properties a client might need but is it also possible to expose and return a dto as a navigation property?

It's possible, but you need to solve some modelling and implementation specific problems.

First, the modelling. OData only supports collection navigation properties to entity types. So in order to map ComputerDto.Disks property as navigation property, you need to make DiskDto entity type. Which in turn requires it to have a key. So either add Id property to it, or associate some other property (for instance, Letter) to it:

//builder.ComplexType<DiskDto>();
builder.EntityType<DiskDto>().HasKey(e => e.Letter);

Now the Disks property won't be included w/o $expand option and also will eliminate the original OData exception.

This was all about OData Edm model and enabling $expand options for Disks.

The next problem to be solved is related to OData and EF Core query implementation details. Running the filtered query (w/o $expand) produces the desired JSON output (no Disks included), but the generated EF Core SQL query is

SELECT [c].[Id], [c].[Name], [d].[Letter], [d].[Capacity], [d].[Id]
FROM [Computers] AS [c]
LEFT JOIN [Disks] AS [d] ON [c].[Id] = [d].[ComputerId]
WHERE (@__TypedProperty_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT([c].[Name], LEN(@__TypedProperty_0)) = @__TypedProperty_0))
ORDER BY [c].[Id], [d].[Id]

As you can see, it includes unnecessary joins and columns, which is inefficient.

And with $expand options you get the VisitLambda exception, which is from EF Core 3.1 query translation pipeline and is caused by the ToList() call in the Disks member projection, which in turn is needed because the target property type is ICollection<DiskDto> and w/o it you get compile time error. It can be solved by making the property type IEnumerable<DiskDto> and removing the ToList() from projection, which would eliminate the exception, but again will produce the even more inefficient SQL query

SELECT [c].[Id], [c].[Name], [d].[Letter], [d].[Capacity], [d].[Id], @__TypedProperty_2, [d0].[Letter], [d0].[Capacity], CAST(1 AS bit), [d0].[Id]
FROM [Computers] AS [c]
LEFT JOIN [Disks] AS [d] ON [c].[Id] = [d].[ComputerId]
LEFT JOIN [Disks] AS [d0] ON [c].[Id] = [d0].[ComputerId]
WHERE (@__TypedProperty_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT([c].[Name], LEN(@__TypedProperty_0)) = @__TypedProperty_0))
ORDER BY [c].[Id], [d].[Id], [d0].[Id]

All that means is that trying to use OData query directly over EF Core projection query is problematic.

So as a solution for the implementation problems I would suggest AutoMapper.Extensions.OData extension which:

Creates LINQ expressions from ODataQueryOptions and executes the query.

What you need is to install the package AutoMapper.AspNetCore.OData.EFCore, use AutoMapper configuration similar to this (the essential is to enable null collections and explicit expansion)

cfg.AllowNullCollections = true;
cfg.CreateMap<Computer, ComputerDto>()
    .ForAllMembers(opt => opt.ExplicitExpansion());
cfg.CreateMap<Disk, DiskDto>()
    .ForAllMembers(opt => opt.ExplicitExpansion());

(note: with this approach the property type can stay ICollection<DiskDto>)

and change the controller method similar to this (the essential is to not use EnableQuery, add options argument and return IEnumerable / ICollection instead of IQueryable)

using AutoMapper.AspNet.OData;

[HttpGet]
public async Task<IEnumerable<ComputerDto>> GetComputers(
    ODataQueryOptions<ComputerDto> options) =>
    await context.Computers.GetAsync(mapper, options, HandleNullPropagationOption.False);

Now both outputs will be as expected, as well as the generated SQL queries:

• (no expand)

output:

{
    "@odata.context": "https://localhost:5001/api/$metadata#Collection(ODataTest.Dtos.ComputerDto)",
    "value": [
        {
            "Id": 1,
            "Name": "TestComputer1"
        },
        {
            "Id": 2,
            "Name": "TestComputer2"
        }
    ]
}

SQL query:

SELECT [c].[Id], [c].[Name]
FROM [Computers] AS [c]
WHERE [c].[Name] IS NOT NULL AND ([c].[Name] LIKE N't%')

• with $expand=disks

output:

{
    "@odata.context": "https://localhost:5001/api/$metadata#Collection(ODataTest.Dtos.ComputerDto)",
    "value": [
        {
            "Id": 1,
            "Name": "TestComputer1",
            "Disks": [
                {
                    "Letter": "C",
                    "Capacity": 234.4
                },
                {
                    "Letter": "D",
                    "Capacity": 1845.3
                }
            ]
        },
        {
            "Id": 2,
            "Name": "TestComputer2",
            "Disks": [
                {
                    "Letter": "C",
                    "Capacity": 75.5
                },
                {
                    "Letter": "D",
                    "Capacity": 499.87
                }
            ]
        }
    ]
}

SQL query:

SELECT [c].[Id], [c].[Name], [d].[Id], [d].[Capacity], [d].[ComputerId], [d].[Letter]
FROM [Computers] AS [c]
LEFT JOIN [Disks] AS [d] ON [c].[Id] = [d].[ComputerId]
WHERE [c].[Name] IS NOT NULL AND ([c].[Name] LIKE N't%')
ORDER BY [c].[Id], [d].[Id]