locked
EntityFramework unable to insert data into SQLServer table RRS feed

  • Question

  • User-1980778524 posted

    Hi, As above,

    error Msg :

    System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

    I created a Web Api Project and I added Ado.net Entities DataModel base on Database First approach.

    I post the Json Data string in Fiddler to this Web Api Post.


    in SQL Server table, I have setUp:

    CustId
    Is Identity yes, Identity Increment 1  Identity Seed 1


    in the EDMX : I made following changes.

    using System;
        using System.Collections.Generic;
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;

        public partial class Customer
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int CustId { get; set; }
            public string CustomerName { get; set; }
            public string CustomerPassword { get; set; }
            public string CustomerEmailAddr { get; set; }
            public string CustomerMobileNo { get; set; }
            public Nullable<System.DateTime> CreatedOn { get; set; }
        }
    }

    ----Controller

     [HttpPost]
      public void PostContact([FromBody] Customer contact)
     {
        repository.Add(contact);
      }

    public void Add(Customer contact)
    {
       try
       {            

          var addedContact = ctx.Customers.Add(contact);
          ctx.SaveChanges();
        }
       catch (Exception ex)
        {
          string err = ex.ToString();
        }         

     }

    After making change on Class Customer in EDMX, still cannot insert.

    Thanks

    Thursday, April 27, 2017 6:43 AM

All replies

  • User753101303 posted

    Hi,

    You changed this in the EDMX designer or directly in the generated code. Could it be that you changed this Inside the code but that the code has been overwritten ?

    Thursday, April 27, 2017 8:28 AM
  • User-1980778524 posted

    Hi,

    You changed this in the EDMX designer or directly in the generated code. Could it be that you changed this Inside the code but that the code has been overwritten ?

    I added it directly to the generated Class in EDMX:

    using System;
        using System.Collections.Generic;
        using System.ComponentModel.DataAnnotations; <-- added
        using System.ComponentModel.DataAnnotations.Schema; <-- added

        public partial class Customer
        {
            [Key]  <-- added
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)] <-- added
            public int CustId { get; set; }
            public string CustomerName { get; set; }
            public string CustomerPassword { get; set; }
            public string CustomerEmailAddr { get; set; }
            public string CustomerMobileNo { get; set; }
            public Nullable<System.DateTime> CreatedOn { get; set; }
        }
    }

    What I need to do?

    Thursday, April 27, 2017 10:16 AM
  • User-1980778524 posted

    Anyone can help?  This is very important for me to learn this concept of handling Web Api POST.

    Friday, April 28, 2017 4:04 AM
  • User753101303 posted

    The usual flow is to change this rather in the EDMX file. In short whatever is shown in the EDMX designer should be done in the EDMX designer. The generated code should not be modified and I believe that not all the information is in this code (ie the EDMX file is still processed at runtime to get some more info used by EF).

    You can "extend" the code (using partial and/or buddy metadata classes to avoid having your change overwrittten) and only if this is not something already handled in the EDMX designer...

    So in short do the change directly in the EDMX file and see what happens.

    Friday, April 28, 2017 7:16 AM
  • User-707554951 posted

    Hi SkyDriving,

    This error means you are trying to assign a value explicitly to a column where the database automatically assigns it.

    SQL Server by it self will insert the value.

    If you need to insert explicit value for identity column you need to turn it on.

    You could execute the following in sql server:

    SET IDENTITY_INSERT dbo.Customers ON;
    GO

    Note: you need to update you edmx file to reflect any changes you may have made in the database.

    Warning: when IDENTITY_INSERT is set to ON, we need to manually assign the identity key (ID in most cases). You must ensure that it is unique and not null or empty

    Or something as below:

    using (TestDBEntities1 context = new TestDBEntities1())
    {
    	using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    	{
    		context.Connection.Open();
    		int i = context.ExecuteStoreCommand("SET IDENTITY_INSERT TestDB.dbo.Person ON");
    		Person p = new Person();
    		p.PersonID = 4;
    		p.Name = "Michael";
    		context.People.AddObject(p);
    		context.SaveChanges();
    		i = context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Person OFF");
    		scope.Complete();
    	}
    }

    For more information, please check the following links:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql

    https://blog.kongnir.com/2012/06/22/c-net-entity-framework-how-to-insert-record-into-database-table-with-auto-increment-primary-id/

    Best Regards

    Cathy

    Friday, April 28, 2017 7:38 AM
  • User-1980778524 posted

    Hi Cathy Zou,

    Thanks for the encouraging news on solving this problem. I follow the instruction. Bu I got this err Message when I added the code for handling the PrimaryKey as below:

    The type or namespace name 'TransactionScope' could not be found (are you missing a using directive or an assembly reference?)

    What is the namespace for it?

    or This TransactionScope is a new class that I have to create? or let the intellisense to create one?

    I have this question on inserting new record base on your code above.

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))

    {

    context.Connection.Open();

    int i = context.ExecuteStoreCommand("SET IDENTITY_INSERT TestDB.dbo.Person ON");

    Person p = new Person();

    p.PersonID = 4;     <---------- How to I know what is the current PKey Id?   I am inserting a new record into the table and SQL svr will generate a new ID and return to me.

    p.Name = "Michael";

    context.People.AddObject(p);

    context.SaveChanges();

    i = context.ExecuteStoreCommand("SET IDENTITY_INSERT dbo.Person OFF");

    scope.Complete();

    }

    Update:

    I just want you to know I am creating a Web Api project in which I added a ado.net Entities Datamodel base on Database First approach. 

    I dont seems to get context.Connection.Open() in My ctx as above. So, I think this somthing wrong.

    Thanks

    Thanks

    Friday, April 28, 2017 8:23 AM
  • User-707554951 posted

    Hi SkyDriving,

    What is the namespace for it?

    or This TransactionScope is a new class that I have to create? or let the intellisense to create one?

    using System.Transactions;

    Note: you need to add reference of System.Transactions.dll by right click Add references in your project.

    http://stackoverflow.com/questions/30248051/the-type-or-namespace-name-transactions-does-not-exist-in-the-namespace-syste

    http://stackoverflow.com/questions/11819344/visual-studio-2012-cant-find-system-transactions-assembly-in-net-4-5-framew

    Best Regards

    Cathy

    Friday, April 28, 2017 9:56 AM
  • User-1980778524 posted

    Hi Cathy

    I added the reference  system.transactions in the project.

    I could not find any of these methods available.

    1)context.Connection.Open()
    1)context.ExecuteStoreCommand()
    3)context.People.AddObject();


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Web.Http;
    using System.Transactions; <-- added

    namespace MyService.DataAccessModelRepository
    {
        public class CustomerRepository : ICustomerRepository
        {
           MyEntities ctx = new MyEntities();

     //- implement one of the ICustomerRepository interface

         public void Add(Customer contact)
            {
                try
                {
                    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                    {
        
        Problem:        
                    I could find any of these methods available.
         
       1)ctx.Connection.Open()
       1)ctx.ExecuteStoreCommand()
       3)ctx.People.AddObject();
     
                    }   

                }
                catch (Exception ex)
                {
                    string err = ex.ToString();   
                }
           }
       }

    Saturday, April 29, 2017 12:55 AM
  • User753101303 posted

    IMHO using SET IDENTTY_INSERT ON is a wrong way to try to fix this and rather than blindly doing what is suggested in the error message it should be fixed depending on your actual intent.

    Here your intent is much more likely to actually use the identity feature it to have this value being generated server side. So you hould likely use the EDMX designer to check that the needed option is properly defined on your CustId column.

    Sunday, April 30, 2017 9:33 PM