primary key set by INSTEAD OF INSERT throwing error in entity framework (ASP.NET framework c#)-CodePudding

I'm just starting out with .NET and have been trying to perform CRUD operations on a table. The table has a trigger on it which sets the primary key. Now, the read, update and delete operations work without a hitch. My problem is with the create operation.

This is the trigger

create or alter trigger dbo.CreateEmpID on dbo.Employee instead of insert
as
begin

declare @NextId varchar(20)
declare @EmpName varchar(50)
declare @cur cursor
set @cur = cursor for select EmpName from inserted
open @cur
fetch next from @cur into @EmpName
while @@FETCH_STATUS = 0
begin
    select @NextId = 'EMP'   convert(varchar(20), isNull(max(convert(int, substring(EmpID, 4, len(EmpID)-3))),0) 1) from Employee
    insert into Employee(EmpID, EmpName) values (@NextId, @EmpName)
    fetch next from @cur into @EmpName
end
close @cur
deallocate @cur

end

This is the part of the code that creates new employees

//POST: Create
    [HttpPost]
    public ActionResult Create(Employee Emp)
    {
        DB.Employees.Add(Emp);
        DB.SaveChanges();
        return RedirectToAction("Index");
    }

This is the error I keep getting

System.Data.Entity.Validation.DbEntityValidationException: 'Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.'

I'm also confused by one other thing. There is another trigger on the same table that works AFTER DELETE which copies the deleted employee to another table. So is it just the INSTEAD OF triggers that have the problem or is it the code that's problematic.

Any help is appreciated.

EDIT :

I changed table to use sequences as mentioned in the first answer and it still give me the error. Also this is the detailed error

System.Data.Entity.Validation.DbEntityValidationException
  HResult=0x80131920
  Message=Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.
  Source=EntityFramework
  StackTrace:
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at Test.Controllers.HomeController.Index() in E:\vs\projects\Test\Test\Controllers\HomeController.cs:line 18
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c.<BeginInvokeSynchronousActionMethod>b__9_0(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_0.<InvokeActionMethodFilterAsynchronouslyRecursive>b__0()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_2.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()

CodePudding user response：

A number of things could go wrong here, but it's not worth troubleshooting, as you should never generate keys by querying max(id) 1 from the target table. It doesn't scale and is prone to generating deadlocks.

If you want a key like EMP1234 you can generate it with a SEQUENCE and a DEFAULT, see eg and How to generate unique incremental employee code(pattern: E0001, E0002, E0003, ... ) in the INSERT stored procedure in SQL Server?

But normally you would just use a SEQUENCE or an IDENTITY COLUMN and move on.