ASP.Net Core Web API - ICollection not showing up in JSON resultset

Question

I am trying to execute 3 procs in order to build the customer object and return it to the client. However, inside my api(server side) I can see the result correctly. But, unfortunately, the ICollection data is missing in the resultset (client-side)

I am using Entity Framework and OData

So, here is my model:

  public class Customer
    {
        [Key]
        public Guid Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string VatCode { get; set; }
        public string ChamberOfCommerceCode { get; set; }
        public DateTime Modified { get; set; }
        public DateTime Created { get; set; }
        public string LanguageCode { get; set; }
        public decimal Discount { get; set; }
        public string CustomerManager { get; set; }
        public Guid PriceList { get; set; }
        public Guid PaymentCondition { get; set; }
       // public bool VatLiable { get; set; }
        public bool IsBlocked { get; set; }
        public bool IsProspect { get; set; }
        public bool IsSuspect { get; set; }
        public string Website { get; set; }
        public string DashboardUrl { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
        public ICollection<FreeFields> FreeFields { get; set; }
  //      [NotMapped]
      //  public Dictionary<string, string> UknownElements { get; set; }
        [ForeignKey(nameof(Contacts.Customer))]
        public ICollection<Contacts> Contact { get; set; }
        [ForeignKey(nameof(Addresses.Customer))]
        public ICollection<Addresses> Address { get; set; }
    }

As you can see, we have 2 ICollections in there: Contacts and Addresses. Here the models of those two:

Contacts model

public class Contacts
    {
        [Key]
        public Guid Id { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Initials { get; set; }
        public string Function { get; set; }
        [Column("Customer")]
        public Guid Customer { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Mobile { get; set; }
        public string LanguageCode { get; set; }
        public bool IsMainContact { get; set; }
        public string Gender { get; set; }
        public string Username { get; set; }
    }

and Addresses

 public class Addresses
    {
        [Key]
        public Guid Id { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string AddressLine3 { get; set; }
        public string PostalCode { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string CountryCode { get; set; }
        public string Type { get; set; }
        [Column("Customer")]
        public Guid Customer { get; set; }// This Property should be GUID instead of String..
        public bool IsMainAddress { get; set; }
        public string Route { get; set; }
        public string State { get; set; }
    }

Now, inside my controller I fetch the data from the Stored Procedures and create the model:

  public IList<Customer> GetAllCustomers()
        {
            //Initialize the objects
            IList<Customer> customers = null;
            ICollection<Contacts> contacts = null;
            ICollection<Addresses> addresses = null;
            Dictionary<string, string> thisdict = null;
            //Fetch the data from stored procedures
            customers = db.Customers.FromSql("SIP_API_MONDIA_Customers_sel").ToList();
            contacts = db.Contacts.FromSql("SIP_API_MONDIA_Contacts_sel").ToList();
            addresses = db.Addresses.FromSql("SIP_API_MONDIA_Address_sel").ToList();

            //Loop through customers and add the contact and addresses when required
            foreach(var item in customers)
            {
                item.Contact = contacts.Where(x => x.Customer == item.Id).ToList();
                item.Address = addresses.Where(x => x.Customer == item.Id).ToList();
             //   item.UknownElements = thisdict;

            }
            return customers;
        }

Tihs works just fine, I can see with the breakpoint that the Customers object is filled. Also the Addresses and contacts are correct. But then, once I return it to the client the result is missing both ICollections..

I feel like I am missing one small detail but I fail to find it.

Answer 1

So first of all, if you follow naming conventions you don't need to explicitly use attributes. Key attribute can be safely removed. ForeignKey attribute can be removed if you change name of properties to plural names: Contacts and Addresses. I highly encourage you to use singular names for classes and plural names for collection navigation properties (for example in One-To-Many relationship).

// Contact is name of class, plural name for property: Contacts
public ICollection<Contact> Contacts { get; set; }

If you are using OData you should build your EDM model (wchich you didn't show us). Also don't use stored procedures for simple SELECT if you have access to DbContext. Your whole controller logic is unnecessary and can be replaced just with:

[EnableQuery]
public IQueryable<Customer> GetAllCustomers()
{
    return db.Customers;
}

So in code it can look like:

Customer class

public class Customer
{
    public Guid Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
    public string VatCode { get; set; }
    public string ChamberOfCommerceCode { get; set; }
    public DateTime Modified { get; set; }
    public DateTime Created { get; set; }
    public string LanguageCode { get; set; }
    public decimal Discount { get; set; }
    public string CustomerManager { get; set; }
    public Guid PriceList { get; set; }
    public Guid PaymentCondition { get; set; }
    public bool IsBlocked { get; set; }
    public bool IsProspect { get; set; }
    public bool IsSuspect { get; set; }
    public string Website { get; set; }
    public string DashboardUrl { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
    public ICollection<FreeFields> FreeFields { get; set; }

    public ICollection<Contact> Contacts { get; set; }
    public ICollection<Address> Addresses { get; set; }
}

Contact class

public class Contact
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string Initials { get; set; }
    public string Function { get; set; }
    public Guid Customer { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Mobile { get; set; }
    public string LanguageCode { get; set; }
    public bool IsMainContact { get; set; }
    public string Gender { get; set; }
    public string Username { get; set; }

    public Guid CustomerId { get; set; }
    // If you want to use class reference navigation property (also called as "hard reference").
    // That can be used in "$expand" or "$select" for example.
    // Uncomment the following line:
    // public Customer Customer { get; set }
}

Address class

public class Address
{
    public Guid Id { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string AddressLine3 { get; set; }
    public string PostalCode { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string CountryCode { get; set; }
    public string Type { get; set; }
    public bool IsMainAddress { get; set; }
    public string Route { get; set; }
    public string State { get; set; }

    public Guid CustomerId { get; set; }
    // If you want to use class reference navigation property (also called as "hard reference").
    // That can be used in "$expand" or "$select" for example.
    // Uncomment the following line:
    // public Customer Customer { get; set }
}

Build your EDM model:

public class MyModelBuilder
{
    public IEdmModel GetEdmModel(IServiceProvider serviceProvider)
    {
        var builder = new ODataConventionModelBuilder(serviceProvider);

        builder.EntitySet<Address>("Addresses")
                        .EntityType
                        .Filter() // Allow for the $filter Command
                        .Count() // Allow for the $count Command
                        .Expand() // Allow for the $expand Command
                        .OrderBy() // Allow for the $orderby Command
                        .Page() // Allow for the $top and $skip Commands
                        .Select();// Allow for the $select Command; 

        builder.EntitySet<Contact>("Contacts")
                        .EntityType
                        .Filter() // Allow for the $filter Command
                        .Count() // Allow for the $count Command
                        .Expand() // Allow for the $expand Command
                        .OrderBy() // Allow for the $orderby Command
                        .Page() // Allow for the $top and $skip Commands
                        .Select() // Allow for the $select Command
                        .Expand(); 

        builder.EntitySet<Customer>("Customers")
                        .EntityType
                        .Filter() // Allow for the $filter Command
                        .Count() // Allow for the $count Command
                        .Expand() // Allow for the $expand Command
                        .OrderBy() // Allow for the $orderby Command
                        .Page() // Allow for the $top and $skip Commands
                        .Select() // Allow for the $select Command
                        .Expand(); 

        return builder.GetEdmModel();
    }
}

Use it in Startup:

public void ConfigureServices(IServiceCollection services)
{
    // ... Other Configurations 

    services.AddOData();
    services.AddTransient<MyModelBuilder>();

    // ... MVC Service Configurations 
}

public void Configure(IApplicationBuilder app, IHostingEnvironment env, MyModelBuilder modelBuilder)
{
    // ... Other Configurations

    app.UseMvc(routeBuilder =>
    {
        routeBuilder.MapODataServiceRoute("ODataRoutes", "odata", modelBuilder.GetEdmModel(app.ApplicationServices));
    });
}

And finally create controller:

[Produces("application/json")]
public class CustomersController : ODataController
{
    private readonly MyDbContext _context;

    public CustomersController (MyDbContext context) => _context = context;

    [EnableQuery]
    public IQueryable<Customer> GetAllCustomers() => _context.Customers;
}

(In code above I assume you have properly configured your DbContext)

Now you should be able to use $expand or $select to get, for example, all addresses of customers.

HTTP GET /odata/Customers?$expand=Addresses

Answer 2

Odata doesn't support eager loading navigational properties without using expand. But you can force expand to be included in query by using an interceptor and changing url. Below is the implementation:

using System;
using System.Web.Http.Controllers;
using System.Web.Http.OData;

namespace ODataWebAPI.API
{
    public class EnableQueryForExpand : EnableQueryAttribute
    {
       public override void OnActionExecuting(HttpActionContext actionContext)
       {
           var url = actionContext.Request.RequestUri.OriginalString;
           var newUrl = ModifyUrl(url);
           actionContext.Request.RequestUri = new Uri(newUrl);
           base.OnActionExecuting(actionContext);
       }

       private string ModifyUrl(string url)
       {
           if (!url.Contains("expand"))
           {
               if (url.Contains("?$"))
               {
                   url = url + "&$";
               }
               else
               {
                   url = url + "?$";
               }
               url = url + "expand=Category,Supplier";
           }
           return url;
       }
   }
}

and you use this attribute in the controller method:

 [EnableQueryForExpand]
 public IQueryable<Customer> GetAllCustomers()
 {

 }