none
Problem with Entity Framework RRS feed

  • Question

  • I am creating a sample EF app.  I am using SQL Server 14 and have created three tables.  Each of the tables has as a Primary Key an identity column.  The following is the first code in the sample (I have other much more complicated apps that use EF with no problems).

            Dim esde As New EntitySampleDatabaseEntities1
    
            For i As Integer = 0 To 9
                Dim cust As New Customer
                cust.CustomerName = "Customer " & i.ToString
                esde.Customers.Add(cust)
            Next
    
            Dim cnt = CType(esde, IObjectContextAdapter).ObjectContext.ObjectStateManager.GetObjectStateEntries(System.Data.Entity.EntityState.Added).Count
    
            Dim savcnt = esde.SaveChanges()
    

    The create/drop for the Customer table is :

    USE [EntitySampleDatabase]
    GO
    
    /****** Object:  Table [dbo].[Customer]    Script Date: 2018-08-23 1:27:00 PM ******/
    DROP TABLE [dbo].[Customer]
    GO
    
    /****** Object:  Table [dbo].[Customer]    Script Date: 2018-08-23 1:27:00 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Customer](
    	[CustomerId] [int] IDENTITY(1,1) NOT NULL,
    	[CustomerName] [varchar](255) NOT NULL,
     CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
    (
    	[CustomerId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    

    When I execute the code it fails on the SaveChanges with the following inner message:

    {"Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF."}


    Lloyd Sheen

    Thursday, August 23, 2018 5:34 PM

Answers

  • Its been a while since I used the migrate, but here is what I did.

    1.  Create tables in SQL Server table

    2.  In VS (2915) I add ADO Entity Data Model

    3.  Use EF designer from Database

    4.  Select table(s) to use and generate

    Now if I just drop one table it works (now).  And now if I drag all three tables it works.  

    Sorry really do not know what happened on first attempt but all seems good now.

    Thanks.


    Lloyd Sheen

    • Marked as answer by sqlguy Thursday, August 23, 2018 6:55 PM
    Thursday, August 23, 2018 6:54 PM

All replies

  • Greetings,

    Have you setup auto migrations?



    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 23, 2018 6:20 PM
    Moderator
  • Since I don't know what they are I am going to say no I haven't and I am using DB first.

    Lloyd Sheen


    • Edited by sqlguy Thursday, August 23, 2018 6:21 PM
    Thursday, August 23, 2018 6:21 PM
  • Since I don't know what they are I am going to say no I haven't and I am using DB first.

    Lloyd Sheen


    The short answer is without migrations and the database has changes EF is unaware of the changes. Personally I do my migration setup in a .tt file (T4 template).

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 23, 2018 6:38 PM
    Moderator
  • Here is a visual via PackageManager console.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 23, 2018 6:54 PM
    Moderator
  • Its been a while since I used the migrate, but here is what I did.

    1.  Create tables in SQL Server table

    2.  In VS (2915) I add ADO Entity Data Model

    3.  Use EF designer from Database

    4.  Select table(s) to use and generate

    Now if I just drop one table it works (now).  And now if I drag all three tables it works.  

    Sorry really do not know what happened on first attempt but all seems good now.

    Thanks.


    Lloyd Sheen

    • Marked as answer by sqlguy Thursday, August 23, 2018 6:55 PM
    Thursday, August 23, 2018 6:54 PM
  • Not sure why you are dropping the table?

    If I start out with

    On the designer canvas selec update model from database

    Here I set the primary key to id and EF took care of it.

    Add a new column in SQL-Server 

    Never dropped the table :-) That is how it should be done.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 23, 2018 7:12 PM
    Moderator
  • The script I showed was just a script generated by SQL Management Studio. The drop really wasn't part of the problem.

    Lloyd Sheen

    Thursday, August 23, 2018 7:18 PM
  • The script I showed was just a script generated by SQL Management Studio. The drop really wasn't part of the problem.

    Lloyd Sheen


    You should not have had to drop any table if EF Database first was being used.
    Friday, August 24, 2018 4:34 AM