OData controller doesn't return data from joined (included) tables

Razzupaltuff 0
Viewed 31
    public class Account
    {
        [Key]
        [StringLength(80)]
        public string AccountID { get; set; } = string.Empty;

        [StringLength(255)]
        public string Name { get; set; } = string.Empty;

        [StringLength(255)]
        public string Email { get; set; } = string.Empty;

        [ForeignKey(nameof(AccountID))]
        public virtual ICollection<Relation> Relations { get; set; }
    }

    public class Role
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long RoleID { get; set; } = -1;

        [StringLength(80)]
        public string RoleName { get; set; } = string.Empty;
    }

    public class Relation
    {
        [Key]
        [StringLength(80)]
        public string AccountID { get; set; } = string.Empty;

        [Required]
        public long RoleID { get; set; } = 0;

        [ForeignKey(nameof(RoleID))]
        public virtual Role Role { get; set; }
    }

    public class AccountsController : ODataController
    {
        private readonly DS2DbContext _context;

        public AccountsController(DS2DbContext context)
        {
            _context = context;
        }

        [EnableQuery(PageSize = 15)]
        public IQueryable<Account> Get()
        {
            var q = _context.accounts
                    .Include(e => e.Relations)
                        .ThenInclude(e => e.Role)
                   ;
            // debug code
            var l = q.ToList();
            var i = l.FirstOrDefault();
            return q;
        }
    }

The test code shows that the data from the included tables is being read, but it is not sent to the client:

{@odata.context: "https://localhost:44393/DS/$metadata#Accounts", @odata.count: 2,…}
    @odata.context: "https://localhost:44393/DS/$metadata#Accounts"
    @odata.count: 2
    value: [,…]
        0: {AccountID: "0", Firstname: "A", Lastname: "H", Email: "a.h@b.g",…}
            AccountID: "0"
            Department: "WD"
            Email: "a.h@b.g"
            Firstname: "A"
            Lastname: "H"
        1: {AccountID: "1", Firstname: "D", L: "M", Email: "d.m@b.g",…}
            AccountID: "1"
            Department: "WD"
            Email: "d.m@b.g"
            Firstname: "D"
            Lastname: "M"

The data from Account.Relations and subsequently from Relation.Role is missing. What do I need to change or add to make this work?

odata include entity-framework-core join
1 Answers

I have found the solution myself. Instead of including tables in the controller, you let the OData framework do the work by passing $expand parameters to it. So in my case, I need to add

?$expand=Relations($expand=Role)

to the server request. This will do a "cascading" include, where for each Account entity all Relations are read from the relations table and each Relation's Role property is read from the roles table.

The controller's Get() method simply has to return _context.accounts:

[EnableQuery(PageSize = 15)]
public IQueryable<Account> Get()
{
    return _context.accounts;
}
Razzupaltuff0
Related