In ASP.NET Core 8 MVC, rest of the form is perfect but having issue in ‘Skill’ Column in “Employee” table. I want to store a multiple skills using checkbox while submitting Employee form.
Error Message while submitting: SqlException: Invalid object name ‘EmployeeSkills’.
ApplicationDbContext
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions options) : base(options)
{
}
//Model
public DbSet<Skill> Skill { get; set; }
public DbSet<State> State { get; set; }
public DbSet<City> City { get; set; }
public DbSet<Employee> Employee { get; set; }
public DbSet<EmployeeSkill> EmployeeSkills { get; set; }
//ViewModel
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//modelBuilder.Entity<EmployeeViewModel>().HasNoKey();
modelBuilder.Entity<EmployeeSkill>()
.HasKey(es => new { es.EmployeeId, es.SkillId });
modelBuilder.Entity<EmployeeSkill>()
.HasOne(es => es.Employee)
.WithMany(e => e.EmployeeSkills)
.HasForeignKey(es => es.EmployeeId);
modelBuilder.Entity<EmployeeSkill>()
.HasOne(es => es.Skill)
.WithMany(s => s.EmployeeSkills)
.HasForeignKey(es => es.SkillId);
}
}
Skill Model
public class Skill
{
[Key]
public int SkillId { get; set; }
[Required]
public string? SkillName { get; set; }
// Navigation properties
public ICollection<EmployeeSkill> EmployeeSkills { get; set; } = new List<EmployeeSkill>();
}
Employee Data Table Definition
CREATE TABLE [dbo].[Employee] (
[EmployeeId] INT IDENTITY (202401, 1) NOT NULL,
[Name] NVARCHAR (50) NULL,
[Email] NVARCHAR (50) NULL,
[Mobile] NVARCHAR (50) NULL,
[StateId] INT NULL,
[CityId] INT NULL,
[Address] NVARCHAR (MAX) NULL,
[Gender] NVARCHAR (50) NULL,
[Skill] NVARCHAR (MAX) NULL,
[Profile] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EmployeeId] ASC),
CONSTRAINT [FK_Employee_City] FOREIGN KEY ([CityId]) REFERENCES [dbo].[City] ([CityId]),
CONSTRAINT [FK_Employee_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State] ([StateId])
);
Employee Model
public class Employee
{
[Key]
public int EmployeeId { get; set; }
[Required(ErrorMessage = "Name is required")]
public string? Name { get; set; }
[Required(ErrorMessage = "Email is required")]
[EmailAddress(ErrorMessage = "Invalid email")]
public string? Email { get; set; }
[Required(ErrorMessage = "Mobile is required")]
[MaxLength(10, ErrorMessage = "Mobile should be 10 digits")]
[MinLength(10, ErrorMessage = "Mobile should be 10 digits")]
public string? Mobile { get; set; }
[Required(ErrorMessage = "State is required")]
public int StateId { get; set; }
[Required(ErrorMessage = "City is required")]
public int CityId { get; set; }
[Required(ErrorMessage = "Address is required")]
public string? Address { get; set; }
[Required(ErrorMessage = "Gender is required")]
public string? Gender { get; set; }
[Required(ErrorMessage = "Skill is required")]
public string? Profile { get; set; }
//Nevigation Property
[BindNever]
public State? State { get; set; }
[BindNever]
public City? City { get; set; }
public ICollection<EmployeeSkill> EmployeeSkills { get; set; } = new List<EmployeeSkill>();
}
EmployeeViewModel
public class EmployeeViewModel
{
[Required (ErrorMessage = "Name is required")]
public string? Name { get; set; }
[Required(ErrorMessage = "Email is required")]
[EmailAddress(ErrorMessage = "Invalid email")]
public string? Email { get; set; }
[Required(ErrorMessage = "Mobile is required")]
[MaxLength(10, ErrorMessage = "Mobile should be 10 digits")]
[MinLength(10, ErrorMessage = "Mobile should be 10 digits")]
public string? Mobile { get; set; }
[Required(ErrorMessage = "State is required")]
public int? StateId { get; set; }
[Required(ErrorMessage = "City is required")]
public int? CityId { get; set; }
[Required(ErrorMessage = "Address is required")]
public string? Address { get; set; }
[Required(ErrorMessage = "Gender is required")]
public string? Gender { get; set; }
public List<int> SelectedSkillIds { get; set; } = new List<int>();
public string? Profile { get; set; }
public SelectList? States { get; set; }
public SelectList? Cities { get; set; }
public SelectList? Skills { get; set; }
}
EmployeeSkill Data Table Definition
CREATE TABLE [dbo].[EmployeeSkill] (
[EmployeeId] INT NOT NULL,
[SkillId] INT NOT NULL,
CONSTRAINT [PK_EmployeeSkill] PRIMARY KEY CLUSTERED ([EmployeeId] ASC, [SkillId] ASC),
CONSTRAINT [FK_EmployeeSkill_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [dbo].[Employee] ([EmployeeId]),
CONSTRAINT [FK_EmployeeSkill_Skill] FOREIGN KEY ([SkillId]) REFERENCES [dbo].[Skill] ([SkillId])
);
EmployeeSkill Model
public class EmployeeSkill
{
public int EmployeeId { get; set; }
public Employee Employee { get; set; }
public int SkillId { get; set; }
public Skill Skill { get; set; }
}
EmployeeController
public class EmployeeController : Controller
{
private readonly ApplicationDbContext _context;
private readonly IWebHostEnvironment _hostEnvironment;
public EmployeeController(ApplicationDbContext context, IWebHostEnvironment hostEnvironment)
{
_context = context;
_hostEnvironment = hostEnvironment;
}
public IActionResult Index()
{
return View();
}
// Employee/Create
[HttpGet]
public IActionResult Create()
{
var employeeViewModel = new EmployeeViewModel
{
States = new SelectList(_context.State.ToList(), "StateId", "StateName"),
Cities = new SelectList(Enumerable.Empty<City>(), "CityId", "CityName"),
Skills = new SelectList(_context.Skill.ToList(), "SkillId", "SkillName")
};
return View(employeeViewModel);
}
[HttpPost]
public async Task<IActionResult> Create(EmployeeViewModel employeeViewModel, IFormFile profileFile)
{
// If a state is selected, populate the cities for that state
if (employeeViewModel.StateId.HasValue)
{
employeeViewModel.Cities = new SelectList(_context.City
.Where(c => c.StateId == employeeViewModel.StateId).ToList(), "CityId", "CityName");
}
else
{
employeeViewModel.Cities = new SelectList(Enumerable.Empty<City>(), "CityId", "CityName");
}
// Re-populate the states dropdown for the view
employeeViewModel.States = new SelectList(_context.State.ToList(), "StateId", "StateName");
// Re-populate the skills for the view
employeeViewModel.Skills = new SelectList(_context.Skill.ToList(), "SkillId", "SkillName");
if (ModelState.IsValid)
{
var existEmployee = await _context.Employee.FirstOrDefaultAsync(e => e.Email == employeeViewModel.Email);
if (existEmployee != null)
{
ModelState.AddModelError("Email", "Email already exist");
employeeViewModel.States = new SelectList(_context.State.ToList(), "StateId", "StateName");
employeeViewModel.Cities = new SelectList(_context.City.Where(c => c.StateId == employeeViewModel.StateId).ToList(), "CityId", "CityName");
employeeViewModel.States = new SelectList(_context.Skill.ToList(), "SkillId", "SkillName");
return View(employeeViewModel);
}
// Handle file upload
if (profileFile != null)
{
string uploadDir = Path.Combine(_hostEnvironment.WebRootPath, "uploads");
Directory.CreateDirectory(uploadDir);
string fileName = Guid.NewGuid().ToString() + Path.GetExtension(profileFile.FileName);
string filePath = Path.Combine(uploadDir, fileName);
using (var fileStream = new FileStream(filePath, FileMode.Create))
{
await profileFile.CopyToAsync(fileStream);
}
employeeViewModel.Profile = fileName; // Store the file name or path
}
var employee = new Employee
{
Name = employeeViewModel.Name,
Email = employeeViewModel.Email,
Mobile = employeeViewModel.Mobile,
StateId = employeeViewModel.StateId.Value,
CityId = employeeViewModel.CityId.Value,
Address = employeeViewModel.Address,
Gender = employeeViewModel.Gender,
Profile = employeeViewModel.Profile
};
foreach (var skillId in employeeViewModel.SelectedSkillIds)
{
employee.EmployeeSkills.Add(new EmployeeSkill { SkillId = skillId });
}
_context.Employee.Add(employee);
await _context.SaveChangesAsync();
return RedirectToAction("Index");
}
// If model is invalid, reload the form with existing states and cities
employeeViewModel.States = new SelectList(_context.State.ToList(), "StateId", "StateName");
employeeViewModel.Cities = new SelectList(_context.City.Where(c => c.StateId == employeeViewModel.StateId).ToList(), "CityId", "CityName");
employeeViewModel.Skills = new SelectList(_context.Skill.ToList(), "SkillId", "SkillName");
return View(employeeViewModel);
}
}
Create.cshtml of EmployeeController
@model Candidate.ViewModel.EmployeeViewModel
@{
ViewData["Title"] = "Employee Create";
}
<h3 class="text-center pb-3">EMPLOYEE CREATE</h3>
<form asp-controller="Employee" asp-action="Create" method="post" enctype="multipart/form-data">
<div class="form-group">
<label for="Name">Name</label>
<input type="text" id="Name" name="Name" class="form-control" value="@Model.Name"/>
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label for="Email">Email</label>
<input type="email" id="Email" name="Email" class="form-control" value="@Model.Email" />
<span asp-validation-for="Email" class="text-danger"></span>
</div>
<div class="form-group">
<label for="Mobile">Mobile</label>
<input type="number" id="Mobile" name="Mobile" class="form-control" value="@Model.Mobile" />
<span asp-validation-for="Mobile" class="text-danger"></span>
</div>
<div class="form-group">
<label for="StateId">State</label>
<select id="StateId" name="StateId" class="form-control" asp-for="StateId" asp-items="Model.States" onchange="this.form.submit()">
<option value="">-- Select State --</option>
</select>
<span asp-validation-for="StateId" class="text-danger"></span>
</div>
<div class="form-group">
<label for="CityId">City</label>
<select id="CityId" name="CityId" class="form-control" asp-for="CityId" asp-items="Model.Cities">
<option value="">-- Select City --</option>
</select>
<span asp-validation-for="CityId" class="text-danger"></span>
</div>
<div class="form-group">
<label for="Address">Address</label>
<textarea id="Address" name="Address" class="form-control">@Model.Address</textarea>
<span asp-validation-for="Address" class="text-danger"></span>
</div>
<div class="form-group">
<label for="Gender">Gender</label><br />
<input type="radio" id="Male" name="Gender" value="Male" />
<label for="Male">Male</label><br />
<input type="radio" id="Female" name="Gender" value="Female" />
<label for="Female">Female</label><br />
<input type="radio" id="Other" name="Gender" value="Other" />
<label for="Other">Other</label><br />
<span asp-validation-for="Gender" class="text-danger"></span>
</div>
<div class="form-group">
<label>Skills:</label>
@foreach (var skill in Model.Skills)
{
<div>
<input type="checkbox" id="[email protected]" name="SelectedSkillIds" value="@skill.Value" />
<label for="[email protected]">@skill.Text</label>
</div>
}
<span asp-validation-for="SelectedSkillIds" class="text-danger"></span>
</div>
<div class="form-group">
<label for="Profile">Profile Picture</label>
<input type="file" id="Profile" name="profileFile" class="form-control" />
<span asp-validation-for="Profile" class="text-danger"></span>
</div>
<button type="submit" class="btn btn-primary">Create</button>
</form>
I tried without Relationship in Employee and Skill Table but while submitting it will match and verify the SkillId as want to store multiple Skill it will not work.
I am expecting
I want to store multiple Skill while submitting