OData and WebAPI: Navigation property not present on model

Question

I'm trying to put together a simple toy project using Entity Framework, WebAPI, OData, and an Angular client. Everything is working fine, except the navigation property that I have put on one of my models doesn't seem to be working. When I call my API using $expand, the returned entities do not have their navigation properties.

My classes are Dog and Owner, and look like this:

    public class Dog
{
    // Properties
    [Key]
    public Guid Id { get; set; }
    public String Name { get; set; }
    [Required]
    public DogBreed Breed { get; set; }
    public int Age { get; set; }
    public int Weight { get; set; }


    // Foreign Keys
    [ForeignKey("Owner")]
    public Guid OwnerId { get; set; }

    // Navigation
    public virtual Owner Owner { get; set; }
}

    public class Owner
{
    // Properties
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Phone { get; set; }
    public DateTime SignupDate { get; set; }

    // Navigation
    public virtual ICollection<Dog> Dogs { get; set; } 
}

I also have my Dog controller set up to handle querying:

public class DogsController : ODataController
{
    DogHotelAPIContext db = new DogHotelAPIContext();
    #region Public methods 

    [Queryable(AllowedQueryOptions = System.Web.Http.OData.Query.AllowedQueryOptions.All)]
    public IQueryable<Dog> Get()
    {
        var result =  db.Dogs.AsQueryable();
        return result;
    }

    [Queryable(AllowedQueryOptions = System.Web.Http.OData.Query.AllowedQueryOptions.All)]
    public SingleResult<Dog> Get([FromODataUri] Guid key)
    {
        IQueryable<Dog> result = db.Dogs.Where(d => d.Id == key).AsQueryable().Include("Owner");
        return SingleResult.Create(result);
    }

    protected override void Dispose(bool disposing)
    {
        db.Dispose();
        base.Dispose(disposing);
    }

}

I've seeded the database with a bit of sample data. All dog records have an OwnerId that matches the Id of an Owner in the Owners table.

Querying for the list of dogs using this works fine:

http://localhost:49382/odata/Dogs

I get a list of Dog entities, without the Owner navigation property.

Querying for the dogs with their owners using OData $expand does NOT work:

http://localhost:49382/odata/Dogs?$expand=Owner

My response is a 200 with all of the Dog entities, but none of them have an Owner property on them in the JSON.

If I query my metadata, I find that OData does seem to know about it:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema Namespace="DogHotelAPI.Models" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EntityType Name="Dog">
        <Key>
          <PropertyRef Name="id" />
        </Key>
        <Property Name="id" Type="Edm.Guid" Nullable="false" />
        <Property Name="name" Type="Edm.String" />
        <Property Name="breed" Type="DogHotelAPI.Models.Enums.DogBreed" Nullable="false" />
        <Property Name="age" Type="Edm.Int32" Nullable="false" />
        <Property Name="weight" Type="Edm.Int32" Nullable="false" />
        <Property Name="ownerId" Type="Edm.Guid" />
        <NavigationProperty Name="owner" Type="DogHotelAPI.Models.Owner">
          <ReferentialConstraint Property="ownerId" ReferencedProperty="id" />
        </NavigationProperty>
      </EntityType>
      <EntityType Name="Owner">
        <Key>
          <PropertyRef Name="id" />
        </Key>
        <Property Name="id" Type="Edm.Guid" Nullable="false" />
        <Property Name="name" Type="Edm.String" />
        <Property Name="address" Type="Edm.String" />
        <Property Name="phone" Type="Edm.String" />
        <Property Name="signupDate" Type="Edm.DateTimeOffset" Nullable="false" />
        <NavigationProperty Name="dogs" Type="Collection(DogHotelAPI.Models.Dog)" />
      </EntityType>
    </Schema>
    <Schema Namespace="DogHotelAPI.Models.Enums" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EnumType Name="DogBreed">
        <Member Name="AfghanHound" Value="0" />
        <Member Name="AmericanStaffordshireTerrier" Value="1" />
        <Member Name="Boxer" Value="2" />
        <Member Name="Chihuahua" Value="3" />
        <Member Name="Dachsund" Value="4" />
        <Member Name="GermanShepherd" Value="5" />
        <Member Name="GoldenRetriever" Value="6" />
        <Member Name="Greyhound" Value="7" />
        <Member Name="ItalianGreyhound" Value="8" />
        <Member Name="Labrador" Value="9" />
        <Member Name="Pomeranian" Value="10" />
        <Member Name="Poodle" Value="11" />
        <Member Name="ToyPoodle" Value="12" />
        <Member Name="ShihTzu" Value="13" />
        <Member Name="YorkshireTerrier" Value="14" />
      </EnumType>
    </Schema>
    <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      <EntityContainer Name="Container">
        <EntitySet Name="Dogs" EntityType="DogHotelAPI.Models.Dog">
          <NavigationPropertyBinding Path="owner" Target="Owners" />
        </EntitySet>
        <EntitySet Name="Owners" EntityType="DogHotelAPI.Models.Owner">
          <NavigationPropertyBinding Path="dogs" Target="Dogs" />
        </EntitySet>
      </EntityContainer>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

What could I be missing that is preventing my navigation preoprty from coming back with the rest of my model?

EDIT

To further isolate the problem I tried including the Owners in C# on the server side. I added this line in the Get method of my Dog controller:

var test = db.Dogs.Include("Owner").ToList();

With this I can debug and see that the related owners ARE being included. Each dog has the owner that is associated with it in this list.

Using .Include("Owner") on what is actually returned does not fix the problem - the properties still never reach the client.

This seems to mean that the navigation properties are working, but are not being sent back to the client. This seems like it wound indicate an issue with OData or WebAPI, I would guess, but I'm not sure what.

Also, I have added the following lines to Application_Start in my Global.asax file in order to handle circular navigation properties:

            var json = GlobalConfiguration.Configuration.Formatters.JsonFormatter;
        json.SerializerSettings.PreserveReferencesHandling =
            Newtonsoft.Json.PreserveReferencesHandling.All;

I did that in case a circular reference was somehow the culprit, but this changes nothing.

UPDATE

I noticed that making a call to

http://localhost:49382/odata/Dogs(abfd26a5-14d8-4b14-adbe-0a0c0ef392a7)/owner

works. This retrieves the owner associated with that dog. This further illustrates that my navigation properties are set up correctly, they just aren't being included in responses to calls using $expand.

UPDATE 2

Here is the register method of my WebApiConfig file:

        public static void Register(HttpConfiguration config)
    {
        //config.Routes.MapHttpRoute(
        //    name: "DefaultApi",
        //    routeTemplate: "api/{controller}/{id}",
        //    defaults: new { id = RouteParameter.Optional }
        //);

        ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
        builder.EnableLowerCamelCase();
        builder.EntitySet<Dog>("Dogs");
        builder.EntitySet<Owner>("Owners");

        config.EnableQuerySupport();

        config.MapODataServiceRoute(
            routeName: "ODataRoute",
            routePrefix: "odata",
            model: builder.GetEdmModel());


        // Uncomment the following line of code to enable query support for actions with an IQueryable or IQueryable<T> return type.
        // To avoid processing unexpected or malicious queries, use the validation settings on QueryableAttribute to validate incoming queries.
        // For more information, visit http://go.microsoft.com/fwlink/?LinkId=279712.
        //config.EnableQuerySupport();

        // To disable tracing in your application, please comment out or remove the following line of code
        // For more information, refer to: http://www.asp.net/web-api
        config.EnableSystemDiagnosticsTracing();
    }

Perigon

In testing on my own functional OData v4 setup. I found that putting the include("RelatedEntity") did not return the related entity either. I attempted to research how to debug the $expand command once it is passed into the API, but was unable to locate the information. I would suggest if it is at all possible to use the Scaffolding provided with Visual Studio to produce an Owner and Dog controller. Adjusting your [Queryable] to meet your requirements from the generic that is produced, and trying it that way if at all possible. – Whig

I also still think that it may just be tied to the navigation property and foreign key setup. Maybe try a non OData query in a separate class with a break point to see if you can pull both data points to verify the relationship is setup using a generic entity query. – Whig

Answer 1

I found the solution to my problem, which was ultimately caused by three things:

1.) I was using the [Queryable] attribute on my controller methods, which are deprecated. I needed to use the newer [EnableQuery] attributes.

2.) In my WebApiConfig.cs file I was enabling querying by using the default config.EnableQuerySupport(). This is deprecated, and has been removed.

3.) My expand call needed was in the form of $expand=Owner but needed to be in the form of $expand=owner since I am enabling lower camel case on my ODataConventionModelBuilder. Thank you very much to Mark Bennetts, whose answer pointed this out!

After making all of these changes, related Owner entities are being returned with Dog entities.

Perigon

Answer 2

This is because you are using

builder.EnableLowerCamelCase();

in your ODataConventionModelBuilder setup.

Its not recognising "Owner" in your query options $expand clause because that path really doesnt exist in the OData model because it is case sensitive.

If you try requesting this /Dogs?$expand=owner I'm sure that will work and you will get both Dogs and their Owners returned in the JSON response.

Sheryllshetland

Sorry - I know this is infuriating, but this isn't the case either. :) Using $expand=owner doesn't change anything. However, now that I've changed the [Queryable] attributes to [EnableQuery] on my controller methods, using expand causes an error: "The query specified in the URI is not valid. Could not find a property named 'Owner' on type 'DogHotelAPI.Models.Dog'." – Perigon

Furthermore, I wouldn't expect this to be an issue with the format of the expand because the 'Owner' field NEVER comes back on Dog entities. I would expect that even a simple query for all Dogs would return the 'Owner' entity on them, even if it is null. That is not happening - 'Owner' is never present on a returned Dog entity. – Perigon

Answer 3

I had a very similar problem, which I believe is caused by the exact same issue.

I was trying to create some bound OData functions which would return entire graphs of entities to make the clients job a little easier in certain situations rather than having to specify $expand clauses for everything.

I specified Include statements in the entity framework linq calls, and verified that the return data was indeed fully populated in debug but, like you I was only ever getting the top-level entity returned with nothing else.

The problem lies with the serialisation used for OData

What you'll find is that if you remove the primary key from your Owner class so it essentially becomes a complex entity, then it will be included in the OData serialised JSON result, otherwise it will not unless the OData request uri comprises an $expand clause that includes it.

I tried to find a way to insert $expand clauses in code to make it work, but unfortunately came up blank.

Hope this helps

Sheryllshetland

Thanks for the comment. I'm not sure exactly how it relates to my issue however: I already don't have the [Key] specified on the Owner class, and I am using an $expand clause in order to bring it along with my Dog entities. What exactly are you suggesting I change? – Perigon

Suspect you'll find the [Key] is inferred because you have an Id property - bet if you remove this property it will work. – Sheryllshetland

I can't verify this because if I do so, then config.MapODataServiceRoute errors stating that a key is missing. And even if it didn't, while this might shine more light on the underlying problem, it would be a long way from being a good solution. Plenty of WebAPI + OData tutorials have examples calling $expand on multiple entities with keys. – Perigon

Your right its not a good solution at all and I wasnt suggesting it. I didnt realise that you couldnt get $expand to work either, thats very odd. I thought that you were just expecting the Owner to be included in the JSON result from the SingleResult Get because you have eagerly loaded it in the linq call with an Include statement. Liek I said - this is something that I wanted to do, and couldnt get it to work unless the entity was a complex object and had no key of its own. – Sheryllshetland

I notice you say you have to use [Queryable] attribute in your controller - sounds like you are not using the OData V4 libraries - maybe check your nuget packages. Microsoft ASP.Net Web API 2.2 for OData v4.0 - version 5.4.0 ODataLib - version 6.9.0 EdmLib - version 6.9.0 OData V4 controllers are expected to use [EnableQuery] The only other thing - your [ForeignKey("Owner")] attribute in the Dog model is in the wrong place like others have said. This attribute should be above the Owner navigation property, and be [ForeignKey("OwnerId")] – Sheryllshetland

I have WebAPI 2.2 for OData v4.0 version 5.6.0, ODataLib 6.12, and EDMLib 6.12. Changing Queryable to EnableQuery gives me an error when I send the $expand request, stating that "The query specified in the URI is not valid. Could not find a property named 'Owner' on type 'DogHotelAPI.Models.Dog'" Also, I followed the example for setting up the foreign key at asp.net/web-api/overview/odata-support-in-aspnet-web-api/…. Is that not the correct way of doing things? – Perigon

Ok, you have the very latest OData libraries, thats fine. Regarding the FK setup, according to the documentation you can specify it either way, so you should be alright there too: The annotation may be placed on the foreign key property and specify the associated navigation property name, or placed on a navigation property and specify the associated foreign key name. Can you show how you are declaring the entity sets to the OData model builder in WebApiConfig – Sheryllshetland

Sure - I just put it up. – Perigon

Ahhhhhh - now it becomes clear builder.EnableLowerCamelCase(); - this is the culprit, its not recognising Owner due to capitalization. If you do /Dogs?$expand=owner that will work – Sheryllshetland

Answer 4

See if the below might work for you. I'm testing in OData v4 so you may need to adjust [EnableQuery] to [Queryable]. Your db context should be returning an IQueryable result such that .AsQueryable() might not be needed.

// GET: odata/Dogs
[EnableQuery]
public IQueryable<Dog> Get()
{
    return db.Dogs;
}

// GET: odata/Dogs(5)/Owner
[EnableQuery]
public IQueryable<Owner> GetOwner([FromODataUri] int key)
{
    return db.Dogs.Where(m => m.ID == key).SelectMany(m => m.Owner);
}

I'm comparing what you have to a small project I'm currently working on. This is probably not the case, but my FK association is setup slightly differently and just maybe by some fluke the ordering of the FK is the issue. My Foreign Keys seem to be decorated atop the nav properties.

public int PublicImageID { get; set; }
[ForeignKey("PublicImageID")]
public PublicImage PublicImage { get; set; }

// Foreign Keys    
public Guid OwnerId { get; set; }
[ForeignKey("OwnerId")]
public virtual Owner Owner { get; set; }

Whig

I tried this, and I still see the same thing. Note that I have to use Queryable, and then explicitly allow query options within that, in order to get anything other than an error message. – Perigon

Also interesting to me is that even if I .Include("Owner") in my server-side controller, the Owner property is not sent across with the Dog entities in my response. – Perigon

I'm comparing some functional code I have that is very similar. I've added an example of my Foreign Key declaration to see if by some fluke that could be the culprit. – Whig

Drat! I was hoping that was it. After making those changes, Dogs still come back without their Owner properties. Which is still confusing, because I feel like I'm going the "out-of-the-box" route for setting this up. – Perigon

Oh well, I doubt it is the case, but do you also have an Owner controller for which you can pull an owner? – Whig

I do. Set up very similarly. However, even if I didn't, I would think that at least I would get back null Owner properties, or get an error or something. – Perigon

So when you use the owner controller via odata it pulls fine? – Whig

Yep. Hitting /odata/Owners pulls the list of owners down just fine. – Perigon

Even found a solution to this? – Ardisardisj