OData ASP.Net Core Get Related Entities

Question

I've got an OData Beta + ASP.Net Core + EF project. I'm trying to work out the OData controller functions and I'm getting an error trying to return the related entities:

Models / Customer.cs

public class Customer
{
    public int Id { get; set; }
    public string Standing { get; set; }

    public List<Person> People { get; set; }
    public List<Address> Addresses { get; set; }
}

CustomersController.cs

using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Bookings_Server.EF;
using Bookings_Server.OData.Models;
using Microsoft.AspNet.OData;
using Microsoft.AspNet.OData.Routing;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Http;

namespace Bookings_Server.OData.Controllers
{
    [Produces("application/json")]
    [ODataRoutePrefix("customers")]
    public class CustomersController : ODataController
    {
        private readonly DataContext _context;

        public CustomersController(DataContext context)
        {
            _context = context;
        }

[EnableQuery]
[ODataRoute("({key})/people")]
public IQueryable<Customer> GetPeople([FromODataUri] int key)
{
    var result = _context.Customers.Where(m => m.Id == key).Select(m => m.People).ToList();
    return (result);
}
}

I'm getting an intellisense error under the result variable (within the return) stating:

Cannot implicitly convert type 'System.Collections.Generic.List<System.Collections.Generic.List<Bookings_Server.OData.Models.Person>>' to 'System.Linq.IQueryable<Bookings_Server.OData.Models.Customer>'. An explicit conversion exists (are you missing a cast?)

I've been looking at other OData V4 examples but they all throw implicit convert errors (assuming this is the difference of working on Aps.Net Core).

Answer 1

First of all: you need to understand the difference between an IQueryable<T>, which represents a (normally) database query, with an IEnumerable<T>, which represents an in-memory collection or data source. So:

// WRONG
public IQueryable<Customer> GetPeople([FromODataUri] int key)

// CORRECT
public IEnumerable<Customer> GetPeople([FromODataUri] int key) 

You should never return to outside your application an Entity Framework query.

Second, you want to include data from related entities, not select that data. So:

// WRONG
return _context.Customers
    .Where(m => m.Id == key)
    .Select(m => m.People) // "only give me People data"
    .ToList();

// CORRECT
return _context.Customers
    .Where(m => m.Id == key)
    .Include(m => m.People) // "give me Customer WITH People data"
    .ToList();

Mix these constructs and you end up with:

[EnableQuery]
[ODataRoute("customers({key})/people")]
public IEnumerable<Customer> GetPeople([FromODataUri] int key)
{
    return _context.Customers
        .Where(m => m.Id == key)
        .Include(m => m.People)
        .ToList();
}

One more thing to note, is that you should always use the asynchronous versions of Entity Framework Core's data access methods:

[EnableQuery]
[ODataRoute("customers({key})/people")]
public async Task<IEnumerable<Customer>> GetPeople([FromODataUri] int key)
{
    return await _context.Customers
        .Where(m => m.Id == key)
        .Include(m => m.People)
        .ToListAsync();
}

As a last comment, you should prefer returning the built-in IActionResult that allows you to easily change the response without having to throw exceptions:

[EnableQuery]
[ODataRoute("customers({key})/people")]
public async Task<IActionResult> GetPeople([FromODataUri] int key)
{
    var customers = await _context.Customers
        .Where(m => m.Id == key)
        .Include(m => m.People)
        .ToListAsync();

    // this is only an example
    if (!customers.Any())
    {
        return NotFound();
    }

    return Ok(customers);
}