search

OData / WebApi

OData Web API: A server library built upon ODataLib and WebApi
https://docs.microsoft.com/odata
Other
856 stars 473 forks source link

Odata expand issue with all null values in left join #2102

Open ankitpatel5989 opened 4 years ago

ankitpatel5989 commented 4 years ago

I have implemented expand with odata v4 using .net core. It is working fine but creating issue when not able to find key property in expand. Property is nullable hence EF applying left join to it. To confirm the same I also checked query in SQL profiler and that query is also working fine in SQL Server.

If I am passing all the property as select it is working fine. Let's take one example.

Model: Product{ public int prodid {get,set}; public string prodname {get,set}; public string depid {get,set}; public Department department{get,set}; }

Department{ public string depid {get,set}; public string depname{get set} }

working Expand odata url: http://localhost:5655/prod?$count=true$select=prodid$expand=department($select=depid,depname) value: { prodid:1 Department: { depid:133 depname:null } } Not working Expand odata url:

http://localhost:5655/prod?$count=true$select=prodid$expand=department Giving 500 internal server error

Data in DB: Product table: prodid |depid |prodname 1 |133 |test

No data available in Department table for depid 133

Runtime in profiler it is generating below query: select p.prodid,p.depid,t.depid, t.depname from (select p.prodid,p.prodname from product p) as [p] left join ( select d.depid, d.depname from department d) as [t] on p.depid=t.depid result: prodid |p.depid |t.depid |t.depname 1 |133 |null |null

KenitoInc commented 4 years ago

@ankitpatel5989 What version of WebApi OData stack and EF/EF Core are you using? Also can you share the metadata?

xuzhg commented 4 years ago

@ankitpatel5989 Would you please share us a repro to dig more?

At my side, i tried to create the model based on your C# model.

Below is the metadata:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
        <Schema Namespace="AspNetCore3xEndpointSample.Models" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="Product">
                <Key>
                    <PropertyRef Name="ProductId" />
                </Key>
                <Property Name="ProductId" Type="Edm.Int32" Nullable="false" />
                <Property Name="ProductName" Type="Edm.String" />
                <Property Name="Depid" Type="Edm.String" />
                <NavigationProperty Name="Department" Type="AspNetCore3xEndpointSample.Models.Department" />
            </EntityType>
            <EntityType Name="Department">
                <Key>
                    <PropertyRef Name="Depid" />
                </Key>
                <Property Name="Depid" Type="Edm.String" Nullable="false" />
                <Property Name="DepName" Type="Edm.String" />
            </EntityType>
        </Schema>
        <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityContainer Name="Container">
                <EntitySet Name="Products" EntityType="AspNetCore3xEndpointSample.Models.Product">
                    <NavigationPropertyBinding Path="Department" Target="Departments" />
                </EntitySet>
                <EntitySet Name="Departments" EntityType="AspNetCore3xEndpointSample.Models.Department" />
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

My products table: image

My department table: image

The query works fine at my side: http://localhost:5000/odata/Products?$expand=Department

{
    "@odata.context": "http://localhost:5000/odata/$metadata#Products(Department())",
    "value": [
        {
            "ProductId": 1,
            "ProductName": "Alexander",
            "Depid": "1",
            "Department": {
                "Depid": "1",
                "DepName": "Chemistry"
            }
        },
        {
            "ProductId": 2,
            "ProductName": "Alonso",
            "Depid": "1",
            "Department": {
                "Depid": "1",
                "DepName": "Chemistry"
            }
        },
        {
            "ProductId": 3,
            "ProductName": "Anand",
            "Depid": "2",
            "Department": {
                "Depid": "2",
                "DepName": "Microeconomics"
            }
        },
        {
            "ProductId": 4,
            "ProductName": "abcdae",
            "Depid": "133",
            "Department": null
        },
        {
            "ProductId": 5,
            "ProductName": "455",
            "Depid": "1",
            "Department": {
                "Depid": "1",
                "DepName": "Chemistry"
            }
        }
    ]
}
ankitpatel5989 commented 4 years ago

Thank you very much for investigating the issue.

Few findings from my side: Sorry but I can't share my actual model with you. One thing I observed we have below properties with mentioned datatype exist. public byte? AllowSubDept { get; set; } //(DBType:tinyint, null) public string DepName { get; set; } //(DBType: varchar(80), null) public DateTime? AddedDate { get; set; } //(DBType: smalldatetime, null) public short? SecType { get; set; } //(DBType:smallint, null)

Can you please retry after adding those in Department table?

We also get below internal error which we debug it in our visual studio: An unhandled exception occurred while processing the request. InvalidOperationException: The EDM instance of type '[Domain.Models.Department Nullable=True]' is missing the property 'depid'.

ankitpatel5989 commented 4 years ago

I missed one more change in Department entitytype. In my metadata depid is nullable. In your investigation its not nullable (Property Name="Depid" Type="Edm.String" Nullable="false").

ankitpatel5989 commented 4 years ago

What version of WebApi OData stack and EF/EF Core are you using? Odata v4 and EF Core (2.2.6) Also can you share the metadata? Sorry but I cant share actual one. But i mentioned changes here which not matched with my metadata.

ankitpatel5989 commented 4 years ago

Do you have any update?

Facing similar issue in other page as well. Here its not giving error but getting same record multiple time instead of getting all records in $expand.

ankitpatel5989 commented 4 years ago

Can you please share code where you defined relationship?

nick-romano0 commented 3 years ago

So I had a very similar issue, and noticed that the table that I was expanding to had a EFCore Query Filter on it.

When the parent record had an {childtable}-id that was filtered out of the expanded table (via the Query Filter), the record was not returned.

So OData did not return records that contained an ID that was filtered out via the QueryFilter.

But what also happens is that the $top and $count parameters don't honor that filter. So it would return a count of 14, while only returning 4 values in the value property.

wiredbarb commented 3 years ago

After updating from 7.3.0 to 7.5.2 we are experiencing a similar issue which I suppose to have the same cause. In our case this affects nullable complex properties.

Metadata (abbreviated for clarity):

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>     
        <Schema Namespace="AspNetSample.TypeDetails" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <ComplexType Name="SAContractDueTypeDetail">
                <Property Name="Description" Type="Edm.String"/>
                <Property Name="Value" Type="Edm.Int64" Nullable="false"/>
                <Property Name="Abbrev" Type="Edm.String"/>
                <Property Name="Text" Type="Edm.String"/>
            </ComplexType>
        </Schema>       
        <Schema Namespace="AspNetSample.ServiceOrder" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="SAContract">
                <Key>
                    <PropertyRef Name="Id"/>
                </Key>
                <Property Name="Due" Type="AspNetSample.TypeDetails.SAContractDueTypeDetail"/>
                <Property Name="Id" Type="Edm.Int64" Nullable="false"/>
            </EntityType>           
        </Schema>
        <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityContainer Name="Container">
                <EntitySet Name="SAContracts" EntityType="AspNetSample.ServiceOrder.SAContract"/>
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

The property Due having a null value is a common use case. Where this would correctly result in {"Id":15,"Due":null} with 7.3.0, it now throws

System.InvalidOperationException: The EDM instance of type '[AspNetSample.TypeDetails.SAContractDueTypeDetail Nullable=True]' is missing the property 'Description'.
at Microsoft.AspNet.OData.ResourceContext.GetPropertyValue(System.String propertyName) at offset 73
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.CreateStructuralProperty(Microsoft.OData.Edm.IEdmStructuralProperty structuralProperty, Microsoft.AspNet.OData.ResourceContext resourceContext) at offset 92
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.CreateStructuralPropertyBag(Microsoft.AspNet.OData.Formatter.Serialization.SelectExpandNode selectExpandNode, Microsoft.AspNet.OData.ResourceContext resourceContext) at offset 113
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.CreateResource(Microsoft.AspNet.OData.Formatter.Serialization.SelectExpandNode selectExpandNode, Microsoft.AspNet.OData.ResourceContext resourceContext) at offset 84
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(System.Object graph, Microsoft.OData.ODataWriter writer, Microsoft.AspNet.OData.Formatter.Serialization.ODataSerializerContext writeContext, Microsoft.OData.Edm.IEdmTypeReference expectedType) at offset 54
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteComplexProperties(Microsoft.AspNet.OData.Formatter.Serialization.SelectExpandNode selectExpandNode, Microsoft.AspNet.OData.ResourceContext resourceContext, Microsoft.OData.ODataWriter writer) at offset 94
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(System.Object graph, Microsoft.OData.ODataWriter writer, Microsoft.AspNet.OData.Formatter.Serialization.ODataSerializerContext writeContext, Microsoft.OData.Edm.IEdmTypeReference expectedType) at offset 119
at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(System.Collections.IEnumerable enumerable, Microsoft.OData.Edm.IEdmTypeReference resourceSetType, Microsoft.OData.ODataWriter writer, Microsoft.AspNet.OData.Formatter.Serialization.ODataSerializerContext writeContext) at offset 163
at Microsoft.AspNet.OData.Formatter.ODataOutputFormatterHelper.WriteToStream(System.Type type, System.Object value, Microsoft.OData.Edm.IEdmModel model, Microsoft.OData.ODataVersion version, System.Uri baseAddress, System.Net.Http.Headers.MediaTypeHeaderValue contentType, Microsoft.AspNet.OData.Interfaces.IWebApiUrlHelper internaUrlHelper, Microsoft.AspNet.OData.Interfaces.IWebApiRequestMessage internalRequest, Microsoft.AspNet.OData.Interfaces.IWebApiHeaders internalRequestHeaders, System.Func`2 getODataMessageWrapper, System.Func`2 getEdmTypeSerializer, System.Func`2 getODataPayloadSerializer, System.Func`1 getODataSerializerContext) at offset 603
at Microsoft.AspNet.OData.Formatter.ODataMediaTypeFormatter.WriteToStreamAsync(System.Type type, System.Object value, System.IO.Stream writeStream, System.Net.Http.HttpContent content, System.Net.TransportContext transportContext, System.Threading.CancellationToken cancellationToken) at offset 349
at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__22.MoveNext() at offset 144
JarLowrey commented 3 years ago

When my expand query was missing results where my nullable Id was actually null, it turned out I had not created my code-first migrations correctly. I had used Guid instead of Guid?.

The generated migration was using NOT NULL DEFAULT '000-..., so the error somehow escaped DB detection. Only showed up on my API Odata calls