locked
How to update entity without fetching it first RRS feed

  • Question

  • User1501362304 posted

    Hi,

    I've requirement to update entity without selecting it first to avoid 2 database hits. I've googled and read a lot on this topic, all say to use Attach method but that does not seem working if my table has non nullable columns and I try to update entity without passing values in them.

    My [User] table in database has Id (primary key), Name (Not NULL), Image (NULL) columns. Now if I try to edit the row using below sample code then it gives entity framework validation error for Name field that "The field Name is required". I am using Entity Framework 6.1

    using(DBEntities DB = new DBEntities())
    {
      User user = new User(){ Id = 1 };
      DB.Users.Attach(user);
      user.Image = "";
      DB.SaveChanges();
    }

    Thanks,
    Vikas

    Thursday, February 6, 2020 5:33 PM

All replies

  • User1120430333 posted

    I believe in EF 6 that you must get the object for update, make a change to it and then save doing it all in the same context, a connected state,  so that change tracking marks the object as modified. The Attach doesn't work in EF 6 for update like it did for previous versions of EF where you could just Attach() an object,  not doing any change tracking and update the object in the database. 

    https://www.entityframeworktutorial.net/crud-operation-in-connected-scenario-entity-framework.aspx

    You could try to set the EntityState manually doing an Attach without doing a get of the object  to see if that works, but I don't think it will work with the Attach.

    But also, you can use the disconnected scenario.

    https://www.entityframeworktutorial.net/entityframework6/save-disconnected-entity-in-entity-framework.aspx

    Disconnected example..... The 'pc' stands for PublishingCompanyContext that is dependency injected into the class that the method is using, which is the same kind of  context usage as your DBEntities.

    public async Task Update(DtoPayroll dto)
            {
                var payroll = new Payroll
                {
                    PayrollId = dto.PayrollId,
                    AuthorId = dto.AuthorId,
                    Salary = dto.Salary
                };
    
                pc.Entry(payroll).State = EntityState.Modified;
                await pc.SaveChangesAsync();
    
            }

     

     

    Thursday, February 6, 2020 10:17 PM
  • User-17257777 posted

    Hi vkagrawal,

    I tested the Attach method based on your description and reproduced your problem. I fixed it by adding the Name(Not Null) field to the user entity.

    using(DBEntities DB = new DBEntities())
    {
      User user = new User(){ Id = 1, Name = "whatever" };
      DB.Users.Attach(user);
      user.Image = "";
      DB.SaveChanges();
    }

    You can change the value of Name at will (but can not be null), and then it will track the user entity which Id is 1 and set it to Unchanged, afterword you can make some update below and finally SaveChanges.

    Best Regards,

    Jiadong Meng

    Friday, February 7, 2020 5:19 AM
  • User1501362304 posted

    Hi jiadongm,

    Thanks for the reply but sorry to say that suggestion is not feasible when we have many not nullable columns in table. My dummy [User] table was to demonstrate my situation, in reality there are many not nullable columns in table and it is not feasible for me to specify property with some not null value in object initialization.

    Sunday, February 9, 2020 5:29 PM
  • User1501362304 posted

    Hi DA924,

    Thanks for the reply and sample code, but that also does not seem working. It is really sad that must have has requirement is rolled back in EF 6, why one would hit database twice for straight forward update which can be done in single sql query. Where we can ask for update/enhancement in EF?

    Thanks,
    Vikas

    Sunday, February 9, 2020 5:42 PM
  • User1120430333 posted

    Hi DA924,

    Thanks for the reply and sample code, but that also does not seem working. It is really sad that must have has requirement is rolled back in EF 6, why one would hit database twice for straight forward update which can be done in single sql query. Where we can ask for update/enhancement in EF?

    Thanks,
    Vikas

    Yes, the example code I showed is using EF Core. Now that I recall, setting modified state for an entity object for an update will not work either in EF6,  and you have to do the double hit using two different contexts one for read and another context for update in a disconnected state too.

    You could try the below version of EF 6 to see if that works or go to EF Core. 

    https://docs.microsoft.com/en-us/ef/ef6/what-is-new/

    EG 6 example..

      public void UpdateStudent(DTOStudent dto)
            {
                var student = new Student();
    
                using (var context = new CUDataEntities())
                {
                    student = (context.Students.Where(a => a.StudentID == dto.StudentID)).SingleOrDefault();
                }
    
                if (student != null)
                {
                    student.FirstName = dto.FirstName;
                    student.LastName = dto.LastName;
                    student.EnrollmentDate = dto.EnrollmentDate;
                } 
    
                using (var dbcontext = new CUDataEntities())
                {
                    if (student != null)
                    {
                        dbcontext.Entry(student).State = EntityState.Modified;
                        dbcontext.SaveChanges();
                    }
                }
            }
        }

    Sunday, February 9, 2020 10:16 PM
  • User1501362304 posted

    Thanks for replying. I do not see any choice other than downgrading EF version or fetching first to update the entity. That is must have feature which needs to be provided back.

    Regards,
    Vikas

    Monday, February 10, 2020 7:15 PM
  • User2053451246 posted

    Not the prettiest solution but you could use ExecuteSqlCommand against your context.

    await context.Database.ExecuteSqlCommandAsync("UPDATE tablename SET columnname = 'value' where PkId = 1");

    The context will be unaware of changes done this way until you retrieve the entity again.

    Monday, February 10, 2020 7:56 PM
  • User1120430333 posted

    Your other choice is to call a parmterized stored procedure from EF. 

    Monday, February 10, 2020 9:49 PM
  • User1501362304 posted

    Yes, but I did not want to write SP for one/two fields update.

    Tuesday, February 11, 2020 6:36 PM
  • User1120430333 posted

    Yes, but I did not want to write SP for one/two fields update

    You have 6 of one, and half a dozen of the other. The choice is yours to make as to what path to take. And I have seen a SP or SP(s) that have done what you are talking about. :)

    Tuesday, February 11, 2020 8:23 PM
  • User1501362304 posted

    Yes, SPs can do the job but still that feature within EF is most wanted and much awaited. Can't keep writing SPs for hundreds of tables to update couple of fields. Also, if we ever need to change database from MS SQL to something else then would have to write those many SPs there as well, so not in favor of SPs at all :(

    Thursday, February 13, 2020 6:40 PM
  • User2053451246 posted

    Did you try my suggestion?

    Friday, February 14, 2020 9:06 PM
  • User1120430333 posted

    Yes, SPs can do the job but still that feature within EF is most wanted and much awaited.

    I don't think it's going to happen for EF6 unless the bridge EF 6, a bridge path between EF 6 and EF Core,  does it when using the EF model. EF Core does what you are wanting to do, and I have done with EF Core. Or you go back to EF 5 I think does what you are wanting to do.

    Friday, February 14, 2020 10:18 PM
  • User1501362304 posted

    Thanks DA924 for being in the conversation. Don't have expectation now for EF6, will either fetch entity first and update then or use EF5 in MVC 5 application.

    Saturday, February 15, 2020 10:17 AM