Unexpected behavior using DbContext and DbSet

Question

I have two identical sql tables: dbo.Smith_Products and temp.Smith_Products.

I have two identical classes under different namespaces: IMS.Model.dbo and IMS.Model.temp

I am trying to move data from one table to another using Mapper:

        var spc = new Smith_ProductsController();
        // get all Smith_Products records
        spc.Gets();

        // our temp.Smith_Products entity -- exact same table definition, exact same class definition. Different sql schema, different namespace
        var spctemp = new imstemp.Smith_ProductsController();

        // config and initialize our mapper
        var config = new MapperConfiguration(cfg => cfg.CreateMap<Smith_Products, imstemp.Smith_Products>());
        var mapper = config.CreateMapper();

        // copy from dbo recordset to temp recordset
        spctemp.Recordset = mapper.Map<Smith_Products[], IList<imstemp.Smith_Products>>(spc.Recordset.ToArray());

        spctemp.Repository.DataSet.Create();
        spctemp.Repository.DataSet.AddOrUpdate(spctemp.Recordset.ToArray());

        var x = spctemp.Repository.SaveChanges();
        // on first run with an empty temp.Smith_Products table, this works fine. Inserts 114 records
        // on subsequent runs, x should = 0 because we've not changed or added anything
        // however, this is not the case. Second run=108, 3rd run=34, etc. Records are being duplicated excecpt for ProductID, our key (identity,increment)

        // then we do this each time
        // retrieve the temp.Smith_Product records
        spctemp.Gets();
        // Change nothing
        spctemp.Repository.DataSet.AddOrUpdate(spctemp.Recordset.ToArray());
        // x should equal 0 each time
        x = spctemp.Repository.SaveChanges();
        //and it does.

the Repository class is simply a subclass of my controllers:

    public class RepositoryContext : DbContext
    {
        public DbSet<Smith_Products> DataSet { get; set; }

        public RepositoryContext()
        {
            Database.Connection.ConnectionString = "xxx"
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Smith_Products>().ToTable("dbo.Smith_Products");
        }
    }
    public RepositoryContext Repository=new RepositoryContext();

My question is, what is going on with the addition of duplicate records when those records should not have been inserted? Is the mapper not converting properly?

Thanks, Chris

This is a very inefficient way to copy data between two tables. Is there a reason you're not writing SQL directly? — Dai, Apr 28 '16 at 21:19
It's incredibly inefficient. Thank you. That wasn't the purpose of the exercise here. The purpose was to make sure that if I do a DbSet transaction on a table using AddorUpdate or "upsert", it wasn't going to screw up my data. I thought I found an issue. Turns out, there wasn't necessarily one. But thank you for you comments. Here's your SQL code: Creating a new table from existing data: select * into temp.smith_products from smith_products or insert into temp.smith_products select * from smith_products. Make sure identity_insert is turned on. Is that what you were looking for? — Chris, Apr 28 '16 at 21:44

score 0 · Answer 1 · answered Apr 28 '16 at 21:37

And I figured it out.

The ProductID was not necessarily matching between the two tables. When I copied the original table to the temp schema, I did not set the Identity seed to 429, the next incremented key in original table.

Figured that out and voila, everything is kosher.

Chris

Unexpected behavior using DbContext and DbSet

1 Answers1