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

One for getting the entity.
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.

If you are using .Net Core, soft delete explained in [Global Query Filters](https://learn.microsoft.com/en-us/ef/core/querying/filters) will help you. — Balaji Kanagaraju, Sep 10 '19 at 12:21
Have a look at pit-falls of generic repository explained [here](https://stackoverflow.com/a/51781877/5779732). — Amit Joshi, Sep 10 '19 at 13:16

Flater · Answer 1 · 2019-09-10T13:02:11.240

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 IRepositoy<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.

Very good answer with detailed explanation and the pit-falls mentioned. Not related but I generally discourage using generic repository with rich ORM like EF/NH. I have discussed it [here](https://stackoverflow.com/a/51781877/5779732). — Amit Joshi, Sep 10 '19 at 13:15
dbContext.SaveChanges(); Must be called after calling the generic method! — Khalid Bin Sarower, Aug 17 '23 at 11:53
@KhalidBinSarower: Your observation is irrelevant for the topic at hand. There are several ways to decide when and how to commit (using `SaveChanges`), and I would generally not advocate for doing so inside the repository method body anyway, as this renders it impossible to provide a broader transaction or unit of work. However, it's not necessary to discuss this here as it does not relate to the question that is actually being asked (note also that the question itself does not even display any usage of `SaveChanges`) — Flater, Aug 21 '23 at 00:03

score 0 · Answer 2 · answered Sep 10 '19 at 12:20

0

You still have to fetch it. Entity Framework caches your dbSets so it's usually pretty quick. Use the same context like so:

public virtual void Delete(object id)
    {
        TEntity entityToDelete = dbSet.Find(id);
        Delete(entityToDelete);
    }

    public virtual void Delete(TEntity entityToDelete)
    {
        if (context.Entry(entityToDelete).State == EntityState.Detached)
        {
            dbSet.Attach(entityToDelete);
        }
        dbSet.Remove(entityToDelete);
    }

Where dbSet =

context.Set<TEntity>();

answered Sep 10 '19 at 12:20

user10728126

151
4

Here also it is making two calls to the database one for getting the entity and the other to delete it. When you execute the second example I have posted it is making a single query to delete the entity. The query is: DELETE [dbo].[Employees] WHERE ( [EmployeeId] = 21 ) – Rana Sep 10 '19 at 12:31
@Rana: This answer doesn't claim to only do it in one call, it's showing you how EF intends you to use it (which is indeed two calls, not one). – Flater Sep 10 '19 at 12:32
@Rana: See my answer. – Flater Sep 10 '19 at 12:36

Krishna Varma · Answer 3 · 2019-09-10T12:31:55.647

0

The current limitation of Entity Framework is, in order to update or delete an entity you have to first retrieve it into memory. However there are few alternatives to delete a specific record.

You can try ExecuteSqlCommandto delete a specific record

_dbContext.Database.ExecuteSqlCommand("Delete Employee where EmployeeId = {0}", id );

or try using EntityFramework.Extended Library to delete a specific record

_dbContext.Settings.Where(s=> s.EmployeeId == id).Delete();

edited Sep 10 '19 at 12:31

answered Sep 10 '19 at 12:27

Krishna Varma

4,238
2
10
25

Note that the linked library has not been supported for 4 years and the delete functionality is now locked behind a paid license. – Flater Sep 10 '19 at 12:30

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

3 Answers3

Using raw SQL

Making it generic