none
MVC Entity Framework Concurrency Exception on Insert RRS feed

  • Question

  • Hi MSDN, 

    I'm having an issue with Entity Framework and SQL Server. When I try to add a record in to a table, I get a "System.Data.Entity.Infrastructure.DbUpdateConcurrencyException" error. This is in a development environment so no changes should've been made. I can successfully retrieve data from the table and also an INSERT Sql statement using the context, but no 'Add'. The data is being parsed and stored correctly from the front end to the object. 

    My current controller code: 

            [HttpPost]
            public ActionResult AddFeedback()
            {
                using (var context = new RiskAssessmentContext.FeedbackContext())
                {
                    Request.InputStream.Position = 0;
                    var input = new StreamReader(Request.InputStream).ReadToEnd();
                    RiskAssessmentContext.RiskFeedback feedbackObject = JsonConvert.DeserializeObject<RiskAssessmentContext.RiskFeedback>(input);
                    feedbackObject.RiskAssessmentFeedbackID = Crypto.GenerateRandomIntID();
                    context.Feedbacks.Add(feedbackObject);
                    context.SaveChanges();
                    //This SQL statement successfuly inserts in to table
                    //context.Database.ExecuteSqlCommand("INSERT INTO dbo.tblWRAPRiskAssessmentFeedback VALUES('1231241', '320', '120', 'Test', 10/10/10, 10/10/10, '', 10/10/10, DEFAULT)");
                    return Json("Success");
                }
            }

    And my Model + context : 

            [Table("tblWRAPRiskAssessmentFeedback")]
            public class RiskFeedback
            {
                [Key]
                public int RiskAssessmentFeedbackID { get; set; }
                public int VesselNumber { get; set; }
                public int RiskAssessmentID { get; set; }
                public string Feedback { get; set; }
                public DateTime? ReadByOffice { get; set; }
                public DateTime? ReadByVessel { get; set; }
                public string CreatedBy { get; set; }
                public DateTime? Created { get; set; }
                [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
                public byte[] Upsize_ts { get; set; }
    
            }
    
            public class FeedbackContext : DbContext
            {
                public FeedbackContext(): base("DatabaseConnection")
                {
                    Database.Log = s => Debug.WriteLine(s);
                }
                public DbSet<RiskFeedback> Feedbacks { get; set; }
            }

    And the current setup of the SQL Table

     RiskAssessmentFeedbackID - int - Not Null

    VesselNumber - int - Not Null

    RiskAssessmentID - int - Nullable

    Feedback - ntext - Nullable

    ReadByOffice - datetime2(7) - Nullable

    ReadByVessel- datetime2(7) - Nullable

    CreatedBy - nvarchar(30) - Nullable

    Created - datetime2(7) - Nullable

    upsize_ts - timestamp - Nullable

    (This is my first post to unable to include screenshot :c   ) 

    Please let me know if there's any more detail that could be useful!

    Any ideas or advice would be massively appreciated, thanks in advance!

    Jamie

    Thursday, August 22, 2019 10:25 AM

Answers

  • Thanks you both massively for your help. Although the solution was unrelated, you've helped me understand entity framework a lot better!
    It ended up being a problem with upsize_ts. SQL server rejected parsing it even when the field was set to computed in the model. Simply removing it from the model (and a stray constraint in SQL Server) seemed to fix it for me. 

    Thanks again!

    • Marked as answer by JaamieT Thursday, August 22, 2019 5:41 PM
    • Unmarked as answer by JaamieT Thursday, August 22, 2019 5:41 PM
    • Marked as answer by JaamieT Friday, August 23, 2019 8:13 AM
    Thursday, August 22, 2019 5:40 PM

All replies

  • Hello,

    Please review the following Microsoft documentation on this topic and this page. Between the two they should provide what you need to resolve this issue.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 22, 2019 10:42 AM
    Moderator
  • Hi Karen,

    Apologies, I neglected to mention I've looked extensively at those articles before but to no avail. They talk about update and delete but I am receiving the error from a Create.

    After further testing, update and delete run fine with no concurrency errors, it appears that it's just Insert :(


    • Edited by JaamieT Thursday, August 22, 2019 11:48 AM More info
    Thursday, August 22, 2019 11:01 AM
  • The EF error message  is about you not setting the state of the EF entity for add, update or delete. Therefore EF is going to throw the exception telling you the the persistence of the object was not successful, because the state of the entity was not set.

    Web solutions are stateless and EF is always in a disconnected state.

    EF issues can be discussed at the MSDN EF .

    https://www.tutorialspoint.com/entity_framework/entity_framework_disconnected_entities#

    Thursday, August 22, 2019 12:57 PM
  • Thank you for your explanation and the article, I feel I understand the situation a bit more now. Adapting some of the code in that article alongside similar ones, I can get it to run with no exceptions, however the data doesn't actually appear in the Database! : 

                            context.Feedbacks.Add(feedbackObject);
                            context.Entry<RiskAssessmentContext.RiskFeedback>(feedbackObject).State = EntityState.Added;
                            context.Feedbacks.Attach(feedbackObject);
                            context.SaveChanges();

     Do you have any ideas why this may be? I think I may be getting mixed up between DbSet and DbContext? And apologies for posting in the wrong forum, I'll ensure to keep that in mind for next time.

    Thanks!


    • Edited by JaamieT Thursday, August 22, 2019 2:21 PM More info
    Thursday, August 22, 2019 2:21 PM
  • Thank you for your explanation and the article, I feel I understand the situation a bit more now. Adapting some of the code in that article alongside similar ones, I can get it to run with no exceptions, however the data doesn't actually appear in the Database! : 

                            context.Feedbacks.Add(feedbackObject);
                            context.Entry<RiskAssessmentContext.RiskFeedback>(feedbackObject).State = EntityState.Added;
                            context.Feedbacks.Attach(feedbackObject);
                            context.SaveChanges();

     Do you have any ideas why this may be? I think I may be getting mixed up between DbSet and DbContext? And apologies for posting in the wrong forum, I'll ensure to keep that in mind for next time.

    Thanks!


    What is the return value for SaveChanges? Is this the standard SaveChanges or has it been overridden?

    Here are the basic patterns for inserting ready for SaveChanges

    /// <summary>
    /// Create a new Product and add the new Product
    /// to the DbContext along with indicating this
    /// item is a new entity via the entry state
    /// </summary>
    public void AddProduct1() 
    {
    	var product = new Product()
    	{
    		ProductName = "Headphones",
    		CategoryID = 1,
    		UnitPrice = 17.99M
    	};
    
    	using (var context = new NorthWindContext())
    	{
    		context.Entry(product).State = EntityState.Added;
    	}
    }
    /// <summary>
    /// Create a new Product and add the new Product
    /// to the DbContext along with indicating this
    /// item is a new entity using Attach method
    /// </summary>
    public void AddProduct2()
    {
    	var product = new Product()
    	{
    		ProductName = "Headphones",
    		CategoryID = 1,
    		UnitPrice = 17.99M
    	};
    
    	using (var context = new NorthWindContext())
    	{
    		context.Products.Attach(product);
    	}
    }
    /// <summary>
    /// Create a new Product and add the new Product
    /// to the DbContext along with indicating this
    /// item is a new entity via the Add method
    /// </summary>
    public void AddProduct3()
    {
    	var product = new Product()
    	{
    		ProductName = "Headphones",
    		CategoryID = 1,
    		UnitPrice = 17.99M
    	};
    
    	using (var context = new NorthWindContext())
    	{
    		context.Products.Add(product);
    	}
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 22, 2019 2:47 PM
    Moderator
  • The return values for cases 1 & 3 are both DbUpdateConcurrencyException. For 2, the return value is 0 and it passes through without error, but the State is Unmodified and nothing gets added to the Table.


      I've made some changes to the model, but they don't appear to have any effect : 
            [Table("tblWRAPRiskAssessmentFeedback")]
            public class RiskFeedback
            {
                [Key, Column(Order=0)]
                public int RiskAssessmentFeedbackID { get; set; }
                [Key, Column(Order =1)]
                public int VesselNumber { get; set; }
                public int RiskAssessmentID { get; set; }
                public string Feedback { get; set; }
                public DateTime? ReadByOffice { get; set; }
                public DateTime? ReadByVessel { get; set; }
                public string CreatedBy { get; set; }
                public DateTime? Created { get; set; }
                [Timestamp]
                [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
                public byte[] Upsize_ts { get; set; }
    
            }
    Thanks again for your time!

    • Edited by JaamieT Thursday, August 22, 2019 3:38 PM More info
    Thursday, August 22, 2019 3:04 PM
  • Hello,

    Had a few minutes to scaffold up a new table as you have, ran this and zero issues, one record inserted. Maybe the issue is with a child table.

    using System;
    using Data.Models.Models;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    
    namespace UnitTestProject1
    {
        [TestClass]
        public class UnitTest1
        {
            [TestMethod]
            public void TestMethod1()
            {
                var feedback = new RiskFeedback()
                {
                    VesselNumber = 1,
                    Created = DateTime.Now,
                    ReadByOffice = DateTime.Now,
                    CreatedBy = "KP",
                    Feedback = "Test",
                    ReadByVessel = DateTime.Now,
                    RiskAssessmentId = 2,
                    UpsizeTs = new byte[2]
                };
    
                using (var context = new FeedbackContext())
                {
                    context.Entry(feedback).State = EntityState.Added;
                    Assert.IsTrue(context.SaveChanges() == 1);
    
                }
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 22, 2019 4:40 PM
    Moderator
  • My suggestion to you is to post to the MSDN EF forum, the Stackoverflow EF forum, ASP.NET forums that has an MVC  and EF forums for help.

    I will be postings from my phone with no access to my computers that I have code to help you, which I won't be able to do for a while

    https://forums.asp.net/


    • Edited by DA924x Thursday, August 22, 2019 4:48 PM
    Thursday, August 22, 2019 4:44 PM
  • Thanks you both massively for your help. Although the solution was unrelated, you've helped me understand entity framework a lot better!
    It ended up being a problem with upsize_ts. SQL server rejected parsing it even when the field was set to computed in the model. Simply removing it from the model (and a stray constraint in SQL Server) seemed to fix it for me. 

    Thanks again!

    • Marked as answer by JaamieT Thursday, August 22, 2019 5:41 PM
    • Unmarked as answer by JaamieT Thursday, August 22, 2019 5:41 PM
    • Marked as answer by JaamieT Friday, August 23, 2019 8:13 AM
    Thursday, August 22, 2019 5:40 PM
  • Hi JaamieT,

    It seems that your problem has been solved. If so, please post "Mark as answer" to the appropriate or helpful answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Thank you for your understanding.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 23, 2019 1:45 AM
  • Hi Xingyu, 

    That's done! Should I edit the post title to be more relevant to what the actual problem was?

    Thanks!

    Friday, August 23, 2019 8:15 AM