在代码中如何定义（可选）导航属性（？$ expand =）？ [英] How does one define an (optional) navigation property (?$expand=) in code?

问题描述

首先解释和要点，然后是问题。所以：

假设我有一个视图 AccountView 在EF（6.1.1）数据库中首先定义（edmx）所以代码生成的类是

  // This类是从EF（edmx）的视图生成的... 
 public partial class AccountView 
 {
 public System.Guid Id {get;组; } 
 public int CompanyId {get;组; } 
} 
  

然后我在同一个命名空间中创建一个部分类（实体）as

  [MetadataType（typeof（AccounViewMetaData） ）] 
 public partial class AccounView 
 {
 //这里显式添加。 AccountView本身只暴露了
 //裸体公司CompanyId。 
公共虚拟公司公司{get;组; } 
 
 //这只是为了防止... 
 public class AccounViewDomainMetaData 
 {
 //这是为了向OData $元数据添加一个导航属性。如何在WebApiConfig中执行
 //？参见如下... 
 [ForeignKey（Company）] 
 public int CompanyId {get;组; } 
} 
} 
  

和

  //这是一个EF生成的一个来自edmx ..- 
的公共部分类公司
 { 
 public Company（）
 {
} 
 
 public int CompanyID {get;组; } 
 public string Name {get;组; } 
} 
  

然后在 WebApiConfig 我写，或尝试，在OData v4（最新的，6.9.0.0系列与WebApi，最新的）如下

  builder.EntitySet< Entities.Company>（ 公司）; 
 
 var accountSet = builder.EntitySet< Entities.AccountView>（Accounts）; 
 accountSet.EntityType.HasKey（i => i.Id）; // EF很难在数据库首个视图上识别主键... 
 
 // TODO：我应该如何定义所需的绑定，以便我可以？$ expand = Company
 //如http：//example.com/service/Accounts？$ expand = Company`` 
 //accountSet.HasRequiredBinding(i => i.CompanyId，typeof（Entities.Company）） ; 
  

生成的 p>



 < schema> 
< EntityType Name =Company> 
< Key> 
< PropertyRef Name =CompanyID/> 
< / Key> 
< Property Name =CompanyIDType =Edm.Int32Nullable =false/> 
< Property Name =NameType =Edm.String/> 
< / EntityType> 
 
< EntityType Name =AccountView> 
< Key> 
< PropertyRef Name =Id/> 
< / Key> 
< Property Name =IdType =Edm.GuidNullable =false/> 
< Property Name =CompanyIdType =Edm.Int32Nullable =false/> 
< NavigationProperty Name =CompanyType =Entities.CompanyNullable =false/> 
< / EntityType> 
< / Schema> 
  

问题如 TODO 注释，我应该如何定义

• 导航属性，以便我可以调用 http://example.com/Accounts?$expand=Company 或 http://example.com/Accounts?$Companies ？

现在，如果我打电话给

• http://example.com/Accounts 或


• http://example.com/Accounts someId）

然后如果我做一些像

• http://example.com/Accounts?$expand=Companies


• http://example.com/Accounts?$expand=Company 或


• http://example.com / accounts（someId）？$ expand =公司

我被 HTTP 400 / strong>（？$ expand = Companies）或 HTTP 500 （？$ expand = Company）。

我成功创建了一个遏制已经关系了。它看起来像是需要一个由ID定义的根实体，而我想提供GET给根，并可以扩展为子列表对象（因此这个问题关于？$ expand =）

现在，一个实体有一个非可选的 expand ，但我怀疑下一个我想要的是一个场景，其中有一个实体列表（或者这个具体示例中的公司），但是如何实现这些场景？如何解决这个扩展的情况到第一个始终存在的子对象？

我的控制器定义如下

  public class AccountsController：ODataController 
 {
 [EnableQuery] 
 public IHttpActionResult Get （）
 {
 try 
 {
 //这里是一个EF实体模型（从一个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）; 
} 
} 
} 
  

要做的是将一些更多的元数据添加到数据库，edmx，视图和模仿文章在ASP.NET Web API 2 OData中使用$ select，$ expand和$ value。到目前为止，没有成功...

<编辑1：剧情变稠，可以这么说。 HTTP 500错误来自内部异常（我必须打开到托管框架异常），说

第一次机会例外在EntityFramework.dll中，输入System.NotSupportedException

附加信息：LINQ to Entities不支持指定的类型成员Company。只支持初始化器，实体成员和实体导航属性。

所以，是的， AccountView 是一个视图，它没有与公司表（恰好恰好是一个表，但它也可以是一个视图）的直接外键关系。我该怎么去添加一个？我已经添加了元数据，就像 $ metadata 信息所证明的那样。 OData不会在数据库中写入 INNER JOIN ？我在这里遗漏了什么吗？ ...这似乎与我添加公司参考的方式相关，EF不喜欢。看来我应该把它添加到OData模型...

<编辑2：似乎没有如果我将 AccountView CompanyId 更改为 CompanyID 匹配公司表中定义的套管。

< edit3：我问了另一个相关的问题，。

解决方案

看起来我得到了一个回答，关于另一个帖子，并从另一个论坛的推动。

关于异常，的确，这是由查询引起的打到数据库，并尝试检索那里没有定义的列。有关详细信息：

• LINQ to Entities不支持指定的类型成员。只支持初始化，实体成员和实体导航属性

然后，对于代码和答案，另一个问题是使用ODataQueryOptions扩展导航属性。这里有一些增加的代码如下

  public async任务< IHttpActionResult> Get（ODataQueryOptions< T>选项）
 {
 IQueryable< T> tempQuery = Context.AccountView; 
 IQueryable< T result = tempQuery; 
 
 if（options.SelectExpand！= null）
 {
 if（options.Filter！= null）
 {
 tempQuery = options.Filter。 ApplyTo（tempQuery，new ODataQuerySettings（））为IQueryable< T>; 
} 
 / *应该去DB级别的其他选项... * / 
 
 //结果需要实现，否则EF会抛出
 // NotSupportedException由于不在数据库中的列和属性... 
 result =（await tempQuery.ToListAsync（））。AsQueryable（）; 
 
 //在这里查询不能直接查询的查询。例如。 
 //导航属性未在EF视图中定义（不能被扩充）... 
 //E.g。让公司在这里。 
 
 //这是OData格式化程序知道渲染导航链接所必需的。 
 Request.ODataProperties（）。SelectExpandClause = options.SelectExpand.SelectExpandClause; 
} 
 
 return Ok（result）; 
  

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.

解决方案

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);

这篇关于在代码中如何定义（可选）导航属性（？$ expand =）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！