How to update record using Entity Framework 6

Question

I am trying to update record using EF6  First finding the record  if exists  update it   Here is my code -  var book   new Model Book       BookNumber     book BookNumber      BookName    book BookName      BookTitle    book BookTitle     using  var db   new MyContextDB          var result   db Books SingleOrDefault b   gt  b BookNumber    bookNumber       if  result    null                try                       db Books Attach book               db Entry book  State   EntityState Modified              db SaveChanges                      catch  Exception ex                        throw                      Everytime I try to update record using above code  I am getting this error -      System Data Entity Infrastructure DbUpdateConcurrencyException  Store   update  insert  or delete statement affected an unexpected number of   rows  0   Entities may have been modified or deleted since entities   were loaded  Refresh ObjectStateManager entrie

User · Answer

You re trying to update the record  which to me means  change a value on an existing record and save it back    So you need to retrieve the object  make a change  and save it   using  var db   new MyContextDB          var result   db Books SingleOrDefault b   gt  b BookNumber    bookNumber       if  result    null                result SomeValue    Some new value           db SaveChanges

User · Answer

Not related to this specific example  but I came across a challenge when trying to use EF and a DateTime field as the concurrency check field  It appears the EF concurrency code doesn t honor the precision setting from the metadata  edmx  i e  Type  quot DateTime quot  Precision  quot 3 quot   The database datetime field will store a millisecond component within the field  i e  2020-10-18 15 49 02 123   Even if you set the original value of the Entity to a DateTime that includes the millisecond component  the SQL EF generates is this  UPDATE  dbo   People  SET  dateUpdated     0 WHERE    PeopleID     1  AND   dateUpdated     2   --  0   10 19 2020 1 07 00 AM   Type   DateTime2  --  1   3182   Type   Int32  --  2   10 19 2020 1 06 10 AM   Type   DateTime2   As you can see   2 is a STRING representation without a millisecond component  This will cause your updates to fail  Therefore  if you re going to use a DateTime field as a concurrency key  you must STRIP off the milliseconds Ticks from the database field when retrieving the record and only pass update the field with a similar stripped DateTime        strip milliseconds due to EF concurrency handling     PeopleModel p   db people Where x   gt  x PeopleID   id       if  p dateUpdated Millisecond  gt  0                DateTime d   new DateTime p dateUpdated Ticks   10000000   10000000           object   b    p PeopleID  d           int upd   db Database ExecuteSqlCommand  quot Update People set dateUpdated  p1 where peopleId  p0 quot   b           if  upd    1              p dateUpdated   d          else             return InternalServerError new Exception  quot Unable to update dateUpdated quot           return Ok p    And when updating the field with a new value  strip the milliseconds also  param int id  PeopleModel person  People tbl   db People Where x   gt  x PeopleID    id  FirstOrDefault    db Entry tbl  OriginalValues  quot dateUpdated quot     person dateUpdated    strip milliseconds from dateUpdated since EF doesn t preserve them tbl dateUpdated   new DateTime DateTime Now Ticks   10000000   10000000

User · Answer

So you have an entity that is updated  and you want to update it in the database with the least amount of code     Concurrency is always tricky  but I am assuming that you just want your updates to win  Here is how I did it for my same case and modified the names to mimic your classes  In other words  just change attach to add  and it works for me    public static void SaveBook Model Book myBook        using  var ctx   new BookDBContext                  ctx Books Add myBook           ctx Entry myBook  State   System Data Entity EntityState Modified          ctx SaveChanges

User · Answer

Like Renat said  remove  db Books Attach book    Also  change your result query to use  AsNoTracking   because this query is throwing off entity framework s model state  It thinks  result  is the book to track now and you don t want that   var result   db Books AsNoTracking   SingleOrDefault b   gt  b BookNumber    bookNumber

User · Answer

Try It      UpdateModel book    var book   new Model Book       BookNumber     book BookNumber      BookName    book BookName      BookTitle    book BookTitle     using  var db   new MyContextDB          var result   db Books SingleOrDefault b   gt  b BookNumber    bookNumber       if  result    null                try                       UpdateModel book               db Books Attach book               db Entry book  State   EntityState Modified              db SaveChanges                      catch  Exception ex                        throw

User · Answer

This if for Entity Framework 6 2 0   If you have a specific DbSet and an item that needs to be either updated or created   var name   getNameFromService     var current    dbContext Names Find name BusinessSystemId  name NameNo   if  current    null         dbContext Names Add name     else        dbContext Entry current  CurrentValues SetValues name      dbContext SaveChanges      However this can also be used for a generic DbSet with a single primary key or a composite primary key   var allNames   NameApiService GetAllNames    GenericAddOrUpdate allNames   BusinessSystemId    NameNo     public virtual void GenericAddOrUpdate lt T gt  IEnumerable lt T gt  values  params string   keyValues  where T   class       foreach  var value in values                try                       var keyList   new List lt object gt                    Get key values from T entity based on keyValues property             foreach  var keyValue in keyValues                                var propertyInfo   value GetType   GetProperty keyValue                   var propertyValue   propertyInfo GetValue value                   keyList Add propertyValue                              GenericAddOrUpdateDbSet keyList  value                 Only use this when debugging to catch save exceptions                dbContext SaveChanges                      catch                       throw                       dbContext SaveChanges       public virtual void GenericAddOrUpdateDbSet lt T gt  List lt object gt  keyList  T value  where T   class         Get a DbSet of T type     var someDbSet   Set typeof T           Check if any value exists with the key values     var current   someDbSet Find keyList ToArray         if  current    null                someDbSet Add value             else               Entry current  CurrentValues SetValues value

User · Answer

I know it has been answered good few times already  but I like below way of doing this  I hope it will help someone     attach object  search for row  TableName tn    context TableNames Attach new TableName   PK COLUMN   YOUR VALUE       set new value tn COLUMN NAME TO UPDATE   NEW COLUMN VALUE     set column as modified  context Entry lt TableName gt  tn  Property tnp   gt  tnp COLUMN NAME TO UPDATE  IsModified   true     save change  context SaveChanges

User · Answer

You should use the Entry    method in case you want to update all the fields in your object   Also keep in mind you cannot change the field id  key   therefore first set the Id to the same as you edit    using var context   new              var EditedObj   context          Obj          Where x   gt  x                 First         NewObj Id   EditedObj Id    This is important when we first create an object  NewObj   in which the default Id   0  We can not change an existing key       context Entry EditedObj  CurrentValues SetValues NewObj        context SaveChanges

User · Answer

using var myDb   new MyDbEntities           user user   new user        user username    me       user email    me me com        myDb Users Add user       myDb users Attach user       myDb Entry user  State   EntityState Modified   this is for modiying update existing entry     myDb SaveChanges

User · Answer

Here is best solution for this issue  In View add all the ID  Keys   Consider having multiple tables named  First  Second and Third    Html HiddenFor model  gt model FirstID   Html HiddenFor model  gt model SecondID   Html HiddenFor model  gt model Second SecondID   Html HiddenFor model  gt model Second ThirdID   Html HiddenFor model  gt model Second Third ThirdID    In C  code    HttpPost  public ActionResult Edit First first      if  ModelState Isvalid          if  first FirstID  gt  0              datacontext Entry first  State   EntityState Modified        datacontext Entry first Second  State   EntityState Modified        datacontext Entry first Second Third  State   EntityState Modified            else             datacontext First Add first             datacontext SaveChanges        Return RedirectToAction  Index          return View first

User · Answer

I found a way that works just fine    var Update   context UpdateTables Find id           Update Title   title              Mark as Changed         context Entry Update  State   System Data Entity EntityState Modified          context SaveChanges

User · Answer

This code is the result of a test to update only a set of columns without making a query to return the record first  It uses Entity Framework 7 code first      This function receives an object type that can be a view model or an anonymous     object with the properties you want to change      This is part of a repository for a Contacts object   public int Update object entity        var entityProperties    entity GetType   GetProperties           Contacts con   ToType entity  typeof Contacts   as Contacts       if  con    null                 context Entry con  State   EntityState Modified           context Contacts Attach con            foreach  var ep in entityProperties                           If the property is named Id  don t add it in the update                  It can be refactored to look in the annotations for a key                 or any part named Id               if ep Name     Id                    context Entry con  Property ep Name  IsModified   true                       return  context SaveChanges       public static object ToType lt T gt  object obj  T type           Create an instance of T type object     object tmp   Activator CreateInstance Type GetType type ToString              Loop through the properties of the object you want to convert     foreach  PropertyInfo pi in obj GetType   GetProperties                  try                          Get the value of the property and try to assign it to the property of T type object             tmp GetType   GetProperty pi Name  SetValue tmp  pi GetValue obj  null   null                     catch  Exception ex                           Logging Log Error ex                          Return the T type object               return tmp      Here is the complete code   public interface IContactRepository       IEnumerable lt Contacts gt  GetAllContats        IEnumerable lt Contacts gt  GetAllContactsWithAddress        int Update object c      public class ContactRepository   IContactRepository       private ContactContext  context       public ContactRepository ContactContext context                 context   context             public IEnumerable lt Contacts gt  GetAllContats                 return  context Contacts OrderBy c   gt  c FirstName  ToList               public IEnumerable lt Contacts gt  GetAllContactsWithAddress                 return  context Contacts              Include c   gt  c Address               OrderBy c   gt  c FirstName  ToList                    TODO Change properties to lambda expression     public int Update object entity                var entityProperties   entity GetType   GetProperties             Contacts con   ToType entity  typeof Contacts   as Contacts           if  con    null                         context Entry con  State   EntityState Modified               context Contacts Attach con                foreach  var ep in entityProperties                                if ep Name     Id                        context Entry con  Property ep Name  IsModified   true                                   return  context SaveChanges               public static object ToType lt T gt  object obj  T type                   Create an instance of T type object         object tmp   Activator CreateInstance Type GetType type ToString                  Loop through the properties of the object you want to convert         foreach  PropertyInfo pi in obj GetType   GetProperties                          try                                  Get the value of the property and try to assign it to the property of T type object                 tmp GetType   GetProperty pi Name  SetValue tmp  pi GetValue obj  null   null                             catch  Exception ex                                   Logging Log Error ex                                      Return the T type object         return tmp               public class Contacts       public int Id   get  set        public string FirstName   get  set        public string LastName   get  set        public string Email   get  set        public string Company   get  set        public string Title   get  set        public Addresses Address   get  set           public class Addresses        Key      public int Id   get  set        public string AddressType   get  set        public string StreetAddress   get  set        public string City   get  set        public State State   get  set        public string PostalCode   get  set         public class ContactContext   DbContext       public DbSet lt Addresses gt  Address   get  set         public DbSet lt Contacts gt  Contacts   get  set         public DbSet lt State gt  States   get  set         protected override void OnConfiguring DbContextOptionsBuilder optionsBuilder                var connString    Server YourServer Database ContactsDb Trusted Connection True MultipleActiveResultSets true            optionsBuilder UseSqlServer connString           base OnConfiguring optionsBuilder

User · Answer

Attaching an entity will set its tracking state to Unchanged   To update an existing entity  all you need to do is set the tracking state to Modified   According to the EF6 docs      If you have an entity that you know already exists in the database but to which changes may have been made then you can tell the context to attach the entity and set its state to Modified  For example   var existingBlog   new Blog   BlogId   1  Name    ADO NET Blog      using  var context   new BloggingContext          context Entry existingBlog  State   EntityState Modified          Do some more work           context SaveChanges

User · Answer

I have the same problem when trying to update record using Attach   and then SaveChanges   combination  but I am using SQLite DB and its EF provider  the same code works in SQLServer DB without problem   I found out  when your DB column has GUID  or UniqueIdentity  in SQLite and your model is nvarchar  SQLIte EF treats it as Binary i e   byte    by default  So when SQLite EF provider tries to convert GUID into the model  string in my case  it will fail as it will convert to byte    The fix is to tell the SQLite EF to treat GUID as TEXT  and therefore conversion is into strings  not byte    by defining  quot BinaryGUID false  quot  in the connectionstring  or metadata  if you re using database first  like so     lt connectionStrings gt       lt add name  quot Entities quot  connectionString  quot metadata res   savetyping    System Data SQLite EF6 provider connection string  amp quot data source C      db sqlite3 Version 3 BinaryGUID false App EntityFramework amp quot  quot  providerName  quot System Data EntityClient quot    gt     lt  connectionStrings gt   Link to the solution that worked for me  How does the SQLite Entity Framework 6 provider handle Guids

User · Answer

You can use the AddOrUpdate method   db Books AddOrUpdate book     requires using System Data Entity Migrations  db SaveChanges

User · Answer

Here s my post-RIA entity-update method  for the Ef6 time frame    public static void UpdateSegment ISegment data        if  data    null  throw new ArgumentNullException  The expected Segment data is not here          var context   GetContext         var originalData   context Segments SingleOrDefault i   gt  i SegmentId    data SegmentId       if  originalData    null  throw new NullReferenceException  The expected original Segment data is not here          FrameworkTypeUtility SetProperties data  originalData        context SaveChanges        Note that FrameworkTypeUtility SetProperties   is a tiny utility function I wrote long before AutoMapper on NuGet   public static void SetProperties lt TIn  TOut gt  TIn input  TOut output  ICollection lt string gt  includedProperties      where TIn   class     where TOut   class       if   input    null      output    null   return      Type inType   input GetType        Type outType   output GetType        foreach  PropertyInfo info in inType GetProperties                  PropertyInfo outfo     info    null   amp  amp  info CanRead                outType GetProperty info Name  info PropertyType                null          if  outfo    null  amp  amp  outfo CanWrite              amp  amp   outfo PropertyType Equals info PropertyType                          if   includedProperties    null   amp  amp  includedProperties Contains info Name                   outfo SetValue output  info GetValue input  null   null               else if  includedProperties    null                  outfo SetValue output  info GetValue input  null   null

User · Answer

For  net core  context Customer Add customer   context Entry customer  State   Microsoft EntityFrameworkCore EntityState Modified  context SaveChanges

User · Answer

You should remove db Books Attach book

User · Answer

I have been reviewing the source code of Entity Framework and found a way to actually update an entity if you know the Key property   public void Update lt T gt  T item  where T  Entity          assume Entity base class have an Id property for all items     var entity    collection Find item Id       if  entity    null                return              context Entry entity  CurrentValues SetValues item       Otherwise  check the AddOrUpdate implementation for ideas   Hope this help

[c#] How to update record using Entity Framework 6?

Examples related to c#

Examples related to entity-framework

Examples related to entity-framework-6

Examples related to ef-database-first