SqlServer: Saving changes is not permitted. The changes you have made require the followign tables to be dropped and re-created

Problem:

Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Solution:

This is different than the default in Sql Server 2005, where you can automatically drop and recreate a table in the designer.  Solution is simple, and doesn't require you to lose your changes.

Screenshots:

In Sql Server Management Studio 2008, go to tools à options, in the options window, left pane Designers, and on the right side uncheck Prevent saving changes that require table re-creation.

System.InvalidOperationException was unhandled by user code The model backing the 'SomeContext' context has changed since the database was created

Problem:
Received the following exception when adding a field to an Entity class:

System.InvalidOperationException was unhandled by user code

  Message=The model backing the 'MovieDBContext' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

  Source=EntityFramework

Full Stack Trace at the end.

Code the exception occurred:

        MovieDBContext db = new MovieDBContext();

        public ActionResult Index()

        {

            var movies = from m in db.Movies

                         where m.ReleaseDate > new DateTime(1984, 6, 1)

                         select m;

            return View(movies.ToList());

        }

Solution:

Update the table in Sql Server to have the additional column you added to your entitiy class.

Full Stack Trace:

  StackTrace:

       at System.Data.Entity.Database.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context)

       at System.Data.Entity.Database.DbDatabase.<>c__DisplayClass8.b__5()

       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)

       at System.Data.Entity.Database.DbDatabase.Initialize(Boolean force)

       at System.Data.Entity.Internal.InternalContext.Initialize()

       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)

       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()

       at System.Data.Entity.Internal.Linq.InternalSet`1.get_Provider()

       at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()

       at System.Linq.Queryable.Where[TSource](IQueryable`1 source, Expression`1 predicate)

       at MvcMovie.Controllers.MoviesController.Index() in xxxx\visual studio 2010\Projects\MovieApp\MvcMovie\Controllers\MoviesController.cs:line 16

       at lambda_method(Closure , ControllerBase , Object[] )

       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)

       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)

       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)

       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.b__12()

       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)

System.ServiceModel.CommunicationException was unhandled Exception

Problem:

Message=The underlying connection was closed: The connection was closed unexpectedly.

I was making a WCF client/service, I ran it for the first time and got that exception.  So, my WCF service had an OperationContract to return a DataTable like so:

[ServiceContract]
    public interface IUserService
    {
        [OperationContract]
        DataTable GetApprovedUser();
    }

And it was implemented like this:

public DataTable GetApprovedUser()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("UserId", typeof (Int64));
            dt.Columns.Add("IsApproved", typeof (bool));

            DataRow newUser = dt.NewRow();
            newUser["UserId"] = 1000;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1001;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1002;
            newUser["IsApproved"] = false;
            dt.Rows.Add(newUser);
            newUser = dt.NewRow();
            newUser["UserId"] = 1003;
            newUser["IsApproved"] = true;
            dt.Rows.Add(newUser);

            return dt;
        }

And my Client:

            ServiceReference1.UserServiceClient proxy = new ServiceReference1.UserServiceClient();

            string User = proxy.Authenticate("theuser", "somecrypt");

            foreach(DataRow userRow in proxy.GetApprovedUser().Tables[0].Rows)

            {

                Console.WriteLine(string.Format("User: {0}, IsApproved: ", userRow["UserId"], userRow["IsApproved"]));

            }

Solution:

Turns out that DataTables are not serializable, so I tried wrapping the DataTable in a DataSet like this:

Interface:

    [ServiceContract]

    public interface IUserService

    {

        [OperationContract]

        void DoWork();

        [OperationContract]

        string Authenticate(string userName, string aCryptKey);

        [OperationContract]

        DataSet GetApprovedUser();

    }

Implementation:

        public DataSet GetApprovedUser()

        {

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();

            ds.Tables.Add(dt);

            dt.Columns.Add("UserId", typeof (Int64));

            dt.Columns.Add("IsApproved", typeof (bool));

            DataRow newUser = dt.NewRow();

            newUser["UserId"] = 1000;

            newUser["IsApproved"] = true;

            dt.Rows.Add(newUser);

            newUser = dt.NewRow();

            newUser["UserId"] = 1001;

            newUser["IsApproved"] = true;

            dt.Rows.Add(newUser);

            newUser = dt.NewRow();

            newUser["UserId"] = 1002;

            newUser["IsApproved"] = false;

            dt.Rows.Add(newUser);

            newUser = dt.NewRow();

            newUser["UserId"] = 1003;

            newUser["IsApproved"] = true;

            dt.Rows.Add(newUser);

            return ds;

        }

And that fixed it.

For those interested, here was the full stack trace:

  Message=The underlying connection was closed: The connection was closed unexpectedly.

  Source=mscorlib

  StackTrace:

    Server stack trace:

       at System.ServiceModel.Channels.HttpChannelUtilities.ProcessGetResponseWebException(WebException webException, HttpWebRequest request, HttpAbortReason abortReason)

       at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)

       at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)

       at System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message message, TimeSpan timeout)

       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)

       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)

       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:

       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

       at WCRTestClient.ServiceReference1.IUserService.GetApprovedUser()

       at WCRTestClient.ServiceReference1.UserServiceClient.GetApprovedUser() in xxxx\visual studio 2010\Projects\Test WCF\WCRTestClient\Service References\ServiceReference1\Reference.cs:line 64

       at WCRTestClient.Program.Main(String[] args) in xxxx\visual studio 2010\Projects\Test WCF\WCRTestClient\Program.cs:line 15

       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)

       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

       at System.Threading.ThreadHelper.ThreadStart()

  InnerException: System.Net.WebException

       Message=The underlying connection was closed: The connection was closed unexpectedly.

       Source=System

       StackTrace:

            at System.Net.HttpWebRequest.GetResponse()

            at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)