locked
WebApi, OData, $expand, and projecting with EF RRS feed

  • Question

  • So I have a simple database. It has two tables: Product and Supplier. Each have a PK and a "Name" field. Product has a FK to Supplier.

    I have set up an EF model against these two tables and renamed the generated classes EF_Product and EF_Supplier respectively.

    I have also set up two other classes.

        public class Product
        {
            [Key]
            public int Id { get; set; }
    
            public string Name { get; set; }
    
            public virtual Supplier Supplier { get; set; }
        }
    
        public class Supplier
        {
            [Key]
            public int Id { get; set; }
    
            public string Name { get; set; }
        }
    
     

    I have set up my WebApiConfig.cs file like this:

        public static class 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());
            }
        }
    

    My two controllers are as follows:

    public class ProductController : ODataController
        {
            [HttpGet]
            public IHttpActionResult Get(ODataQueryOptions<Product> options)
            {
                var context = new ExampleContext();
    
                var products = 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 results = options.ApplyTo(products);
    
                return Ok(results, results.GetType());
            }
    
            private IHttpActionResult Ok(object content, Type type)
            {
                Type resultType = typeof(OkNegotiatedContentResult<>).MakeGenericType(type);
    
                return Activator.CreateInstance(resultType, content, this) as IHttpActionResult;
            }
        }
    
        public class SupplierController : ODataController
        {
            [HttpGet]
            public IHttpActionResult Get(ODataQueryOptions<Supplier> options)
            {
                var context = new ExampleContext();
    
                var suppliers = context.EF_Suppliers
                    .Select(x => new Supplier()
                    {
                        Id = x.SupplierId,
                        Name = x.SupplierName,
                    });
    
                IQueryable results = options.ApplyTo(suppliers);
    
                return Ok(results, results.GetType());
            }
    
            private IHttpActionResult Ok(object content, Type type)
            {
                Type resultType = typeof(OkNegotiatedContentResult<>).MakeGenericType(type);
    
                return Activator.CreateInstance(resultType, content, this) as IHttpActionResult;
            }
        }
    

    So $select works great now. The query that EF generates from the database also looks good.

    But $expand has issues. If I call $expand like so: product?$expand=Supplier I get the following error:

    "The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; odata=minimalmetadata; streaming=true; charset=utf-8'.","type":"System.InvalidOperationException","stacktrace":"","internalexception":{
            "message":"The argument to DbIsNullExpression must refer to a primitive, enumeration or reference type.","type":"System.ArgumentException","stacktrace":"   at System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.DbExpressionBuilder.ValidateIsNull(DbExpression argument, Boolean allowRowType)"

    If I change my controller to this (adding .ToList()), everything of course works fine, but that kinda defeats the purpose:

    public class ProductController : ODataController
        {
            [HttpGet]
            public IHttpActionResult Get(ODataQueryOptions<Product> options)
            {
                var context = new ExampleContext();
    
                var products = 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 results = options.ApplyTo(products);
    
                return Ok(results, results.GetType());
            }
    
            private IHttpActionResult Ok(object content, Type type)
            {
                Type resultType = typeof(OkNegotiatedContentResult<>).MakeGenericType(type);
    
                return Activator.CreateInstance(resultType, content, this) as IHttpActionResult;
            }
        }
    

    Does anyone have any ideas?

    • Moved by Jason Dot Wang Wednesday, October 30, 2013 5:13 AM This thread is about ADO.NET Entity Framework and LINQ to Entities
    Tuesday, October 29, 2013 8:44 PM

All replies

  • Hi LukeSigler_QLC,

      Welcome to MSDN Forum Support.

     

    You are more likely to get more efficient responses to ADO.NET Entity Framework and Linq to Entities issues at 

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework where you can contact ADO.NET Entity Framework and Linq to Entities experts.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 30, 2013 5:12 AM
  • Hello LukeSigler,

    Please have a look at the link below:

    http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/.

    And search for “4.6. Expand System Query Option ($expand)”, we could see this “A URI with a $expand System Query Option indicates that Entries associated with the Entry or Collection of Entries identified by the Resource Path section of the URI must be represented inline (i.e. eagerly loaded).”

    So that is why when you use the toList() it would work fine.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 30, 2013 7:04 AM
  • Yes, I know why ToList() is working. Thank you though. My question is why it doesn't work while I am projecting. It works fine when I call straight to EF. No need for a ToList() when I do that. But it doesn't when I am projecting the EF classes into my own DTOs.
    Wednesday, October 30, 2013 2:26 PM
  • Did you ever get an answer to this, @LukeSigler_QLC?  I am having the same problem.  See my stackoverflow question here:  http://stackoverflow.com/questions/19851959/using-the-odata-expand-query-option-with-webapi-and-a-viewmodel
    Friday, November 8, 2013 7:35 PM
  • I did not @adavsko. I had someone on Twitter recommend linqtoquerystring, but it seemed a bit hacky to me.

    https://twitter.com/Schandlich/status/396315041177145344


    Monday, November 11, 2013 5:38 PM