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();
}
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.
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.
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();
}
If you find the post has answered your issue, then please mark post as 'answered'.
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
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.
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 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.
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.
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.
Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
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. :)
If you find the post has answered your issue, then please mark post as 'answered'.
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 :(
Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
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.
If you find the post has answered your issue, then please mark post as 'answered'.
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.
Member
1 Points
22 Posts
How to update entity without fetching it first
Feb 06, 2020 05:33 PM|vkagrawal|LINK
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
Contributor
4623 Points
3903 Posts
Re: How to update entity without fetching it first
Feb 06, 2020 10:17 PM|DA924|LINK
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(); }Participant
1220 Points
463 Posts
Re: How to update entity without fetching it first
Feb 07, 2020 05:19 AM|jiadongm|LINK
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Member
1 Points
22 Posts
Re: How to update entity without fetching it first
Feb 09, 2020 05:29 PM|vkagrawal|LINK
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.
Member
1 Points
22 Posts
Re: How to update entity without fetching it first
Feb 09, 2020 05:42 PM|vkagrawal|LINK
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
Contributor
4623 Points
3903 Posts
Re: How to update entity without fetching it first
Feb 09, 2020 10:16 PM|DA924|LINK
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(); } } } }Member
1 Points
22 Posts
Re: How to update entity without fetching it first
Feb 10, 2020 07:15 PM|vkagrawal|LINK
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
Contributor
7048 Points
2185 Posts
Re: How to update entity without fetching it first
Feb 10, 2020 07:56 PM|ryanbesko|LINK
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.
Contributor
4623 Points
3903 Posts
Re: How to update entity without fetching it first
Feb 10, 2020 09:49 PM|DA924|LINK
Your other choice is to call a parmterized stored procedure from EF.
Member
1 Points
22 Posts
Re: How to update entity without fetching it first
Feb 11, 2020 06:36 PM|vkagrawal|LINK
Yes, but I did not want to write SP for one/two fields update.
Contributor
4623 Points
3903 Posts
Re: How to update entity without fetching it first
Feb 11, 2020 08:23 PM|DA924|LINK
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. :)
Member
1 Points
22 Posts
Re: How to update entity without fetching it first
Feb 13, 2020 06:40 PM|vkagrawal|LINK
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 :(
Contributor
7048 Points
2185 Posts
Re: How to update entity without fetching it first
Feb 14, 2020 09:06 PM|ryanbesko|LINK
Did you try my suggestion?
Contributor
4623 Points
3903 Posts
Re: How to update entity without fetching it first
Feb 14, 2020 10:18 PM|DA924|LINK
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.
Member
1 Points
22 Posts
Re: How to update entity without fetching it first
Feb 15, 2020 10:17 AM|vkagrawal|LINK
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.