How to perform deletion of entities based on a list of ids

Question

I am just starting with linq and entity framework in general and I have a question that may seem naive to all of the advanced users!

I have the following code :

var allDocuments = (from i in companyData.IssuedDocuments select i.IssuedDocumentId).ToList<int>();
var deletedDocuments = allDocuments.Except(updatedDocuments);

and I need to delete all the entities in companyData that their id is stored in deletedDocuments in a disconnected scenario.

Could you please show me a way to do this in an efficient manner?

score 1 · Answer 1 · edited May 23 '17 at 12:03

You could avoid fetching all the ids by specifying you only want deleted ids like this:

var deletedIds = from i in companyData.IssuedDocuments 
                 where !updatedIds.Contains(i.IssuedDocumentId)
                 select i.IssuedDocumentId

Now if companyData.IssuedDocuments is a DbSet you can tell EF to delete them like this:

foreach (var id in deletedIds)
{
    var entity = new MyEntity { Id = id };
    companyData.IssuedDocuments.Attach(entity);
    companyData.IssuedDocuments.Remove(entity);
}
dbContext.SaveChanges();

This will issue multiple DELETE statements to the database without fetching the full entities into memory.

If companyData.IssuedDocuments is your repository then you could load the full entities instead of just the ids:

var deleted = from i in companyData.IssuedDocuments 
              where !updatedIds.Contains(i.IssuedDocumentId)
              select i

foreach (var entity in deleted)
    companyData.IssuedDocuments.Delete(entity);
dbContext.SaveChanges();

Again EF issues multiple DELETE statements to the database

If you can upgrade then EF6 has introduced a RemoveRange method on the DbSet that at you could look at. It may send a single DELETE statement to the database - I haven't tried it yet.

If performance is still an issue then you have to execute sql.

References:

RemoveRange

Deleting an object without retrieving it

How should I remove all elements in a DbSet?

Christos · Answer 2 · 2013-12-18T09:41:35.253

0

companyData.RemoveAll(x=>deletedDocuments.Contains(x.Id));

I suppose the companyData is a IEnumerable type. The type T contains an Id property, which is the Id of the data. Then deletedDocuments contains the ids of all the documents that we want to remove.

One thing that's important and I should note it here is that the deletion of the documents happens in memory and it doesn't execute it in a db. Otherwise you should provide us with the version of entity framework you use and how you access you implelemnt your CRUD operations against your db.

edited Dec 18 '13 at 09:41

answered Dec 18 '13 at 09:34

Christos

53,228
8
76
108

I use entity framework 5.0 and I use the UoW pattern to access the DB. The code that worked in memory is companyData.IssuedDocuments.RemoveAll(x => deletedDocuments.Contains(x.IssuedDocumentId)); but this does not mark the records for deletion from the DB – gnl Dec 18 '13 at 09:45
@gnl Since you are using the UoW pattern, there would be a reposiroty class for CRUD operations regarding the companyData type. Right? Could you please provide me this class. In this class, I will see you implement your CRUD operations regarding the companyData entity. Thanks ! – Christos Dec 18 '13 at 10:29

score 0 · Answer 3 · edited Jan 02 '14 at 20:26

Firstly I would like to thank you all for your suggestions.

I followed Christos Paisios suggestion but I was getting all kinds of exceptions when I was trying to persist the changes to the DB and the way that I finally managed to solve the issues was by adding the following override in my DbContext class

public override int SaveChanges()
{
    var orphanedResponses = ChangeTracker.Entries().Where(
        e => (e.State == EntityState.Modified || e.State == EntityState.Added) &&
        e.Entity is IssuedDocument &&
            e.Reference("CompanyData").CurrentValue == null);
    foreach (var orphanedResponse in orphanedResponses)
    {
        IssuedDocuments.Remove(orphanedResponse.Entity as IssuedDocument);
    }

    return base.SaveChanges();
}

How to perform deletion of entities based on a list of ids

3 Answers3