Value cannot be null. (Parameter 'key')

huiali commented 4 years ago

Short summary (3-5 sentences) describing the issue.

OData URL-->GET https://localhost:5002/oq/A?$expand=B($expand=C)--> ERROR --> Value cannot be null. (Parameter 'key')

But https://localhost:5002/oq/A?$expand=B --> Successful return

Platform: NETCORE 3.1 Database: Mysql

Assemblies affected

<PackageReference Include="Microsoft.AspNetCore.OData" Version="7.3.0" /> <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.1.1" />

Reproduce steps

The simplest set of steps to reproduce the issue. If possible, reference a commit that demonstrates the issue.

Expected result

"@odata.context": "https://localhost:5002/oq/$metadata#A(B(C()))", "value": [ { "Id": 1, "Remarks": "1111", "B": [ { "Id": 1, "AId":1 "Name": "123", "C":{"Id": 5, "BId":1, "Address": "aaa" } } ]

class A =>ICollection<B> B HasMany WithOne class B=> C C HasOne WithMany class C =>ICollection<B> B

Actual result

System.ArgumentNullException: Value cannot be null. (Parameter 'key') at System.Collections.Concurrent.ConcurrentDictionary2.ThrowKeyNullException() at System.Collections.Concurrent.ConcurrentDictionary2.TryGetValue(TKey key, TValue& value) at System.Collections.Concurrent.ConcurrentDictionary2.get_Item(TKey key) at Microsoft.AspNet.OData.Query.Expressions.ModelContainer.GetModel(String id) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Query\Expressions\ModelContainer.cs:line 29 at Microsoft.AspNet.OData.Query.Expressions.SelectExpandWrapper.GetModel() in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Query\Expressions\SelectExpandWrapper.cs:line 158 at Microsoft.AspNet.OData.Query.Expressions.SelectExpandWrapper.GetEdmType() in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Query\Expressions\SelectExpandWrapper.cs:line 49 at Microsoft.AspNet.OData.Formatter.Serialization.ODataSerializerContext.GetEdmType(Object instance, Type type) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataSerializerContext.cs:line 290 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.GetResourceType(Object graph, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 1363 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(Object graph, ODataWriter writer, ODataSerializerContext writeContext, IEdmTypeReference expectedType) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 336 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 77 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSetSerializer.cs:line 147 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSetSerializer.cs:line 87 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteComplexAndExpandedNavigationProperty(IEdmProperty edmProperty, SelectItem selectItem, ResourceContext resourceContext, ODataWriter writer) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 879 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteExpandedNavigationProperties(SelectExpandNode selectExpandNode, ResourceContext resourceContext, ODataWriter writer) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 807 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(Object graph, ODataWriter writer, ODataSerializerContext writeContext, IEdmTypeReference expectedType) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 358 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSerializer.cs:line 77 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSetSerializer.cs:line 147 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSetSerializer.cs:line 87 at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObject(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\Serialization\ODataResourceSetSerializer.cs:line 56 at Microsoft.AspNet.OData.Formatter.ODataOutputFormatterHelper.WriteToStream(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, IWebApiUrlHelper internaUrlHelper, IWebApiRequestMessage internalRequest, IWebApiHeaders internalRequestHeaders, Func2 getODataMessageWrapper, Func2 getEdmTypeSerializer, Func2 getODataPayloadSerializer, Func`1 getODataSerializerContext) in S:\src\workspace\WebApi-master\WebApi-master\src\Microsoft.AspNet.OData.Shared\Formatter\ODataOutputFormatterHelper.cs:line 208

Additional detail

I found through additional source debugging：ModelID is null，it is possible to try in SQL server before

Optional, details of the root cause if known. Delete this section if you have no additional details to add.

gathogojr commented 4 years ago

@huiali To make it possible to triage your issue, kindly populate the Reproduce steps section. You don't need to supply the actual source code but just enough to reproduce the issue accurately

huiali commented 4 years ago

@gathogojr Thank you for your reply, I wrote an example that can reproduce the error, please refer to https://github.com/huiali/odata-sample

huiali commented 4 years ago

@gathogojr I attached the OData source code, EF can return data normally, OData source code error, please see the following error.

BinduShreeU commented 4 years ago

Is anyone looking into this ?

gathogojr commented 4 years ago

@huiali @BinduShreeU I spent some time investigating this issue. I was able to reproduce it with the following two MySQL EF Core providers

Pomelo.EntityFrameworkCore.MySql (Pomelo Foundation Project)
MySql.Data.EntityFrameworkCore (MySQL Project - Oracle)

However, the issue is not reproduceable with the following two providers:

Microsoft.EntityFrameworkCore.SqlServer (EF Core Project)
Microsoft.EntityFrameworkCore.Sqlite (EF Core Project)

It'll take me a little more digging to determine whether the providers are playing any part in the buggy behaviour.

I have pushed a repro with 4 test projects for each of the providers I tried. https://github.com/gathogojr/ODataWebApiIssue2124Repro

Here are the results I observed:

EF Core/SqlServer: GET http://localhost:46191/odata/Projects?$expand=Tasks($expand=Supervisor) EF Core/Sqlite: GET http://localhost:46194/odata/Projects?$expand=Tasks($expand=Supervisor) Response:

{
    "@odata.context": "http://localhost:46191/odata/$metadata#Projects(Tasks(Supervisor()))",
    "value": [
        {
            "Id": 1,
            "Name": "Pipeline Installation",
            "Tasks": [
                {
                    "Id": 1,
                    "Description": "Install Piping",
                    "ProjectId": 1,
                    "SupervisorId": 1,
                    "Supervisor": {
                        "Id": 1,
                        "Name": "Nancy Davolio"
                    }
                },
                {
                    "Id": 2,
                    "Description": "Install Couplings",
                    "ProjectId": 1,
                    "SupervisorId": 1,
                    "Supervisor": {
                        "Id": 1,
                        "Name": "Nancy Davolio"
                    }
                },
                {
                    "Id": 3,
                    "Description": "QA Inspection",
                    "ProjectId": 1,
                    "SupervisorId": 2,
                    "Supervisor": {
                        "Id": 2,
                        "Name": "Andrew Fuller"
                    }
                }
            ]
        }
    ]
}

EF Core/PomeloMySql: GET http://localhost:46192/odata/Projects?$expand=Tasks($expand=Supervisor) EF Core/OracleMySql: GET http://localhost:46193/odata/Projects?$expand=Tasks($expand=Supervisor) Response:

{
    "@odata.context": "http://localhost:46192/odata/$metadata#Projects(Tasks(Supervisor()))",
    "value": [
        {
            "Id": 1,
            "Name": "Pipeline Installation",
            "Tasks": [

Stack Trace:

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Error: An unhandled exception has occurred while executing the request.

System.ArgumentNullException: Value cannot be null. (Parameter 'key')
   at System.Collections.Concurrent.ConcurrentDictionary`2.ThrowKeyNullException()
   at System.Collections.Concurrent.ConcurrentDictionary`2.TryGetValue(TKey key, TValue& value)
   at System.Collections.Concurrent.ConcurrentDictionary`2.get_Item(TKey key)
   at Microsoft.AspNet.OData.Query.Expressions.ModelContainer.GetModel(String id)
   at Microsoft.AspNet.OData.Query.Expressions.SelectExpandWrapper.GetModel()
   at Microsoft.AspNet.OData.Query.Expressions.SelectExpandWrapper.GetEdmType()
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataSerializerContext.GetEdmType(Object instance, Type type)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.GetResourceType(Object graph, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(Object graph, ODataWriter writer, ODataSerializerContext writeContext, IEdmTypeReference expectedType)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteComplexAndExpandedNavigationProperty(IEdmProperty edmProperty, SelectItem selectItem, ResourceContext resourceContext, ODataWriter writer)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteExpandedNavigationProperties(SelectExpandNode selectExpandNode, ResourceContext resourceContext, ODataWriter writer)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(Object graph, ODataWriter writer, ODataSerializerContext writeContext, IEdmTypeReference expectedType)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObject(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatterHelper.WriteToStream(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, IWebApiUrlHelper internaUrlHelper, IWebApiRequestMessage internalRequest, IWebApiHeaders internalRequestHeaders, Func`2 getODataMessageWrapper, Func`2 getEdmTypeSerializer, Func`2 getODataPayloadSerializer, Func`1 getODataSerializerContext)
   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, Object asyncEnumerable, Func`2 reader)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultAsync>g__Logged|21_0(ResourceInvoker invoker, IActionResult result)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Warning: The response has already started, the error page middleware will not be executed.
Microsoft.AspNetCore.Server.IIS.Core.IISHttpServer: Error: Connection ID "18230571293206380545", Request ID "80000003-0000-fd00-b63f-84710c7967bb": An unhandled exception was thrown by the application.

System.ArgumentNullException: Value cannot be null. (Parameter 'key')
   at System.Collections.Concurrent.ConcurrentDictionary`2.ThrowKeyNullException()
   at System.Collections.Concurrent.ConcurrentDictionary`2.TryGetValue(TKey key, TValue& value)
   at System.Collections.Concurrent.ConcurrentDictionary`2.get_Item(TKey key)
   at Microsoft.AspNet.OData.Query.Expressions.ModelContainer.GetModel(String id)
   at Microsoft.AspNet.OData.Query.Expressions.SelectExpandWrapper.GetModel()
   at Microsoft.AspNet.OData.Query.Expressions.SelectExpandWrapper.GetEdmType()
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataSerializerContext.GetEdmType(Object instance, Type type)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.GetResourceType(Object graph, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(Object graph, ODataWriter writer, ODataSerializerContext writeContext, IEdmTypeReference expectedType)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteComplexAndExpandedNavigationProperty(IEdmProperty edmProperty, SelectItem selectItem, ResourceContext resourceContext, ODataWriter writer)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteExpandedNavigationProperties(SelectExpandNode selectExpandNode, ResourceContext resourceContext, ODataWriter writer)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteResource(Object graph, ODataWriter writer, ODataSerializerContext writeContext, IEdmTypeReference expectedType)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObject(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatterHelper.WriteToStream(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, IWebApiUrlHelper internaUrlHelper, IWebApiRequestMessage internalRequest, IWebApiHeaders internalRequestHeaders, Func`2 getODataMessageWrapper, Func`2 getEdmTypeSerializer, Func`2 getODataPayloadSerializer, Func`1 getODataSerializerContext)
   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, Object asyncEnumerable, Func`2 reader)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultAsync>g__Logged|21_0(ResourceInvoker invoker, IActionResult result)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()

I hope to complete the investigation and provide an update

gathogojr commented 4 years ago

@huiali @BinduShreeU Thank you for your patience. I just concluded my investigation and the findings are quite interesting. It'd seem that something wonky is happening somewhere between the MySql EF Core provider and MySQL.

Using the example/repo I shared in the previous update as a reference...

When you add the nested $expand like this /Projects?$expand=Tasks($expand=Supervisor), the MySql EF Core provider generates a query like the one below:

SELECT `p`.`Id`, `p`.`Name`, `t0`.`c`, `t0`.`Id`, `t0`.`Description`, `t0`.`ProjectId`, `t0`.`SupervisorId`, `t0`.`c0`, `t0`.`c1`, `t0`.`c2`, `t0`.`Id0`, `t0`.`Name`, `t0`.`c3`
FROM `Projects` AS `p`
LEFT JOIN (
    SELECT '9a3e8496-4664-4230-bc0f-6c1623cd70cc' AS `c`, `t`.`Id`, `t`.`Description`, `t`.`ProjectId`, `t`.`SupervisorId`, TRUE AS `c0`, 'Supervisor' AS `c1`, '9a3e8496-4664-4230-bc0f-6c1623cd70cc' AS `c2`, `e`.`Id` AS `Id0`, `e`.`Name`, `e`.`Id` IS NULL AS `c3`
    FROM `Tasks` AS `t`
    LEFT JOIN `Employees` AS `e` ON `t`.`SupervisorId` = `e`.`Id`
) AS `t0` ON `p`.`Id` = `t0`.`ProjectId`
ORDER BY `p`.`Id`, `t0`.`Id`;

The query looks correct, BUT, take a look at the result it returns:

+----+-----------------------+------+------+-------------------+-----------+--------------+------+------+------+------+---------------+------+
| Id | Name                  | c    | Id   | Description       | ProjectId | SupervisorId | c0   | c1   | c2   | Id0  | Name          | c3   |
+----+-----------------------+------+------+-------------------+-----------+--------------+------+------+------+------+---------------+------+
|  1 | Pipeline Installation | NULL |    1 | Install Piping    |         1 |            1 |    1 | NULL | NULL |    1 | Nancy Davolio |    0 |
|  1 | Pipeline Installation | NULL |    2 | Install Couplings |         1 |            1 |    1 | NULL | NULL |    1 | Nancy Davolio |    0 |
|  1 | Pipeline Installation | NULL |    3 | QA Inspection     |         1 |            2 |    1 | NULL | NULL |    2 | Andrew Fuller |    0 |
+----+-----------------------+------+------+-------------------+-----------+--------------+------+------+------+------+---------------+------+

Here's is the problem with the above result. Columns c and c2 both have a value of NULL instead of the expected 9a3e8496-4664-4230-bc0f-6c1623cd70cc. I can't exactly explain why MySQL Database Engine would behave this way but I did figure out a small tweak that fixes the query... If a CAST is applied to the two Guid literals in the inner query, the expected result is returned, i.e.,

SELECT `p`.`Id`, `p`.`Name`, `t0`.`c`, `t0`.`Id`, `t0`.`Description`, `t0`.`ProjectId`, `t0`.`SupervisorId`, `t0`.`c0`, `t0`.`c1`, `t0`.`c2`, `t0`.`Id0`, `t0`.`Name`, `t0`.`c3`
FROM `Projects` AS `p`
LEFT JOIN (
    SELECT CAST('9a3e8496-4664-4230-bc0f-6c1623cd70cc' AS CHAR(255)) AS `c`, `t`.`Id`, `t`.`Description`, `t`.`ProjectId`, `t`.`SupervisorId`, TRUE AS `c0`, 'Supervisor' AS `c1`, CAST('9a3e8496-4664-4230-bc0f-6c1623cd70cc' AS CHAR(255)) AS `c2`, `e`.`Id` AS `Id0`, `e`.`Name`, `e`.`Id` IS NULL AS `c3`
    FROM `Tasks` AS `t`
    LEFT JOIN `Employees` AS `e` ON `t`.`SupervisorId` = `e`.`Id`
) AS `t0` ON `p`.`Id` = `t0`.`ProjectId`
ORDER BY `p`.`Id`, `t0`.`Id`;

Result:

+----+-----------------------+--------------------------------------+------+-------------------+-----------+--------------+------+------+--------------------------------------+------+---------------+------+
| Id | Name                  | c                                    | Id   | Description       | ProjectId | SupervisorId | c0   | c1   | c2                                   | Id0  | Name          | c3   |
+----+-----------------------+--------------------------------------+------+-------------------+-----------+--------------+------+------+--------------------------------------+------+---------------+------+
|  1 | Pipeline Installation | 9a3e8496-4664-4230-bc0f-6c1623cd70cc |    1 | Install Piping    |         1 |            1 |    1 | NULL | 9a3e8496-4664-4230-bc0f-6c1623cd70cc |    1 | Nancy Davolio |    0 |
|  1 | Pipeline Installation | 9a3e8496-4664-4230-bc0f-6c1623cd70cc |    2 | Install Couplings |         1 |            1 |    1 | NULL | 9a3e8496-4664-4230-bc0f-6c1623cd70cc |    1 | Nancy Davolio |    0 |
|  1 | Pipeline Installation | 9a3e8496-4664-4230-bc0f-6c1623cd70cc |    3 | QA Inspection     |         1 |            2 |    1 | NULL | 9a3e8496-4664-4230-bc0f-6c1623cd70cc |    2 | Andrew Fuller |    0 |
+----+-----------------------+--------------------------------------+------+-------------------+-----------+--------------+------+------+--------------------------------------+------+---------------+------+

The Guid literal in columns c and c1 is used in the Select/Expand logic. It represents the ModelID. When a NULL is returned, the whole thing breaks down like a house of cards. Which is why you observed the exception being thrown on this line: Contract.Assert(ModelID != null);

You had also observed that request executes successfully in the absence of the nested $expand, i.e.: /Projects?$expand=Tasks The query generated by the MySql EF Core provider in this scenario looks as follows:

SELECT `p`.`Id`, `p`.`Name`, '8f71c9ff-7822-4fd6-be16-b882e513daee', `t`.`Id`, `t`.`Description`, `t`.`ProjectId`, `t`.`SupervisorId`, TRUE
FROM `Projects` AS `p`
LEFT JOIN `Tasks` AS `t` ON `p`.`Id` = `t`.`ProjectId`
ORDER BY `p`.`Id`, `t`.`Id`;

Result:

+----+-----------------------+--------------------------------------+------+-------------------+-----------+--------------+------+
| Id | Name                  | 8f71c9ff-7822-4fd6-be16-b882e513daee | Id   | Description       | ProjectId | SupervisorId | TRUE |
+----+-----------------------+--------------------------------------+------+-------------------+-----------+--------------+------+
|  1 | Pipeline Installation | 8f71c9ff-7822-4fd6-be16-b882e513daee |    1 | Install Piping    |         1 |            1 |    1 |
|  1 | Pipeline Installation | 8f71c9ff-7822-4fd6-be16-b882e513daee |    2 | Install Couplings |         1 |            1 |    1 |
|  1 | Pipeline Installation | 8f71c9ff-7822-4fd6-be16-b882e513daee |    3 | QA Inspection     |         1 |            2 |    1 |
+----+-----------------------+--------------------------------------+------+-------------------+-----------+--------------+------+

The result here is as expected, in spite of the LEFT JOIN and the Guid literal. This would suggest the problem presents itself when the Guid literal is present in the sub query.

In conclusion, I think the fix is required somewhere in the MySql EF Core provider. My suggested fix would be to apply a CAST to literals where they appear in a subquery but the maintainers of that library might have a neater way of fixing the issue.

Note that both Pomelo.EntityFrameworkCore.MySql and MySql.Data.EntityFrameworkCore are plagued with the same issue. These findings also explain why SqlServer and Sqlite EF Core providers don't exhibit the buggy behaviour.

Can I suggest that you raise the issue on Pomelo.EntityFrameworkCore.MySql repo and share these findings? Alternatively you could contribute a fix on the strength of the feedback I have provided here.

I'll proceed to close the ticket but feel free to re-open it if you feel there's an angle I didn't address cc. @mikepizzo

woojunfeng commented 4 years ago

@gathogojr I think there is a order by bug in MySql

work fine:

SELECT * FROM 
    (SELECT 1 AS id
    UNION
    SELECT 2 AS id
    UNION
    SELECT 3 AS id
    ) AS T
LEFT JOIN 
    (SELECT 'test' AS txt,
        T1.id AS id1,
        T2.id AS id2
     FROM
        (SELECT 1 AS id
        UNION
        SELECT 2 AS id) AS T1
    INNER JOIN 
        (SELECT 1 AS id
        UNION
        SELECT 2 AS id) AS T2
    ON T1.id = T2.id
    ) AS T3
ON T.id = T3.id1
    ORDER BY T.id

txt is null:

SELECT * FROM 
    (SELECT 1 AS id
    UNION
    SELECT 2 AS id
    UNION
    SELECT 3 AS id
    ) AS T
LEFT JOIN 
    (SELECT 'test' AS txt,
        T1.id AS id1,
        T2.id AS id2
     FROM
        (SELECT 1 AS id
        UNION
        SELECT 2 AS id) AS T1
    INNER JOIN 
        (SELECT 1 AS id
        UNION
        SELECT 2 AS id) AS T2
    ON T1.id = T2.id
    ) AS T3
ON T.id = T3.id1
    ORDER BY T.id,T3.id1

BinduShreeU commented 4 years ago

Hi @gathogojr , Thanks for trying this out.

But the issue is not in the provider, but in MySql as pointed by @woojunfeng

@mikepizzo

gathogojr commented 4 years ago

@BinduShreeU @woojunfeng Duly noted and I agree, the behaviour exhibited by MySql in this case can be best described as buggy. My suggestion for dealing with the issue was more of a hack/workaround based on the reasoning that a fix from the team behind MySql could potentially take longer.

woojunfeng commented 4 years ago

@BinduShreeU @gathogojr the query works in MariaDB 10.3.14, but fails in MySql 8.0.x Maybe MySql team fixed it in the higher versions.

BinduShreeU commented 4 years ago

But what about thousands of people using mysql (buggy version ) who wants to use oData ?

lauxjpn commented 3 years ago

I can confirm that this is a MySQL database engine bug, that I reported as [REGRESSION] Order by and constant from left joined table result in NULL value to Oracle over a year ago.

In my personal experience, the MySQL dev team works very slow when it comes to bugs, even critical ones like this. So I would not get my hopes up, that this will be fixed anytime soon. Of course it might help to post a comment on the bug-tracker issue, so they are aware that this is still a huge and critical issue.

Maybe MySql team fixed it in the higher versions.

@woojunfeng Unfortunately, they did not.

But what about thousands of people using mysql (buggy version ) who wants to use oData ?

@BinduShreeU You have currently 3 options:

Use MariaDB instead of MySQL.
Use MySQL 6, which does not contain the bug, but will reach its end-of-life in about two weeks on 2021-02-05.
Do not order the result server side. Instead, query the result unordered and order it client side.

If a CAST is applied to the two Guid literals in the inner query, the expected result is returned, i.e.,

@gathogojr That is correct. Explicitly casting all constant values does successfully work around this bug.

I will take a look at implementing this workaround in Pomelo.EntityFrameworkCore.MySql for MySQL 5.7+.

argelj289 commented 1 year ago

@huiali To make it possible to triage your issue, kindly populate the Reproduce steps section. You don't need to supply the actual source code but just enough to reproduce the issue accurately

I am experiencing the issue with $select clause

it keeps saying the source is null but it is not

first, I thought its because the data is not coming from SQL to Linq

why is this happening?

take note, the object's property I am displaying on the $select has no null value

OData / WebApi