What is the most effective way to delete entites that satisfy a condition from a database?

user_7279624 0
Viewed 211

AFAIK, there isn't a direct way to delete entities using a predicate such as 

DbSet.RemoveWhere(() => {})

I tried a few ways to delete and don't know which is the most efficient way to do it. Could you point me in the right direction?

The first and most basic thing I tried is:

_context.Users.RemoveRange(_context.Users.Where(u => u.Name.Equals("John")));

which loads the user to the memory before deletion. I don't like this approach. The second way I tried is using the Z.EntityFramework.Plus package:

_context.Users.Where(u => u.Name.Equals("John")).Delete()

which claims to delete objects without loading them but I don't know if this is better then the first way. Also this feels wrong as the efcore should have a proper way to do this. The third attempt I made was something like this:

_context.Users.Where(u => u.Name.Equals("John")).ForEachAsync(u => _context.Users.Remove(u));

I don't know if this loads the users into the memory and it has a nasty Async in there. It also gives the following exception:

"Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack."
entity-framework-core entity-framework c#
1 Answers

I think ExecuteSqlRaw is more appropriate for you:

_context.Database.ExecuteSqlRaw("DELETE FROM Users WHERE Name = @p0", "John");

Generally Remove and RemoveRange are suitable for situations which you already have Primary Key (usually Id) of objects, in those cases you can do something like this:

_context.Users.Remove(new User {Id = id});
user_92120400
Related