OData $ expand，DTO和Entity Framework [英] OData $expand, DTOs, and Entity Framework
问题描述
数据库有两个表：产品和供应商。产品可以有一个供应商。供应商可以有多个产品。
我还创建了两个DTO课程：
public class Supplier
{
[Key]
public int Id {get;组; }
public string Name {get;组; }
public virtual IQueryable< Product>产品{get;组;
public class Product
{
[Key]
public int Id {get;组; }
public string Name {get;组;
}
我已经设置了我的WebApiConfig，如下所示：
public static void注册（HttpConfiguration config）
{
ODataConventionModelBuilder oDataModelBuilder = new ODataConventionModelBuilder（）;
oDataModelBuilder.EntitySet< Product>（product）;
oDataModelBuilder.EntitySet< Supplier>（supplier）;
config.Routes.MapODataRoute（routeName：oData，
routePrefix：odata，
model：oDataModelBuilder.GetEdmModel（））;
}
我已经设置了我的两个控制器，如下所示：
公共类ProductController：ODataController
{
[HttpGet]
[可查询]
public IQueryable< Product> ; Get（）
{
var context = new ExampleContext（）;
var results = context.EF_Products
.Select（x => new Product（）{Id = x.ProductId，Name = x.ProductName}）;
返回结果为IQueryable< Product> ;;
}
}
public class SupplierController：ODataController
{
[HttpGet]
[可查询]
public IQueryable<供应商> ; Get（）
{
var context = new ExampleContext（）;
var results = context.EF_Suppliers
.Select（x => new Supplier（）{Id = x.SupplierId，Name = x.SupplierName}）;
返回结果为IQueryable< Supplier> ;;
}
}
这是返回的元数据。如您所见，导航属性设置正确：
<？xml version =1.0encoding =utf -8\" >？;
< edmx：Edmx Version =1.0xmlns：edmx =http://schemas.microsoft.com/ado/2007/06/edmx>
< edmx：DataServices m：DataServiceVersion =3.0m：MaxDataServiceVersion =3.0xmlns：m =http://schemas.microsoft.com/ado/2007/08/dataservices/metadata>
< Schema Namespace =StackOverflowExample.Modelsxmlns =http://schemas.microsoft.com/ado/2009/11/edm>
< EntityType Name =Product>
< Key>
< PropertyRef Name =Id/>
< / Key>
< Property Name =IdType =Edm.Int32Nullable =false/>
< Property Name =NameType =Edm.String/>
< / EntityType>
< EntityType Name =Supplier>
< Key>
< PropertyRef Name =Id/>
< / Key>
< Property Name =IdType =Edm.Int32Nullable =false/>
< Property Name =NameType =Edm.String/>
< NavigationProperty Name =ProductsRelationship =StackOverflowExample.Models.StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartnerToRole =ProductsFromRole =ProductsPartner/>
< / EntityType>
<关联名称=StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartner>
< End Type =StackOverflowExample.Models.ProductRole =ProductsMultiplicity =*/>
< End Type =StackOverflowExample.Models.SupplierRole =ProductsPartnerMultiplicity =0..1/>
< / Association>
< / Schema>
< Schema Namespace =Defaultxmlns =http://schemas.microsoft.com/ado/2009/11/edm>
< EntityContainer Name =Containerm：IsDefaultEntityContainer =true>
< EntitySet Name =productEntityType =StackOverflowExample.Models.Product/>
< EntitySet Name =supplierEntityType =StackOverflowExample.Models.Supplier/>
< AssociationSet Name =StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_ProductsPartnerSetAssociation =StackOverflowExample.Models.StackOverflowExample_Models_Supplier_Products_StackOverflowExample_Models_Product_ProductsPartner>
< End Role =ProductsPartnerEntitySet =supplier/>
< End Role =ProductsEntitySet =product/>
< / AssociationSet>
< / EntityContainer>
< / Schema>
< / edmx：DataServices>
< / edmx：Edmx>
所以正常数组的odata查询工作正常：/ odata / product？$ filter = Name + eq + 'Product1'和/ odata / supplier？$ select = Id例如全部工作正常。
问题是我尝试使用$ expand时。如果我做/ odata / supplier？$ expand =产品，我当然会收到一个错误：
指定的类型成员 LINQ to Entities不支持产品，只支持初始化，实体成员和实体导航属性。
更新：
我不断收到相同的问题，以便我添加更多信息。是的，导航属性设置正确，可以在上面发布的元数据信息中看到。
这与控制器上丢失的方法无关。如果我要创建一个实现IODataRoutingConvention的类，/ odata / supplier（1）/ product将被解析为〜/ entityset / key / navigation就可以了。
如果我完全绕过我的DTO，只是返回EF生成的类，$ expand就开始运行了。
更新2：
如果我将产品类更改为以下内容：
public class Product
{
[Key]
public int Id {get;组; }
public string Name {get;组; }
public virtual供应商供应商{get;组;
}
然后将ProductController更改为：
public class ProductController：ODataController
{
[HttpGet]
[可查询]
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 / product我会收回我的预期响应中没有返回一系列具有供应商字段的产品。 sql查询生成从供应商表中加入并选择，如果不是下一个查询结果，这将是有意义的。
如果我打电话/ odata / product ？$ select = Id，我会收回我的期望。但是$ select转换为不加入供应商表的sql查询。
/ odata / product？$ expand =产品失败，出现不同的错误：
DbIsNullExpression的参数必须引用原始，枚举或引用类型。
如果我将产品控制器更改为以下内容：
public class ProductController：ODataController
{
[HttpGet]
[可查询]
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 / product，/ odata / product？$ select = Id和/ odata / product？$ expand =供应商返回正确的结果，但显然.ToList（）会令目标失败。
我可以尝试修改产品控制器，只有在$ expand查询传递时才调用.ToList（），如下所示：
[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）;
返回returnValue为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）;
返回returnValue为IQueryable< Product> ;;
}
}
}
不幸的是，当我打电话/ odata / product？$ select = Id或/ odata / product？$ expand =供应商引发序列化错误，因为returnValue不能转换为IQueryable。如果我打电话/ odata / product，我可以投。
这里有什么工作？我只需要跳过尝试使用自己的DTO，或者/我应该自己实现$ expand和$ select？
基本问题在EF 6.1.0中已修复。请参阅 https://entityframework.codeplex.com/workitem/826 。
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)
The database has two tables: Product and Supplier. A Product can have one Supplier. A Supplier can have multiple Products.
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; }
}
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>
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.
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:
"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.
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.
If I were to bypass my DTOs completely and just return the EF generated classes, $expand works out of the box.
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; }
}
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
}
});
}
}
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.
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/product?$expand=Product fails with a different error:
"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/product, /odata/product?$select=Id, and /odata/product?$expand=Supplier return the correct results, but obviously the .ToList() defeats the purpose a bit.
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>;
}
}
}
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.
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?
The underlying issue was fixed in EF 6.1.0. See https://entityframework.codeplex.com/workitem/826.
这篇关于OData $ expand，DTO和Entity Framework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
