OData的$扩大，DTO的，和Entity Framework [英] OData $expand, DTOs, and Entity Framework

问题描述

我有一个数据库第一EF的DataModel建立一个基本的WebAPI服务设置。我运行的是每晚构建的WebAPI，EF6和的WebAPI的OData包。 （的WebAPI：5.1.0-α1，EF：6.1.0-α1，OData的的WebAPI：5.1.0-α1）

I have a basic WebApi service setup with a database first EF DataModel set up. I am running the nightly builds of WebApi, EF6, and the WebApi OData packages. (WebApi: 5.1.0-alpha1, EF: 6.1.0-alpha1, WebApi OData: 5.1.0-alpha1)

该数据库有两个表：产品和供应商。 A产品可以有一个供应商。供应商可以有多个产品。

The database has two tables: Product and Supplier. A Product can have one Supplier. A Supplier can have multiple Products.

我还创建了两个DTO类：

I have also created two DTO classes:

public class Supplier
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public virtual IQueryable<Product> Products { get; set; }
}

public class Product
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }
}

我已经建立了我的WebApiConfig如下：

I have set up my WebApiConfig as follows:

public static void Register(HttpConfiguration config)
{
    ODataConventionModelBuilder oDataModelBuilder = new ODataConventionModelBuilder();

    oDataModelBuilder.EntitySet<Product>("product");
    oDataModelBuilder.EntitySet<Supplier>("supplier");

    config.Routes.MapODataRoute(routeName: "oData",
        routePrefix: "odata",
        model: oDataModelBuilder.GetEdmModel());
}

我已经建立了我的两个控制器如下：

I have set up my two controllers as follows:

public class ProductController : ODataController
{
    [HttpGet]
    [Queryable]
    public IQueryable<Product> Get()
    {
        var context = new ExampleContext();

        var results = context.EF_Products
            .Select(x => new Product() { Id = x.ProductId, Name = x.ProductName});

        return results as IQueryable<Product>;
    }
}

public class SupplierController : ODataController
{
    [HttpGet]
    [Queryable]
    public IQueryable<Supplier> Get()
    {
        var context = new ExampleContext();

        var results = context.EF_Suppliers
            .Select(x => new Supplier() { Id = x.SupplierId, Name = x.SupplierName });

        return results as IQueryable<Supplier>;
    }
}

下面是被返回的元数据。正如你所看到的，导航属性的设置是否正确：

Here is the metadata that gets returned. As you can see, the navigation properties are set up correctly:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
 <edmx:DataServices m:DataServiceVersion="3.0" m:MaxDataServiceVersion="3.0" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <Schema Namespace="StackOverflowExample.Models" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
   <EntityType Name="Product">
    <Key>
     <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Int32" Nullable="false" />
    <Property Name="Name" Type="Edm.String" />
   </EntityType>
   <EntityType Name="Supplier">
    <Key>
     <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Int32" Nullable="false" />
    <Property Name="Name" Type="Edm.String" />
    <NavigationProperty Name="Products" Relationship="StackOverflowExample.Models.StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartner" ToRole="Products" FromRole="ProductsPartner" />
   </EntityType>
   <Association Name="StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartner">
    <End Type="StackOverflowExample.Models.Product" Role="Products" Multiplicity="*" />
    <End Type="StackOverflowExample.Models.Supplier" Role="ProductsPartner" Multiplicity="0..1" />
   </Association>
  </Schema>
  <Schema Namespace="Default" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
   <EntityContainer Name="Container" m:IsDefaultEntityContainer="true">
    <EntitySet Name="product" EntityType="StackOverflowExample.Models.Product" />
    <EntitySet Name="supplier" EntityType="StackOverflowExample.Models.Supplier" />
     <AssociationSet Name="StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartnerSet" Association="StackOverflowExample.Models.StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartner">
      <End Role="ProductsPartner" EntitySet="supplier" />
      <End Role="Products" EntitySet="product" />
     </AssociationSet>
    </EntityContainer>
   </Schema>
  </edmx:DataServices>
</edmx:Edmx>

所以ODATA查询的普通阵列正常工作：？？/ ODATA /产品$ =过滤器+姓名+ EQ产品1和/ ODATA /供应商选择$ = ID例如所有做工精细

So the normal array of odata queries work fine: /odata/product?$filter=Name+eq+'Product1' and /odata/supplier?$select=Id for example all work fine.

问题是，当我试图用$合作扩大。 ？如果我是做/ ODATA /供应商$展开;产品，当然，我得到一个错误：

The problem is when I attempt to work with $expand. If I were to do /odata/supplier?$expand=Products, I of course get an error:

指定类型的成员'产品'不是在LINQ支持实体只有初始化，实体成员和实体导航属性都支持。

"The specified type member 'Products' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

更新：
我不断收到同样的问题，所以我加入了更多的信息。是的，导航属性正确设置为可以在我上面张贴的元数据信息中可以看出。

Update: I keep getting the same questions so I am adding more information. Yes, the navigation properties are set up correctly as can be seen in the metadata information I posted above.

这是不相关的是缺少在控制器上的方法。如果我要创建一个实现IODataRoutingConvention类，/ ODATA /供应商（1）/产品将被解析出来为〜/ EntitySet的/键/导航就好了。

This is not related to methods being missing on the controller. If I were to create a class that implements IODataRoutingConvention, /odata/supplier(1)/product would be parsed out as "~/entityset/key/navigation" just fine.

如果我是完全绕过我的DTO，只是返回EF生成的类，$拓展工程开箱。

If I were to bypass my DTOs completely and just return the EF generated classes, $expand works out of the box.

更新2：
如果我改变我的产品类别为以下内容：

Update 2: If I change my Product class to the following:

public class Product
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }

    public virtual Supplier Supplier { get; set; }
}

，然后更改ProductController的这样：

and then change the ProductController to this:

public class ProductController : ODataController
{
    [HttpGet]
    [Queryable]
    public IQueryable<Product> Get()
    {
        var context = new ExampleContext();

        return context.EF_Products
            .Select(x => new Product() 
            { 
                Id = x.ProductId, 
                Name = x.ProductName, 
                Supplier = new Supplier() 
                {
                    Id = x.EF_Supplier.SupplierId, 
                    Name = x.EF_Supplier.SupplierName 
                } 
            });
    }
}

如果我是叫/ ODATA /产品，我还是会回到我的预期。产品与供应商领域的一个数组中的反应没有回来。生成的SQL查询连接，并从供应商表，这将是有意义的我，如果没有下一个查询结果的选择。

If I were to call /odata/product I would get back what I expected. An array of Products with the Supplier field not returned in the response. The sql query generated joins and selects from the Suppliers table, which would make sense to me if not for the next query results.

如果我是叫/ ODATA /产品？$选择= ID，我还是会回到我所期望的那样。但是$选择转换为一个SQL查询，没有加入到供应商表。

If I were to call /odata/product?$select=Id, I would get back what I would expect. But $select translates to a sql query that does not join to the suppliers table.

/ ODATA /产品$拓展=产品失败，不同的​​错误：

/odata/product?$expand=Product fails with a different error:

来DbIsNullEx pression参数必须指向一个原始的，枚举或引用类型。

"The argument to DbIsNullExpression must refer to a primitive, enumeration or reference type."

如果我改变我的产品控制器为以下内容：

If I change my Product Controller to the following:

public class ProductController : ODataController
{
    [HttpGet]
    [Queryable]
    public IQueryable<Product> Get()
    {
        var context = new ExampleContext();

        return context.EF_Products
            .Select(x => new Product() 
            { 
                Id = x.ProductId, 
                Name = x.ProductName, 
                Supplier = new Supplier() 
                {
                    Id = x.EF_Supplier.SupplierId, 
                    Name = x.EF_Supplier.SupplierName 
                } 
            })
            .ToList()
            .AsQueryable();
    }
}

/ ODATA /产品/ ODATA /产品？$选择= ID，和/ ODATA /产品？$ =扩大供应商返回正确的结果，但显然.ToList（）失败的目的了一下。

/odata/product, /odata/product?$select=Id, and /odata/product?$expand=Supplier return the correct results, but obviously the .ToList() defeats the purpose a bit.

我可以尝试修改产品控制器只调用.ToList（）当$扩大查询被传递，就像这样：

I can try to modify the Product Controller to only call .ToList() when an $expand query is passed, like so:

    [HttpGet]
    public IQueryable<Product> Get(ODataQueryOptions queryOptions)
    {
        var context = new ExampleContext();

        if (queryOptions.SelectExpand == null)
        {
            var results = context.EF_Products
                .Select(x => new Product()
                {
                    Id = x.ProductId,
                    Name = x.ProductName,
                    Supplier = new Supplier()
                    {
                        Id = x.EF_Supplier.SupplierId,
                        Name = x.EF_Supplier.SupplierName
                    }
                });

            IQueryable returnValue = queryOptions.ApplyTo(results);

            return returnValue as IQueryable<Product>;
        }
        else
        {
            var results = context.EF_Products
                .Select(x => new Product()
                {
                    Id = x.ProductId,
                    Name = x.ProductName,
                    Supplier = new Supplier()
                    {
                        Id = x.EF_Supplier.SupplierId,
                        Name = x.EF_Supplier.SupplierName
                    }
                })
                .ToList()
                .AsQueryable();

            IQueryable returnValue = queryOptions.ApplyTo(results);

            return returnValue as IQueryable<Product>;
        }
    }
}

不幸的是，当我打电话/ ODATA /产品？$选择= ID或/ ODATA /产品？$展开;供应商，它抛出一个序列化错误，因为的returnValue不能转换为IQueryable的。我可投但如果我叫/ ODATA /产品。

Unfortunately, when I call /odata/product?$select=Id or /odata/product?$expand=Supplier it throws a serialization error because returnValue can't be cast to IQueryable. I can be cast though if I call /odata/product.

什么是工作在这里？难道我只是跳过试图用我自己的DTO或可以/我应该推出自己的执行$扩大和$选择？

What is the work around here? Do I just have to skip trying to use my own DTOs or can/should I roll my own implementation of $expand and $select?

推荐答案

下面是我结束了记录解决方案：的 http://beyondtheduck.com/projecting-and-expand-possible-at-last-kinda/

Here is the solution that I ended up documenting: http://beyondtheduck.com/projecting-and-expand-possible-at-last-kinda/

这篇关于OData的$扩大，DTO的，和Entity Framework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！