How does one define an (optional) navigation prope

Question

First the explanation and the gist, then the question. So:

Let's say I have a view AccountView defined in EF (6.1.1) database first (edmx) so that the code-generated class is

//This class is generated from a view by EF (edmx)...
public partial class AccountView
{
    public System.Guid Id { get; set; }
    public int CompanyId { get; set; }
}

Then I create a partial class in the same namespace (Entities) as

[MetadataType(typeof(AccounViewMetaData))]
public partial class AccounView
{
    //This is added here explicitly. AccountView itself exposes just
    //a naked key, CompanyId.
    public virtual Company Company { get; set; }

    //This is just in case...
    public class AccounViewDomainMetaData
    {
        //This is to add a navigation property to the OData $metadata. How to do this
        //in WebApiConfig? See as follows...
        [ForeignKey("Company")]
        public int CompanyId { get; set; }
    }
}

and

//This is an EF generated class one from an edmx..-
public partial class Company
{
    public Company()
    {            
    }

    public int CompanyID { get; set; }
    public string Name { get; set; }
}  

And then in WebApiConfig I Write, or try to, something in OData v4 (the newest, 6.9.0.0 series with WebApi, the newest also) as follows

builder.EntitySet<Entities.Company>("Companies");

var accountSet = builder.EntitySet<Entities.AccountView>("Accounts");
accountSet.EntityType.HasKey(i => i.Id); //EF has hard time recognizing primary keys on database first views...

//TODO: How should I define the required binding so that I could "?$expand=Company"
//as such as ``http://example.com/service/Accounts?$expand=Company``
//accountSet.HasRequiredBinding(i => i.CompanyId, typeof(Entities.Company));

The resulting $metadata is like

<schema>
    <EntityType Name="Company">
        <Key>
            <PropertyRef Name="CompanyID"/>
        </Key>
        <Property Name="CompanyID" Type="Edm.Int32" Nullable="false"/>
        <Property Name="Name" Type="Edm.String"/>
     </EntityType>

    <EntityType Name="AccountView">
        <Key>
            <PropertyRef Name="Id"/>
        </Key>
        <Property Name="Id" Type="Edm.Guid" Nullable="false"/>
        <Property Name="CompanyId" Type="Edm.Int32" Nullable="false"/>
        <NavigationProperty Name="Company" Type="Entities.Company" Nullable="false"/>
    </EntityType>
</Schema>

Question(s): As mentioned in the TODO comment, how should I define

• A navigational property so that I could call http://example.com/Accounts?$expand=Company or http://example.com/Accounts?$Companies?

Now it works if I call

• http://example.com/Accounts or
• http://example.com/Accounts(someId)

Then if I do something like

• http://example.com/Accounts?$expand=Companies
• http://example.com/Accounts?$expand=Company or
• http://example.com/Accounts(someId)?$expand=Company

I get greeted by HTTP 400 (?$expand=Companies) or HTTP 500 (?$expand=Company).

I succeeded with creating a Containment relationship already. It looks like, though, it requires having the root entity defined by an ID, whereas I'd like to provide "GET" to the root and optionally expand to a "child list" objects (hence this question about ?$expand=).

Now the case is having one non-optional expand done for one entity, but I suspect the next thing I'd like have is a scenario where there's a list of entities (or companies in terms of this specific example) but how could I achieve these scenarios? How to fix even this case of expanding to the first always existing sub-object?

My controller is defined as follows

public class AccountsController: ODataController
{
    [EnableQuery]
    public IHttpActionResult Get()
    {
        try
        {
            //This context here is a EF entities model (generated from an edmx)...
            return Ok(Context.AccountView);
        }
        catch(Exception ex)
        {
            return InternalServerError(); 
        }
    }

    [EnableQuery]
    public IHttpActionResult Get([FromODataUri]Guid key)
    {
        try
        {
            return Ok(SingleResult.Create(Context.AccountView.Where(a => a.Id == key)));
        }
        catch (Exception ex)            
        {                
            return InternalServerError(ex); 
        }
    }
}

What I'm basically trying to do is to add some more metadata to database first, edmx, views and mimick the article Using $select, $expand, and $value in ASP.NET Web API 2 OData. Thus far with no success...

<edit 1: The plot thickens, so to speak. The HTTP 500 error comes from an internal exception (I had to turn on breaking to managed framework exceptions) that says

A first chance exception of type 'System.NotSupportedException' occurred in EntityFramework.dll

Additional information: The specified type member 'Company' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

So, yes, AccountView is a view and it does not have a direct foreign key relation to Company table (which just happens to be a table, but it could be a view too). How could I go to add one? I though adding metadata did the trick already, as evidenced by $metadata information. Doesn't OData just write an INNER JOIN in the database? Am I missing something here? ... This seem to be related to the way I added the Company reference and EF doesn't like that. It looks I should go about adding it to the OData model only...

<edit 2: It doesn't seem to make a difference (at least not with the setting I have) if I change the AccountView CompanyId to CompanyID to match the casing that of the definition in Company table.

<edit3: I asked another, related question to this, How to add complex properties on a model built with ODataConventionModelBuilder from an EF model.

Answer 1

It looks I got this one answered as to related another post and little pushing from another forum.

About the exception, indeed, it was caused by the query hitting to the database and trying to retrieve columns which aren't defined there. For more information:

• The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported
• EF 4: The specified type member '*' is not supported in LINQ to Entities

Then as for the code and answer, the other question is Expanding navigation properties with ODataQueryOptions. The code a bit augmented here is as follows

public async Task<IHttpActionResult> Get(ODataQueryOptions<T> options)
{
    IQueryable<T> tempQuery = Context.AccountView;
    IQueryable<T result = tempQuery;

    if(options.SelectExpand != null)
    {
        if(options.Filter != null)
        {
            tempQuery = options.Filter.ApplyTo(tempQuery, new ODataQuerySettings()) as IQueryable<T>;   
        }
         /* Other options that should go to the DB level... */

        //The results need to be materialized, or otherwise EF throws a
        //NotSupportedException due to columns and properties that aren't in the DB...
        result = (await tempQuery.ToListAsync()).AsQueryable();

        //Do here the queries that can't be hit straight by the queries. E.g.
        //navigation properties not defined in EF views (that can't be augmented)...
        //E.g. get the company here.

        //This is needed to that OData formatter knows to render navigation links too.
        Request.ODataProperties().SelectExpandClause = options.SelectExpand.SelectExpandClause;
}

return Ok(result);

Answer 2

Try $expand instead of &expand.