CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
SinusQuell
SinusQuell

Reputation: 21

Entity Framework Core trying to access database with property name instead of db-column field

I have the following relation in my MainContext:

modelBuilder.Entity<Project>()
    .HasMany(project => project.Messages)
    .WithMany(message => message.Projects)
    .UsingEntity<ProjectmessageProject>(
        typeBuilder => typeBuilder.HasOne(x => x.Message).WithMany().HasForeignKey(x => x.ProjectmessageId),
        typeBuilder => typeBuilder.HasOne(x => x.Project).WithMany().HasForeignKey(x => x.ProjectIdTest)
    );

I have renamed ProjectId to ProjectIdTest for testing. This is not the cause and I can reverse it. Now, when I try to access ProjectmessageProject, EF Core generates the following SQL query:

SELECT `p0`.`project_id`, `p0`.`projectmessage_id`, `p0`.`folder_id`, `p0`.`ProjectId`, `p0`.`ProjectMessageId`, `p`.`id`
      FROM `projects` AS `p`
      INNER JOIN `projectmessage_project` AS `p0` ON `p`.`id` = `p0`.`ProjectId`
      WHERE `p`.`id` = @__id_0
      ORDER BY `p`.`id`

Now, the problem here is that ProjectId and ProjectMessageId are the property names in the entity. There are no DB columns with these names. They are project_id and projectmessage_id, which are already selected.

Here is the entity:

[Table("projectmessage_project")]
public record ProjectmessageProject : IProjectmessageProject
{
    public Project? Project { get; init; }

    public ProjectMessage? Message { get; init; }

    [NotMapped]
    public UInt64 Id { get; init; }

    [Column("projectmessage_id")]
    public UInt64? ProjectmessageId { get; init; }

    [Column("project_id")]
    public UInt64? ProjectIdTest { get; init; }

    [Column("folder_id")]
    public UInt64? FolderId { get; init; }

}

As you can see as well, Id is marked as NotMapped, but EF Core still tries to access it.

This is the exception I get in the console:

Connection id "0HMMVFK574S69", Request id "0HMMVFK574S69:00000005": An unhandled exception was thrown by the application.

MySqlConnector.MySqlException (0x80004005): Unknown column 'p0.ProjectId' in 'field list'

at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 127
at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
         at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
         at MySqlConnector.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in /_/src/MySqlConnector/MySqlCommand.cs:line 272
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.<PopulateSplitCollection>g__InitializeReader|67_1[TCollection,TElement,TRelatedEntity](RelationalQueryContext queryContext, RelationalCommandCache relationalCommandCache, Boolean detailedErrorsEnabled)
         at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.PopulateSplitCollection[TCollection,TElement,TRelatedEntity](Int32 collectionId, RelationalQueryContext queryContext, IExecutionStrategy executionStrategy, Boolean detailedErrorsEnabled, SplitQueryResultCoordinator resultCoordinator, RelationalCommandCache relationalCommandCache, Func`3 childIdentifier, IReadOnlyList`1 identifierValueComparers, Func`5 innerShaper, Action`3 relatedDataLoaders)
         at lambda_method138(Closure , QueryContext , IExecutionStrategy , SplitQueryResultCoordinator )
         at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
         at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_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 ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
         at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Watch.BrowserRefresh.BrowserRefreshMiddleware.InvokeAsync(HttpContext context)
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

Thank you for any advice.

I've tried changing around the relation, adding ForeignKeys for the two Properties "Project" and Message, I've added a composite key in the context like this:

modelBuilder.Entity<ProjectmessageProject>().HasKey(x => new
        {
            x.ProjectIdTest,
            x.ProjectmessageId,
            x.FolderId
        });

When I rename "ProjectIdTest" back to "ProjectId", it actually tries to access some "ProjectId1" column in the database.

I've been trying around for like 8 hours. Nothing seems to work.

Upvotes: 1

Views: 748

Answers (1)

Eldar
Eldar

Reputation: 10790

When you add a Navigation Property you need to explicitly configure the foreign keys if your foreign key property names don't comply with the EF Core naming convention (eg : ProjectId). If you don't EF Core will generate those columns, and name them according to the convention. If you want to re-use existing columns for referential integrity you need to configure your entity like the below :


[Table("projectmessage_project")]
public record ProjectmessageProject : IProjectmessageProject
{
    [ForeignKey(nameof(ProjectIdTest))]
    public Project? Project { get; init; }

    [ForeignKey(nameof(ProjectmessageId ))]
    public ProjectMessage? Message { get; init; }

    [NotMapped]
    public UInt64 Id { get; init; }

    [Column("projectmessage_id")]
    public UInt64? ProjectmessageId { get; init; }

    [Column("project_id")]
    public UInt64? ProjectIdTest { get; init; }

    [Column("folder_id")]
    public UInt64? FolderId { get; init; }

}

Mentioned Conventions

Upvotes: 1

Related Questions