Database Already Created, new tables not adding in

Question

I am writing working with a Database that was precreated, and that I have created the DBSet definitions for the tables. The current database was inherited from a previous system and contains over 100,000 rows of data per table (transactions for over 5 years). We are putting this into a new application, porting over the data.

So, the database was manually created, not code first. The new classes are code first, and we are hoping to have those tables automatically generate into the table.

I have checked, and the user has db_owner access to the database that we are using for the database connection. The database is also not in ReadOnly.

The following is the Context class that was created:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using BHI.WCMS.DAL;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations;


namespace BHI.WCMS.DAL.Partner
{
    public class PartnerContext : DbContext
    {
        public PartnerContext()
            : base("partner")
        {
            //Configuration.LazyLoadingEnabled = false;
            Database.SetInitializer<PartnerContext>(new MigrateDatabaseToLatestVersion<PartnerContext, PartnerConfiguration>());
        }

        public PartnerContext(string ConnectionStringName)
            : base(ConnectionStringName)
        {
        }

        //protected override void OnModelCreating(DbModelBuilder modelBuilder)
        //{
        //    modelBuilder.Entity<PartnerProfileServiceCenterTransaction>().Property(p => p.AmountDue).HasPrecision(8, 2);
        //    base.OnModelCreating(modelBuilder);
        //}

        public DbSet<WarrantyForms> WarrantyForms { get; set; }
        public DbSet<WarrantyFormLines> WarrantyFormLines { get; set; }
        public DbSet<WarrantyFormLineParts> WarrantyFormLineParts { get; set; }
        public DbSet<WarrantyFormLineReplacements> WarrantyFormLineReplacements { get; set; }
        public DbSet<PartnerProfile> PartnerProfiles { get; set; }
        public DbSet<PartnerProfileContactInfo> PartnerProfileContactInfos { get; set; }
        public DbSet<PartnerProfileCustomerAddress> PartnerProfileCustomerAddresses { get; set; }
        public DbSet<PartnerProfileServiceCenterTransaction> PartnerProfileServiceCenterTransactions { get; set; }

        public static PartnerProfile InsertPartnerProfile(PartnerProfile profile)
        {
            using (var db = new PartnerContext())
            {
                foreach (var addy in profile.BillingAddress)
                {
                    addy.DateCreated = DateTime.Now;
                    addy.DateModified = DateTime.Now;
                    db.PartnerProfileCustomerAddresses.Add(addy);
                    db.SaveChanges();
                }

                //profile.DateCreated = DateTime.Now;
                //profile.DateModified = DateTime.Now;
                //db.PartnerProfiles.Add(profile);
                //db.SaveChanges();
            }
            return profile;
        }   // end of  public static PartnerProfile InsertPartnerProfile(PartnerProfile profile)

        public static PartnerProfile UpdatePartnerProfile(PartnerProfile profile)
        {
            using (var db = new PartnerContext())
            {

            }
            return profile;
        }   // end of  public static PartnerProfile UpdatePartnerProfile(PartnerProfile profile)

        public static PartnerProfile SelectPartnerProfile(PartnerProfile profile)
        {
            using (var db = new BHI.WCMS.DAL.Partner.PartnerContext())
            {

            }
            return profile;
        }   // end of  public static PartnerProfile SelectPartnerProfile(PartnerProfile profile)

    }       // end of class WarrantyContext : DbContext

    internal sealed class PartnerConfiguration : DbMigrationsConfiguration<PartnerContext>
    {
        public PartnerConfiguration()
        {
            AutomaticMigrationsEnabled = true;
        }
    }
}           // end of namespace BHI.WCMS.DAL.Partner

I have looked at adding the configuration per other articles

and that did not work. I also tried running this Enable-Migrations -ContextTypeName BHI.WCMS.DAL.Partner.PartnerContext -EnableAutomaticMigrations

And that did not work either. When I change the Context from Partner to Logging (a database that was 100% code first) it loads the tables in without a problem. So, this leads me to make the following assumptions:

There is not an issue with the class files defining the tables
There is not an issue with the DBContext file that is referencing the files
Because I am able to retrieve data from the database for the old tables, there is not an issue with the username/password for the database
Because the user account has db_owner access, there should not be any permissions issues with the database.

All that being said, I am looking at this, and not able to get the tables to create. So, there appears to be something wrong in either the tables that are currently there, or there is an issue with the database itself (there is no __MigrationHistory table).

The error message I get is this:

    Exception: System.Data.Entity.Infrastructure.DbUpdateException
    Message: An error occurred while updating the entries. See the inner exception for details.
    Source: EntityFramework
       at System.Data.Entity.Internal.InternalContext.SaveChanges()
       at BHI.WCMS.DAL.Partner.WarrantyContext.InsertPartnerProfile(PartnerProfile profile) in c:\inetpub\Bissell SiteCore\Dev\Partners-Dev\BHI.WCMS\BHI.WCMS.DAL\Partner\WarrantyContext.cs:line 47
       at BHI.WCMS.ECommerce.BWS.Partner.MapServiceToObject(ServiceCenterResponse response) in c:\inetpub\Bissell SiteCore\Dev\Partners-Dev\BHI.WCMS\BHI.WCMS.ECommerce.BWS\Partner.cs:line 151
       at BHI.WCMS.ECommerce.BWS.Partner.BWSGetServiceCenterInfo(String accountNumber, String zipCode) in c:\inetpub\Bissell SiteCore\Dev\Partners-Dev\BHI.WCMS\BHI.WCMS.ECommerce.BWS\Partner.cs:line 48

    Nested Exception

    Exception: System.Data.Entity.Core.UpdateException
    Message: An error occurred while updating the entries. See the inner exception for details.
    Source: EntityFramework
       at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
       at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
       at System.Data.Entity.Internal.InternalContext.SaveChanges()

    Nested Exception

    Exception: System.Data.SqlClient.SqlException
    Message: Invalid object name 'dbo.PartnerProfileCustomerAddresses'.
    Source: .Net SqlClient Data Provider
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at Glimpse.Ado.AlternateType.GlimpseDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
       at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
       at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
       at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()

Any help on this would be greatly appreciated.

Thanks Robert

score 1 · Answer 1 · answered Sep 14 '16 at 20:40

Ok, in working with one of the guys here, we were able to figure out what is happening.

This is possible to create a database and then allow for it to create more tables into that manually created database (that is what we were doing on the Logging table).
The issue was related not to the Context being wrong, but my poor attention to detail.

On of my entity classes was coded incorrectly (and I hadn't messed with it in a year to notice)

Incorrect Version

public partial class WarrantyFormLineReplacements
{
    #region Class Variables
    public int in_WFLineReplacementID;
    public int in_WFlineId;
    public int in_WFId;
    [MaxLength(120)]
    public string vc_ModelNumber;
    public decimal dc_ReplacementCost;
    [MaxLength(255)]
    public string vc_InsertBy;
    public DateTime dt_InsertDate;
    [MaxLength(255)]
    public string vc_UpdateBy;
    public DateTime dt_UpdateDate;
    #endregion
}       // end of public partial class WarrantyFormLineReplacements

Corrected Version

public partial class WarrantyFormLineReplacements
{
    #region Class Variables
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int in_WFLineReplacementID {get;set;}
    public int in_WFlineId { get; set; }
    public int in_WFId { get; set; }
    [MaxLength(120)]
    public string vc_ModelNumber { get; set; }
    public decimal dc_ReplacementCost { get; set; }
    [MaxLength(255)]
    public string vc_InsertBy { get; set; }
    public DateTime dt_InsertDate { get; set; }
    [MaxLength(255)]
    public string vc_UpdateBy { get; set; }
    public DateTime dt_UpdateDate { get; set; }
    #endregion
}       // end of public partial class WarrantyFormLineReplacements

Core Problems that I caused

The Core problems were, I didn't have a Key (which a try catch and logging caught), and when I added the key I did not have the accessor/mutator methods defined for each variable ( {get; set;} ). Once I added those, it created the the tables without any issues into the database that was already created. It also created the __MigrationHistory table as I was expecting.

Database Already Created, new tables not adding in

1 Answers1