What is the best way to implement update row if it exists, else insert new row logic using Entity Framework?
Below is what I have done so far. I want to check, if any field in the existing employee database has changed then only update that record or if it is a new one add as a new row.
Ex- Update the job title if it has changed, or add it as a new line if a new employee is added
//DbContext
public class DataContext : DbContext
{
public static string providerName = "System.Data.SqlClient";
public DbSet<DisplayAPIDataEmployee>? Employee { get; set; }
protected override void OnConfiguring(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder optionBuilder)
{
optionBuilder.UseSqlServer("Server=;Initial Catalog = ;user id = ;password=");
}
protected override void OnModelCreating(Microsoft.EntityFrameworkCore.ModelBuilder modelBuilder)
{
modelBuilder.Entity<DisplayAPIDataEmployee>().ToTable("Employee", e => e.IsTemporal());
}
}
// Data model
[Table("Employee")]
public class DisplayAPIDataEmployee
{
public DisplayAPIDataEmployee()
{
createdOn = DateTime.Now;
}
public DateTime ?createdOn { get; set; }
public string ?displayName { get; set; }
public string ?shortBirthDate { get; set; }
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string employee_id { get; set; }
}
CodePudding user response:
Inject the DbContext class into your controller and handle your logic in a controller method
private readonly DataContext _context;
public Controller(DataContext _context) => this._context = _context;
...
// rest of your code
...
public void Test(string employee_id) {
using DataContext dbContext = _context;
using IDbContextTransaction transaction = dbContext.Database.BeginTransaction();
try {
DisplayAPIDataEmployee? employee = dbContext.Employee.FirstOrDefault(e => e.employee_id.Equals(employee_id));
if (employee is null) {
// add employee
DisplayAPIDataEmployee add_employee = new(); //
add_employee.employee_id = "";
dbContext.Employee.AddRange(add_employee);
dbContext.SaveChanges();
}
else {
employee.employee_id = ""; // update employee property value
dbContext.SaveChanges(); // entity 'employee' is tracked by EF Core and any saved changes to it is reflected to entity in Database.
}
transaction.Commit(); // commit all save changes if successful
}
catch (Exception ex)
{
transaction.Rollback(); // rollback in case of errors
dbContext.ChangeTracker.Clear();
// Log error
}
}