CODEWITHSUNDEEP
linq-to-entitiesentity-framework-core
Miguel Moura
Miguel Moura

Reputation: 39484

Delete entities without loading them into memory

In Entity Framework Core I have the following Entity:

public class File {
  public Int32 Id { get; set; }
  public Byte[] Content { get; set; }
  public String Name { get; set; }
}

And I have a list of files ids which I need to delete:

List<Int32> ids = new List<Int32> { 4, 6, 8 }; // Ids example

How can I delete the 3 files without loading each file Content property?

_context.Files.Remove(??);

I do not want to load each file Content property as it is big in size.

Upvotes: 55

Views: 37312

Answers (5)

Luke Vo
Luke Vo

Reputation: 20788

EF Core 7 now supports ExecuteUpdate and ExecuteDelete (Bulk updates):

// Delete all Tags (BE CAREFUL!)
await context.Tags.ExecuteDeleteAsync();

// Delete Tags with a condition
await context.Tags.Where(t => t.Text.Contains(".NET")).ExecuteDeleteAsync();

The equivalent SQL queries are:

DELETE FROM [t]
FROM [Tags] AS [t]

DELETE FROM [t]
FROM [Tags] AS [t]
WHERE [t].[Text] LIKE N'%.NET%'

Upvotes: 41

Toolkit
Toolkit

Reputation: 11119

You can try EntityFramework-Plus and Database.BeginTransaction()

var db = new YourDbContext();
var dbContextTransaction = db.Database.BeginTransaction();
ctx.Users.Where(x => x.LastLoginDate < DateTime.Now.AddYears(-2)).Delete();
//some other DB actions 
db.SaveChanges();
dbContextTransaction.Commit();

This way Delete is committed with the transaction

Update - it is now a paid version, thus the downvotes I guess

Upvotes: -5

Mohammad Komaei
Mohammad Komaei

Reputation: 9676

Install Z.EntityFramework.Extensions or Z.EntityFramework.Extensions.EFCore package according to your dotnet version.

Then use DeleteFromQuery() or DeleteFromQueryAsync() method after your query.

await _dbContext.MyTable.Where(w => w.TypeId == 5).DeleteFromQueryAsync();

DeleteFromQuery gives you access to directly execute a DELETE statement in the database and provide a HUGE performance improvement without select and load objects.

Performance Comparisons :

Operations : 1,000 Entitie - 2,000 Entities - 5,000 Entities

SaveChange : 1,000 ms - 2,000 ms - 5,000 ms

DeleteFromQuery : 1 ms - 1 ms - 1 ms

Upvotes: 6

Matt
Matt

Reputation: 911

Entity tracking can work manually and without any database call, so long as you can uniquely identify the entity.

What you are after is documented here.

var entity = new EntityModel {
   Id = yourId
};

var entry = context.Entry(entity);
entry.State = EntityState.Deleted;
context.SaveChanges();

Which is the same as ...

var entity = new EntityModel {
   Id = yourId
};

var entry = context.Entry(entity);
context.Remove(entry);
context.SaveChanges();

Upvotes: 26

Ivan Stoev
Ivan Stoev

Reputation: 205849

If you are sure the all Ids exist in the database and context does not contain (is not tracking) other entities with the same keys, you can use simple fake (stub) entities:

_context.RemoveRange(ids.Select(id => new File { Id = id }));

To avoid problem with non existing ids, you can get the existing ids from the database:

var existingIds = _context.Files.Where(f => ids.Contains(f.Id)).Select(f => f.Id).ToList();

_context.RemoveRange(existingIds.Select(id => new File { Id = id }));

To avoid tracking entity problem, you can use the FindTracked custom extension method from my answer to Delete loaded and unloaded objects by ID in EntityFrameworkCore and combine it with any of the above.

var existingIds = _context.Files.Where(f => ids.Contains(f.Id)).Select(f => f.Id).ToList();

_context.RemoveRange(
    existingIds.Select(id => _context.FindTracked(id) ?? new File { Id = id }));

Upvotes: 29

Related Questions