.Net EF I can't update the data in my db. Getting an Invalid column name exception

Question

This is the request handler I have for updating:

public async Task<UpdateEmployeesResponse> Handle(UpdateEmployeesRequest request, CancellationToken cancellationToken)
{
    var data = _context.Employees
                       .FirstOrDefault(person => person.Auth0ID == request.UpdateEmployee.Auth0Id);

    var skill = _context.Skill.FirstOrDefault(skill => skill.Id == request.UpdateEmployee.SkillLevel.Id);

    var employeeProgrammingLanguages = _context.EmployeeProgrammingLanguages.Where(_ => _.EmployeeId == data.Id);
    var programmingLanguagesList = new List<ProgrammingLanguage>();

    var element = 0;

    foreach (var programmingLanguage in request.UpdateEmployee.ProgramLanguages)
    {
        while (element < employeeProgrammingLanguages.Count())
        {
            var eProg = employeeProgrammingLanguages.ToList()[element];
            var prog = _context.ProgrammingLanguages.FirstOrDefault(_ => _.Id == eProg.ProgrammingLanguageId);//employeeProgrammingLanguages.ElementAtOrDefault(element).ProgrammingLanguageIdFirstOrDefault(_ => _.Id == eProg.ProgrammingLanguageId); 

            if (programmingLanguage.Name != "")
            {
                prog.Name = programmingLanguage.Name;
            }

            prog.Skill = programmingLanguage.Skill;

            programmingLanguagesList.Add(prog);

            _context.ProgrammingLanguages.Update(prog);
            element++;
        }
    }

    data.UpdateEmployeeInformation(request.UpdateEmployee.FirstName, request.UpdateEmployee.LastName, request.UpdateEmployee.Auth0Id, request.UpdateEmployee.Township, request.UpdateEmployee.Description, skill, programmingLanguagesList);

    _context.Employees.Update(data);

    await _context.SaveChangesAsync(cancellationToken);

    return new UpdateEmployeesResponse { UpdateEmployee = request.UpdateEmployee };
}

Updating the data.UpdateEmployeeInformation only with the basic information of an Employee works but when I try the change an associated table I get an error which you can see in the screenshot.

https://i.stack.imgur.com/MtCMX.png

Error :

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'EmployeeId'.

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.InitializeReader(DbContext _, Boolean result) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable1 source) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression1 predicate) at Involved.Cv.Service.Employee.UpdateEmployeesRequestHandler.Handle(UpdateEmployeesRequest request, CancellationToken cancellationToken) in C:\Users\Abdurrahman\Desktop\School\APLessen\IT-Case\AP-ITCASE\Involved-ITCase-AP\Involved.Cv.Service\Employee\UpdateEmployeesRequestHandler.cs:line 49 at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) at MediatR.Pipeline.RequestPostProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) at MediatR.Pipeline.RequestPreProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate1 next) at Involved.Cv.Host.Controllers.EmployeeController.Update(UpdateEmployeeInformation model, CancellationToken cancellationToken) in C:\Users\Abdurrahman\Desktop\School\APLessen\IT-Case\AP-ITCASE\Involved-ITCase-AP\Involved.Cv.Host\Controllers\EmployeeController.cs:line 40 at lambda_method(Closure , Object ) at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult() at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.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.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.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) ClientConnectionId:186bcee6-7dc4-4984-98ed-d67514c1117b Error Number:207,State:1,Class:16

EmployeeProgrammingLanguage class :

public class EmployeeProgrammingLanguage
{
        public int Id { get; private set; }
        public int? EmployeeId { get; private set; }
        public int? ProgrammingLanguageId { get; private set; }

        public EmployeeProgrammingLanguage(int? employeeId, int? programmingLanguageId)
        {
            EmployeeId = employeeId;
            ProgrammingLanguageId = programmingLanguageId;
        }
}

ProgrammingLanguage class :

public class ProgrammingLanguage
{
        public int Id { get; private set; }
        public string Name { get; set; }
        public double Skill { get; set; }

        public ProgrammingLanguage(string name, double skill)
        {
            Name = name;
            Skill = skill;
        }
}

Thanks for helping! Ill add extra information if needed, not sure if this is enough to identify the problem.

Db scripts :

Create.Table("ProgrammingLanguage")
                .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                .WithColumn("Name").AsString().NotNullable()
                .WithColumn("Skill").AsDouble().NotNullable();

Create.Table("EmployeeProgrammingLanguage")
                .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                .WithColumn("EmployeeId").AsInt32().ForeignKey("Employee", "Id")
                .WithColumn("ProgrammingLanguageId").AsInt32().ForeignKey("ProgrammingLanguage", "Id");

Create.Table("Employee")
                .WithColumn("Id").AsInt32().Identity().PrimaryKey()
                .WithColumn("FirstName").AsString(50).NotNullable()
                .WithColumn("LastName").AsString(50).NotNullable()
                .WithColumn("Email").AsString().NotNullable()
                .WithColumn("BirthDate").AsDate().NotNullable()
                .WithColumn("UpdatedTime").AsDate().Nullable()
                .WithColumn("Auth0ID").AsString(50).NotNullable()
                .WithColumn("Township").AsString(50).NotNullable()
                .WithColumn("Description").AsString().Nullable()
                .WithColumn("SkillLevelId").AsInt32().Nullable().ForeignKey("SkillLevel", "Id");

Answer 1

This looks a little suspect. Looking at the Entity and DB "Script" (Migration?)

public class EmployeeProgrammingLanguage
    {
        public int Id { get; private set; }
        public int? EmployeeId { get; private set; }
        public int? ProgrammingLanguageId { get; private set; }

        public EmployeeProgrammingLanguage(int? employeeId, int? programmingLanguageId)
        {
            EmployeeId = employeeId;
            ProgrammingLanguageId = programmingLanguageId;
        }
    }

Create.Table("EmployeeProgrammingLanguage")
    .WithColumn("Id").AsInt32().Identity().PrimaryKey()
    .WithColumn("EmployeeId").AsInt32().ForeignKey("Employee", "Id")             
    .WithColumn("ProgrammingLanguageId").AsInt32().ForeignKey("ProgrammingLanguage", "Id");

What are you using for mapping? Your entities aren't using attributes, are you using OnModelCreating or EntityTypeConfiguration classes, or relying on EF's conventions to work things out?

A few potential issues I see:

1. Private setters on EmployeeProgrammingLanguage and relying on Constructor initialization. Entities should use public or internal setters given EF will want to manage updating FKs.
2. No navigation properties for the Employee or ProgrammingLanguage in this linking entity. The migration script mentions the EmployeeId is a Foreign Key to Employee, but at the same time you're not leveraging any navigation properties for this link.
3. Null-able FKs on a many-to-many linking entity. &
4. Many-to-many linking entity being treated as a top-level entity. (DbSet in the DbContext)

Many-to-Many linking tables are typically accessed via navigation from one, or both sides of the relationship. In many cases, these tables consist of only the FKs to the related entities as a composite PK. With EF 6 this table doesn't even need to be mapped to an entity, it can be managed entirely behind the scenes. AFAIK this isn't yet supported in EF Core without a joining entity.

EF6 without joining entity:

public class Employee
{
    // ...
    public virtual ICollection<ProgrammingLanguage> ProgrammingLanguages { get; set; } = new List<ProgrammingLanguage>();
}

public class ProgrammingLanguage
{
    // ...
    public virtual ICollection<Employee> Employees { get; set; } = new List<Employee>();
}

With joining entity (EF Core or EF6 where you want additional properties in the joining table)

public class Employee
{
    // ...
    public virtual ICollection<EmployeeProgrammingLanguage> EmployeeProgrammingLanguages { get; set; } = new List<EmployeeProgrammingLanguage>();
}

public class ProgrammingLanguage
{
    // ...
    public virtual ICollection<EmployeeProgrammingLanguage> EmployeeProgrammingLanguages { get; set; } = new List<EmployeeProgrammingLanguage>();
}

With the joining entity, the FK columns would be non-null-able, and could use either a composite key or a PK column. (I.e. "Id") The downside of joining entities is just the added hassle of diving "through" the joining entity to get the details you want. With EF6 and avoiding joining entities, to review programming languages for an employee would be along the lines of .SelectMany(e => e.ProgrammingLanguages) With the joining entities it would be .SelectMany(e => e.EmployeeProgrammingLanguages.Select(epl => epl.ProgrammingLanguage))

You wouldn't normally access EmployeeProgrammingLanguage via a DbSet on the DbContext.