none
EF 6 / Web API 2.2 / OData RRS feed

  • Question

  • Hi,

    I have EF 6 / Web API 2.2 / OData working fine when a collection of a single entity is retrieved :)

    GET /odata/Property

    HTTP/1.1 200 OK
    Cache-Control: no-cache
    Pragma: no-cache
    Content-Type: application/json; odata=minimalmetadata; streaming=true; charset=utf-8
    Expires: -1
    Server: Microsoft-IIS/7.5
    DataServiceVersion: 3.0
    X-AspNet-Version: 4.0.30319
    X-Powered-By: ASP.NET
    Access-Control-Allow-Origin: *
    Access-Control-Allow-Methods: GET,PUT,POST,DELETE,OPTIONS
    Access-Control-Allow-Headers: Content-Type
    Date: Thu, 19 Jun 2014 23:03:05 GMT
    Content-Length: 2596

    {
      "odata.metadata":"http://czielinski-dt.anguslan/odata/$metadata#Property","value":[
        {
          "PropertyID":"69837934-22e1-e311-88c5-a0481c9efc87","PropertyName":"Property of State Farm Lloyds","PropertyCity":"Dallas","StateID":"ffe00059-20e1-e311-88c5-a0481c9efc87","PropertyFootage":"200000.00","CompanyID":"abd52337-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACCo="
        },{
          "PropertyID":"0f4b9c8f-22e1-e311-88c5-a0481c9efc87","PropertyName":"Property of Texas Farmers Insurance Company","PropertyCity":"Houston","StateID":"ffe00059-20e1-e311-88c5-a0481c9efc87","PropertyFootage":"350000.00","CompanyID":"acd52337-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACCs="
        },{
          "PropertyID":"fd34fcb4-22e1-e311-88c5-a0481c9efc87","PropertyName":"Property of Allstate Texas Lloyd's","PropertyCity":"Dallas","StateID":"ffe00059-20e1-e311-88c5-a0481c9efc87","PropertyFootage":"275000.00","CompanyID":"add52337-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACCw="
        },{
          "PropertyID":"44d8a4f0-22e1-e311-88c5-a0481c9efc87","PropertyName":"Property of USAA Texas Lloyd's Company","PropertyCity":"Dallas","StateID":"ffe00059-20e1-e311-88c5-a0481c9efc87","PropertyFootage":"565300.00","CompanyID":"0a4d7740-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACC0="
        },{
          "PropertyID":"d420e41a-23e1-e311-88c5-a0481c9efc87","PropertyName":"Property of United Services Automobile Association","PropertyCity":"Washington","StateID":"7885e573-1fe1-e311-88c5-a0481c9efc87","PropertyFootage":"1200300.00","CompanyID":"12ecc946-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACC4="
        },{
          "PropertyID":"4f9cb25c-23e1-e311-88c5-a0481c9efc87","PropertyName":"Property of Allstate Fire and Casualty Insurance Company","PropertyCity":"Elberton","StateID":"f106f384-1fe1-e311-88c5-a0481c9efc87","PropertyFootage":"220375.00","CompanyID":"d44d214d-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACC8="
        },{
          "PropertyID":"8c3b4287-23e1-e311-88c5-a0481c9efc87","PropertyName":"Property of Chubb Lloyds Insurance Company of Texas","PropertyCity":"Houston","StateID":"ffe00059-20e1-e311-88c5-a0481c9efc87","PropertyFootage":"1375220.00","CompanyID":"d54d214d-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACDA="
        },{
          "PropertyID":"d689cdfe-23e1-e311-88c5-a0481c9efc87","PropertyName":"Property of Safeco Insurance Company of Indiana","PropertyCity":"Memphis","StateID":"fee00059-20e1-e311-88c5-a0481c9efc87","PropertyFootage":"480375.00","CompanyID":"d64d214d-21e1-e311-88c5-a0481c9efc87","Timestamp":"AAAAAAAACDE="
        }
      ]
    }

    However, I am having considerable issues trying to retrieve a list of entities with $expand -ed entity attributes.  For example, getting the state name that corresponds to the "StateID" listed above.

    GET /odata/Property()?$expand=State

    HTTP/1.1 400 Bad Request
    Cache-Control: no-cache
    Pragma: no-cache
    Content-Type: application/xml; charset=utf-8
    Expires: -1
    Server: Microsoft-IIS/7.5
    DataServiceVersion: 3.0
    X-AspNet-Version: 4.0.30319
    X-Powered-By: ASP.NET
    Access-Control-Allow-Origin: *
    Access-Control-Allow-Methods: GET,PUT,POST,DELETE,OPTIONS
    Access-Control-Allow-Headers: Content-Type
    Date: Thu, 19 Jun 2014 23:02:38 GMT
    Content-Length: 2072

    <?xml version="1.0" encoding="utf-8"?>
    <m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
      <m:code />
      <m:message xml:lang="en-US">The query specified in the URI is not valid. Could not find a property named 'State' on type 'System.Web.Http.OData.Query.Expressions.SelectAllAndExpand_1OfProperty'.</m:message>
      <m:innererror>
        <m:message>Could not find a property named 'State' on type 'System.Web.Http.OData.Query.Expressions.SelectAllAndExpand_1OfProperty'.</m:message>
        <m:type>Microsoft.Data.OData.ODataException</m:type>
        <m:stacktrace>   at Microsoft.Data.OData.Query.SyntacticAst.ExpandBinder.GenerateExpandItem(ExpandTermToken tokenIn)&#xD;
       at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()&#xD;
       at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()&#xD;
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)&#xD;
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)&#xD;
       at Microsoft.Data.OData.Query.SyntacticAst.ExpandBinder.Bind(ExpandToken tokenIn)&#xD;
       at Microsoft.Data.OData.Query.SelectExpandSemanticBinder.Parse(IEdmEntityType elementType, IEdmEntitySet entitySet, ExpandToken expandToken, SelectToken selectToken, ODataUriParserConfiguration configuration)&#xD;
       at System.Web.Http.OData.Query.Validators.SelectExpandQueryValidator.Validate(SelectExpandQueryOption selectExpandQueryOption, ODataValidationSettings validationSettings)&#xD;
       at System.Web.Http.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)&#xD;
       at System.Web.Http.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions)&#xD;
       at System.Web.Http.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor)&#xD;
       at System.Web.Http.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)</m:stacktrace>
      </m:innererror>
    </m:error>

    Answering the most obvious question -- YES, there is a property named 'State'.

        using System;
        using System.Collections.Generic;

        public partial class Property
        {
            public Property()
            {
                this.SystemUserProperties = new HashSet<SystemUserProperty>();
            }

            public System.Guid PropertyID { get; set; }
            public string PropertyName { get; set; }
            public string PropertyCity { get; set; }
            public System.Guid StateID { get; set; }
            public decimal PropertyFootage { get; set; }
            public System.Guid CompanyID { get; set; }
            public byte[] Timestamp { get; set; }

            public virtual Company Company { get; set; }
            public virtual State State { get; set; }
            public virtual ICollection<SystemUserProperty> SystemUserProperties { get; set; }
        }

    I have tried setting LazyLoadingEnabled to true and false.

    If I were to guess at what is wrong... I think I need to add something to the OData EDM to tell it about the foreign-key relationship...

            builder.EntitySet<State>("State");
            builder.Entity<State>().HasKey(record => record.StateID);

            config.Routes.MapODataServiceRoute("odata", "odata", builder.GetEdmModel());

    Or modify the controller and tell it that it must load the State information as well.

            public IQueryable<Property> GetProperty()
            {
           return DataContext.Properties;
            }

    But I am really guessing at this point and have not been able to find a solution with bing.com.

    Anyone have any ideas on how to make (OData) $expand work with EF 6 & Web API 2.2?

    GET /odata/Property()?$expand=State

    Thursday, June 19, 2014 11:38 PM

Answers

  • Hello,

    >> but I would really like to know if the OData $expand actually works with EF 6 & Web API 2.

    Yes, it could both with WebAPI 2 and WebAPI 2.2

    As you described, I installed the WebAPI 2.2 for Odata with this blog and try to reproduce your issue:

    http://blogs.msdn.com/b/webdev/archive/2014/03/13/getting-started-with-asp-net-web-api-2-2-for-odata-v4-0.aspx

    Whenever I queried from the mater table or from sub table as below with expand syntax:

    It could return correct data.

    http://localhost:17318/odata/Order?$expand=OrderDetails
    
    {
    
      "odata.metadata":"http://localhost:17318/odata/$metadata#Order","value":[
    
        {
    
          "OrderDetails":[
    
            {
    
              "OrderDetailID":1,"OrderDetailName":"1","OrderID":1
    
            },{
    
              "OrderDetailID":2,"OrderDetailName":"2","OrderID":1
    
            }
    
          ],"OrderID":1,"OrderName":"1"
    
        },{
    
          "OrderDetails":[
    
            {
    
              "OrderDetailID":3,"OrderDetailName":null,"OrderID":2
    
            },{
    
              "OrderDetailID":4,"OrderDetailName":null,"OrderID":2
    
            }
    
          ],"OrderID":2,"OrderName":"2"
    
        },{
    
          "OrderDetails":[
    
            
    
          ],"OrderID":3,"OrderName":"3"
    
        }
    
      ]
    
    }
    
    http://localhost:17318/odata/OrderDetail?$expand=Order
    
    {
    
      "odata.metadata":"http://localhost:17318/odata/$metadata#OrderDetail","value":[
    
        {
    
          "Order":{
    
            "OrderID":1,"OrderName":"1"
    
          },"OrderDetailID":1,"OrderDetailName":"1","OrderID":1
    
        },{
    
          "Order":{
    
            "OrderID":1,"OrderName":"1"
    
          },"OrderDetailID":2,"OrderDetailName":"2","OrderID":1
    
        },{
    
          "Order":{
    
            "OrderID":2,"OrderName":"2"
    
          },"OrderDetailID":3,"OrderDetailName":null,"OrderID":2
    
        },{
    
          "Order":{
    
            "OrderID":2,"OrderName":"2"
    
          },"OrderDetailID":4,"OrderDetailName":null,"OrderID":2
    
        }
    
      ]
    
    }
    

    My entities class are below, and I use database first approach:

    public partial class Order
    
        {
    
            public Order()
    
            {
    
                this.OrderDetails = new HashSet<OrderDetail>();
    
            }
    
        
    
            public int OrderID { get; set; }
    
            public string OrderName { get; set; }
    
        
    
            public virtual ICollection<OrderDetail> OrderDetails { get; set; }
    
        }
    
    public partial class OrderDetail
    
        {
    
            public int OrderDetailID { get; set; }
    
            public string OrderDetailName { get; set; }
    
            public int OrderID { get; set; }
    
        
    
            public virtual Order Order { get; set; }
    
        }
    

    >> If I were to guess at what is wrong... I think I need to add something to the OData EDM to tell it about the foreign-key relationship...

    No, we do not need to, the relationship is already configured in the model.

    >>Is anyone willing to post a Web (Server) Application and client that demonstrates the use of OData $expand using EF 6.x / Web API 2.x / OData 5.x ???

    Please do not ask for an application, it is beyond the scope of our support. You can try it yourself and if you meet any issue, and then posting it here.

    If I misunderstand, please let me know.

    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.

    Monday, June 23, 2014 3:20 AM
    Moderator

All replies

  • Creating a SQL Server "View" (-Model) of the data resolves the issue, but I would really like to know if the OData $expand actually works with EF 6 & Web API 2.

    Perhaps I should rephrase my question?

    Is anyone willing to post a Web (Server) Application and client that demonstrates the use of OData $expand using EF 6.x / Web API 2.x / OData 5.x ???
    Friday, June 20, 2014 2:38 AM
  • Hello,

    >> but I would really like to know if the OData $expand actually works with EF 6 & Web API 2.

    Yes, it could both with WebAPI 2 and WebAPI 2.2

    As you described, I installed the WebAPI 2.2 for Odata with this blog and try to reproduce your issue:

    http://blogs.msdn.com/b/webdev/archive/2014/03/13/getting-started-with-asp-net-web-api-2-2-for-odata-v4-0.aspx

    Whenever I queried from the mater table or from sub table as below with expand syntax:

    It could return correct data.

    http://localhost:17318/odata/Order?$expand=OrderDetails
    
    {
    
      "odata.metadata":"http://localhost:17318/odata/$metadata#Order","value":[
    
        {
    
          "OrderDetails":[
    
            {
    
              "OrderDetailID":1,"OrderDetailName":"1","OrderID":1
    
            },{
    
              "OrderDetailID":2,"OrderDetailName":"2","OrderID":1
    
            }
    
          ],"OrderID":1,"OrderName":"1"
    
        },{
    
          "OrderDetails":[
    
            {
    
              "OrderDetailID":3,"OrderDetailName":null,"OrderID":2
    
            },{
    
              "OrderDetailID":4,"OrderDetailName":null,"OrderID":2
    
            }
    
          ],"OrderID":2,"OrderName":"2"
    
        },{
    
          "OrderDetails":[
    
            
    
          ],"OrderID":3,"OrderName":"3"
    
        }
    
      ]
    
    }
    
    http://localhost:17318/odata/OrderDetail?$expand=Order
    
    {
    
      "odata.metadata":"http://localhost:17318/odata/$metadata#OrderDetail","value":[
    
        {
    
          "Order":{
    
            "OrderID":1,"OrderName":"1"
    
          },"OrderDetailID":1,"OrderDetailName":"1","OrderID":1
    
        },{
    
          "Order":{
    
            "OrderID":1,"OrderName":"1"
    
          },"OrderDetailID":2,"OrderDetailName":"2","OrderID":1
    
        },{
    
          "Order":{
    
            "OrderID":2,"OrderName":"2"
    
          },"OrderDetailID":3,"OrderDetailName":null,"OrderID":2
    
        },{
    
          "Order":{
    
            "OrderID":2,"OrderName":"2"
    
          },"OrderDetailID":4,"OrderDetailName":null,"OrderID":2
    
        }
    
      ]
    
    }
    

    My entities class are below, and I use database first approach:

    public partial class Order
    
        {
    
            public Order()
    
            {
    
                this.OrderDetails = new HashSet<OrderDetail>();
    
            }
    
        
    
            public int OrderID { get; set; }
    
            public string OrderName { get; set; }
    
        
    
            public virtual ICollection<OrderDetail> OrderDetails { get; set; }
    
        }
    
    public partial class OrderDetail
    
        {
    
            public int OrderDetailID { get; set; }
    
            public string OrderDetailName { get; set; }
    
            public int OrderID { get; set; }
    
        
    
            public virtual Order Order { get; set; }
    
        }
    

    >> If I were to guess at what is wrong... I think I need to add something to the OData EDM to tell it about the foreign-key relationship...

    No, we do not need to, the relationship is already configured in the model.

    >>Is anyone willing to post a Web (Server) Application and client that demonstrates the use of OData $expand using EF 6.x / Web API 2.x / OData 5.x ???

    Please do not ask for an application, it is beyond the scope of our support. You can try it yourself and if you meet any issue, and then posting it here.

    If I misunderstand, please let me know.

    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.

    Monday, June 23, 2014 3:20 AM
    Moderator