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, Action
1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 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.QueryingEnumerable
1.Enumerator.InitializeReader(DbContext _, Boolean result) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func
3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable
1 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, Expression
1 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, RequestHandlerDelegate
1 next) at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next) at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next) at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next) at MediatR.Pipeline.RequestPostProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next) at MediatR.Pipeline.RequestPreProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 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");
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:
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
.
User contributions licensed under CC BY-SA 3.0