How can I delete records in Entity Framework using ExecuteSqlCommand?

Question

This is my code:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOption))
{
    foreach (ManufacturecodeEntity mcodeEntity in ManufacturecodeEntities)
    {
         ManufacturecodeEntity pcodeEntity = mcodeEntity.Parent;
         pcodeEntity.IsCurrent = true;

         UnitOfWork.ManufacturecodeRepository.Update(pcodeEntity);
    }

    UnitOfWork.DbContext.Database.ExecuteSqlCommand("Delete from manufacturecodes where detailstate_id=" + Id.ToString());
    UnitOfWork.SaveChanges();

    scope.Complete();
}

But when I run to method ExecuteSqlCommand, my application stops, then throws timeout exception.

I use ExecuteSqlCommand to delete records because records is more than 1500, if I use Entity Framework Delete and SaveChanges method, it will take 60s, I can't accept the result.

So I try the ExecuteSqlCommand method to improve the performance. Now it seems there is something wrong.

Please help me, thanks.

M.S. · Accepted Answer · 2016-04-23 07:25:23Z

2

You should use SqlParameters in the ExecuteSqlCommand as:

UnitOfWork.DbContext.Database.ExecuteSqlCommand("Delete from manufacturecodes where detailstate_id=@id", new SqlParameter("@id", id);

answered Apr 23, 2016 at 7:25

M.S.

4,4531 gold badge22 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2155362 Over a year ago

same result, time out exception

user2155362 · Accepted Answer · 2016-04-23 08:01:13Z

0

I think I know correct way. I should use DbContext.Database.BeginTransaction instead of TransactionScope

answered Apr 23, 2016 at 8:01

user2155362

1,7136 gold badges22 silver badges31 bronze badges

Collectives™ on Stack Overflow

How can I delete records in Entity Framework using ExecuteSqlCommand?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related