Remove an entity without fetching it in the generic repository pattern entity framework

Question

I am trying to delete an entity of Employee from the database which contains different tables like Employee, Project, Skills using a generic repository pattern.

namespace Information.Repository
{
    public class IRepositoy<TEntity> : IRepository<TEntity> where TEntity : class
    {
        private readonly ApplicationDbContext _dbContext;

        public IRepositoy(ApplicationDbContext dbContext)
        {
            _dbContext = dbContext;
        }
        public void Remove(int id)
        {
            TEntity element = _dbContext.Set<TEntity>().Find(id);
            _dbContext.Set<TEntity>().Remove(element);
        }
    }
}

When the above Remove method is called it makes two database call

1. One for getting the entity.

2. Second for deleting it.

I have found the query like the below one which executes with single SQL query when the entity type(Employee or Project or Skill) is known

 public void Remove(int id)
        {
            Employee employee = new Employee { EmployeeId = id };
            _dbContext.Entry(employee).State = EntityState.Deleted;
        }

can anyone please suggest me how to delete an entity without fetching it using a generic repository pattern similar to the above example.

Answer 1

Using raw SQL

Entity Framework doesn't allow you to delete an object that you haven't loaded (or attached). This also extends to conditional deletes (e.g. deleting all users named John) as it requires you to load the users before deleting them.

You can get around this by executing raw SQL. It's not ideal as you tend to use EF so you don't have to write SQL, but the lack of a decent delete behavior (without loading) makes this an acceptable solution.

Something along the lines of:

using (var context = new FooContext())
{
    var command = "DELETE * FROM dbo.Foos WHERE Id = 1";

    context
        .Database
        .ExecuteSqlCommand(command);
} 

Where relevant, don't forget about SQL injection protection. However, it's usually a non-issue for simple deletes as the FK is usually a GUID or int, which doesn't expose you to injection attacks.

---

Making it generic

The example you posted works as well, but you're probably not using it because it can't easily be made generic-friendly.

What I tend to do in all my EF projects is to have an (abstract) base class for all my entities, something along the lines of:

public class BaseEntity
{
    public int Id { get; set; }

    public DateTime CreatedOn { get; set; }
    public string CreatedBy { get; set; }

    public DateTime? UpdatedOn { get; set; }
    public string UpdatedBy { get; set; }
}

An interface would also work, I just prefer a base class here.

The audit fields are not part of this answer but they do showcase the benefits of having a base class.

When all your entities inherit from the same base class, you can put a generic type constraint on your repositories which ensures that the generic type has an Id property:

public class Repository<TEntity> : IRepository<TEntity> where TEntity : BaseEntity

At which point you can generically implement your proposed solution:

public void Remove(TEntity obj)
{
    dbContext.Entry(obj).State = EntityState.Deleted;
}

You can also specify a parameterless constructor type constraint:

where TEntity : BaseEntity, new()

which enables you to instantiate your generic type as well:

public void Remove(int id)
{
    TEntity obj = new TEntity() { Id = id };
    dbContext.Entry(obj).State = EntityState.Deleted;
}

Note
There is a generic raw SQL solution as well, but I've omitted it as it is more complex because it requires you to retrieve the table name based on the entity type.
The raw SQL variant is only valuable in cases where you want to execute conditional deletes (e.g. removing all entities whose id is an even number).

However, since most conditional deletes are entity-specific, this means that you generally don't need to make them generic, which makes the raw SQL approach more viable as you only have to implement it in a specific repository and not the generic one.