How to run SQL with Entity Framework?

Question

I tried to execute a command SQL for Entity Framework, tried in the following ways:

string cmd = "UPDATE t0071_compra SET t0071_status = 'Enviado' WHERE t0071_id_compra = 4 AND t0020_id_empresa = 1";
context.Database.SqlQuery<string>(cmd);
context.SaveChanges();

---

string cmd = "UPDATE t0071_compra SET t0071_status = 'Enviado' WHERE t0071_id_compra = 4 AND t0020_id_empresa = 1";
context.Database.ExecuteSqlCommand(cmd);
context.SaveChanges();

but in none of the ways worked, I’m using Mysql, what’s wrong there?

Answer 1

If you want to execute a command that does not return results (it is not a SELECT)

just use DbContext.Database.ExecuteSqlCommand, where Dbcontext is your context.

The method need not be used SaveChanges in this case, because the command will already run directly in the bank and will not affect the EF entities in the context.

Remember that after executing the command this way you need to update the entities manually (via DbContext.Entry(entity).Reload();) if you want to use the updated values in the same application Lifecycle (i.e., before discarding and restarting the context)

Here are some links talking about:

• https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113). aspx
• https://stackoverflow.com/questions/9081244/reload-an-entity-and-all-navigation-property-association-dbset-entity-framework