How can I write a SQL update query with a where clause using Entity Framework .NET Core

c# entity-framework-core sql

Question

I only want to update a field based on the condition that is mentionned below. I know how to write it in SQL. I'm not sure how to accomplish this in entity framework.

UPDATE Table SET SomeDateTime = @NewDateTime WHERE Id = @MyId AND SomeDateTime > @NewDateTime

I want to use this particular query due to using a micro service architecture.

1
4
5/24/2019 8:02:45 AM

Accepted Answer

If you want use sql directly you can use ExecuteSqlCommand

If you were handling a object and then doing a update I would change a object and call SaveChanges, but that's not the case.. here is an update directly to the table, If that table has millions of rows you want perform sql to get performance on that.

example

using(var context = new SampleContext())
{
    var commandText = "UPDATE Table SET SomeDateTime = @NewDateTime WHERE Id = @MyId AND SomeDateTime > @NewDateTime";
    var newDateTime = new SqlParameter("@NewDateTime", myDateValue);
    var myId = new SqlParameter("@MyId", myIdValue);

    context.Database.ExecuteSqlCommand(commandText,  new[]{newDateTime,myId});
}
1
5/24/2019 11:13:53 AM

Popular Answer

If Id is a primary key, which means you only find one record with it, then the way I'd update it is retrieving the record, changing the value of the properties I want to edit, then saving the changes on the context.

int MyId = ...
DateTime NewDateTime = ...

using (YourDbContext dbContext = new YourDbContext())
{
   YourObject obj = dbContext.YourObjects.SingleOrDefault(item => item.Id == MyId && item.SomeDateTime > NewDateTime)
   if (obj != null)
   {
      obj.SomeDateTime = NewDateTime;
      dbContext.SaveChanges();
   }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow