0

this is the method of the startup class to seed the data from an existing database. I don't understood why it give me an exception if i set indentity_insert ON:

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory) { loggerFactory.AddConsole(Configuration.GetSection("Logging")); loggerFactory.AddDebug();

        app.UseApplicationInsightsRequestTelemetry();


        //Seed data
        using (var context = app.ApplicationServices.GetService<ApplicationDbContext>())
        {
            if (env.IsDevelopment())
            {
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Customers ON");// i turn on
                context.SaveChanges();
                //seed code here
                string path = "C:\\Users\\Reynaldo\\Desktop\\playin\\CustomerT.csv";

                using (TextReader fileReader = System.IO.File.OpenText(path))
                {
                    Mapper.Initialize(cfg =>
                    {
                        cfg.CreateMap<CustomerViewModel, Customer>()
                         .ForMember(dto => dto.Name, conf => conf.MapFrom(ol => ol.FirstName))
                         .ForMember(dto => dto.Id, conf => conf.MapFrom(ol => ol.CustomerId))                             
                         .ForMember(dto=>dto.PlanType,conf=>conf.Ignore())
                         .ForMember(dto => dto.SalePayments, conf => conf.Ignore()); ;

                        cfg.CreateMap<string, double>().ConvertUsing(Convert.ToDouble);
                        cfg.CreateMap<string, DateTime>().ConvertUsing(new DateTimeTypeConverter());
                    });
                    var reader = new CsvReader(fileReader);
                    reader.Configuration.RegisterClassMap<CustomerViewModelMap>();
                    var allvalues = reader.GetRecords<CustomerViewModel>();
                    foreach (var item in allvalues)
                    {
                        var x = Mapper.Map<Customer>(item);
                        context.Customers.Add(x);
                        break;
                    }
                }


                context.SaveChanges();//here is when the exception throws.
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Customers OFF");
                context.SaveChanges();
            }
        }

        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
            app.UseDatabaseErrorPage();
            app.UseBrowserLink();
        }
        else
        {
            app.UseExceptionHandler("/Home/Error");
        }

        app.UseApplicationInsightsExceptionTelemetry();

        app.UseStaticFiles();

        app.UseIdentity();

        // Add external authentication middleware below. To configure them please see http://go.microsoft.com/fwlink/?LinkID=532715

        app.UseMvc(routes =>
        {
            routes.MapRoute(
                name: "default",
                template: "{controller=Home}/{action=Index}/{id?}");
        });
    }

Exception

Rey Cruz
  • 384
  • 3
  • 14
  • It may have something to do with the original SET IDENTITY_INSERT ON being lost as soon as Content.SaveChanges is called. I'm pretty sure that once the connection is dropped, SET IDENTITY_INSERT is reset (the scope is lost). See http://stackoverflow.com/questions/5791941/what-is-the-scope-of-set-identity-insert-xyz-on. I know that with LinqToSQL and datacontexts, connections are opened and closed at a rate of knots. Things like this just don't work. – Adam Sep 23 '16 at 09:35
  • Try executing your SQL in one block - one command. You'll probably have to drop the entity niceties, and execute raw SQL. – Adam Sep 23 '16 at 09:39
  • 1
    Also look here, there is an answer that mentions TransactionScope. This may help: http://stackoverflow.com/questions/1368987/linq-to-sql-and-identity-insert – Adam Sep 23 '16 at 09:39

0 Answers0