OData $expand, DTOs, and Entity Framework
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?
Answer
The underlying issue was fixed in EF 6.1.0. See https://entityframework.codeplex.com/workitem/826.