数据库有两个表:Product和Supplier。一个Product可以有一个Supplier,一个Supplier可以有多个Product。
我还创建了两个DTO类:
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:
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());
}
我已经按照以下方式设置了我的两个控制器:
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>;
}
}
这里是返回的元数据。您可以看到,导航属性已正确设置:
<?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/product?$filter=Name+eq+'Product1'和/odata/supplier?$select=Id都可以正常工作。
问题出现在我尝试使用$expand的时候。如果我执行/odata/supplier?$expand=Products,当然会出错:
"指定的类型成员“Products”不受LINQ to Entities支持。仅支持初始化程序、实体成员和实体导航属性。"
更新: 我一直收到同样的问题,所以我添加了更多信息。是的,导航属性已经按照元数据信息正确设置。
这与控制器上缺少方法无关。如果我创建一个实现IODataRoutingConvention接口的类,/odata/supplier(1)/product将被解析为"~/entityset/key/navigation"。
如果我完全绕过我的DTO,只返回EF生成的类,$expand就可以直接使用。
更新2: 如果我将我的Product类更改为以下内容:
public class Product
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public virtual Supplier Supplier { get; set; }
}
然后将ProductController更改为以下内容:
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/product,我会得到预期的结果。返回一个Products数组,响应中不包含Supplier字段。生成的sql查询从Suppliers表连接和选择,如果不是下一个查询结果,这对我来说是有意义的。
如果我调用/odata/product?$select=Id,我会得到我所期望的结果。但$select将转换为一个不连接到Suppliers表的sql查询。
/odata/product?$expand=Product失败并显示不同的错误:
"The argument to DbIsNullExpression must refer to a primitive, enumeration or reference type."
如果我将我的Product Controller更改为以下内容:
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();
}
}
< p > /odata/product,/odata/product?$select=Id和/odata/product?$expand=Supplier都能正确返回结果,但显然.ToList()有点违背初衷。
我可以尝试修改Product控制器,只有在传递$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);
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/product?$select=Id或/odata/product?$expand=Supplier时,它会抛出一个序列化错误,因为returnValue不能转换为IQueryable。然而,如果我调用/odata/product,就可以进行转换。这里有什么解决方法吗?我是不是只能跳过使用自己的DTO,或者可以/应该自己实现$expand和$select?