sqlite EF DbUpdateException

Question

I am trying to learn SQLite and entity framework using a simple console application defined below. When I run this in VS I get the exception shown below when context.SaveChanges() is executed. I need help in fixing this.

using System;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;

namespace EntityFrameworkConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            sb.DataSource = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "test.db");
            sb.ForeignKeys = true;
            sb.BinaryGUID = true;
            sb.DateTimeFormat = SQLiteDateFormats.ISO8601;
            sb.DateTimeKind = DateTimeKind.Local;
            sb.ToFullPath = true;

            using (var context = new BloggingContext(new SQLiteConnection(sb.ConnectionString)))
            {

                context.Blogs.Add(new Blog { Name = "Yet Another Blog #1" });

                context.SaveChanges();

                var blogs = (from b in context.Blogs
                             orderby b.Name
                             select b).ToList();

            }
        }

    }
}

using System.Data.Common;
using System.Data.Entity;

namespace EntityFrameworkConsoleApplication1
{
    [DbConfigurationType(typeof(MyConfiguration))]
    public class BloggingContext : DbContext
    {
        public BloggingContext(DbConnection c):base(c,true)
        {
        }

        public DbSet<Blog> Blogs { get; set; }
    }
}

namespace EntityFrameworkConsoleApplication1
{
    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
}

using System;
using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.SQLite;
using System.Data.SQLite.Linq;
using System.Reflection;

namespace EntityFrameworkConsoleApplication1
{
    public class MyConfiguration : DbConfiguration
    {
        public MyConfiguration()
        {
            SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
            Type t = Type.GetType(
                       "System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6");
            FieldInfo fi = t.GetField("Instance", BindingFlags.NonPublic | BindingFlags.Static);
            SetProviderServices("System.Data.SQLite", (DbProviderServices)fi.GetValue(null));
        }
    }
}

System.Data.Entity.Infrastructure.DbUpdateException was unhandled
_HResult=-2146233087 _message=An error occurred while updating the entries. See the inner exception for details. HResult=-2146233087
IsTransient=false Message=An error occurred while updating the entries. See the inner exception for details. Source=EntityFramework StackTrace: at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at EntityFrameworkConsoleApplication1.Program.Main(String[] args) in c:\Users\John\Documents\Visual Studio 2013\Projects\EntityFrameworkConsoleApplication1\EntityFrameworkConsoleApplication1\Program.cs:line 33 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.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException: System.Data.Entity.Core.UpdateException _HResult=-2146233087 _message=An error occurred while updating the entries. See the inner exception for details. HResult=-2146233087 IsTransient=false Message=An error occurred while updating the entries. See the inner exception for details. Source=EntityFramework StackTrace: at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.b__2(UpdateTranslator ut) at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func2 updateFunction, Boolean throwOnClosedConnection) at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update(Boolean throwOnClosedConnection) at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__33() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy) at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass28.b__25() at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges() InnerException: System.Data.SQLite.SQLiteException _HResult=-2147467259 _message=SQL logic error or missing database no such table: Blogs HResult=-2147467259 IsTransient=false Message=SQL logic error or missing database no such table: Blogs Source=System.Data.SQLite ErrorCode=1 StackTrace: at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c__DisplayClassb.<Reader>b__8() at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TInterceptionContext,TResult](Func1 operation, TInterceptionContext interceptionContext, Action1 executing, Action1 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary2 identifierValues, List`1 generatedValues) at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() InnerException:

Yes I seen this. The context does have a local copy of the Blogs before the SaveChanges(). Is this error message saying that the database file, when created does not have the information for Blogs? I just noticed that the database file is being created before the context is being created. I had thought that when the context is created the database file is created. — user3396724, May 18 '14 at 18:40

ILOVECSHARP · Accepted Answer · 2014-05-18T22:48:50.267

0

I guess you have changed your model or you have added Blogs after database created since first time. In Constractor of BloggingContext write this:

Database.SetInitializer<BloggingContext>(new DropCreateDatabaseIfModelChanges<BloggingContext>());

Hope it works.

edited May 18 '14 at 22:48

answered May 18 '14 at 22:36

ILOVECSHARP

51
4

I tried this and get the following exception "DeleteDatabase is not supported by the provider". I am trying to create and modify the database file in the application. It was not created previously. – user3396724 May 19 '14 at 01:29
So why did you got this exception? unless EF has tried to delete something. if Delete database is not supported by provider, try Migration. http://www.entityframeworktutorial.net/code-first/migration-in-code-first.aspx – ILOVECSHARP May 19 '14 at 04:27
I am trying to create the database using code first in the application. This is an example of an app in which I allow the user to create new databases of items. The code example above was my attempt of creating a new database file using code first and then to add items to it. Given the error I am getting I must be doing something incorrectly in the code. Is it possible to accomplish creating the database using code first and then adding items to it? – user3396724 May 20 '14 at 02:22
Yes. I had similar problem before. Because of this i said maybe you changed your model, even a tiny change in the names. So if it was the first time that you ran you progrom, the problem in somewhere else. Instead of "DropCreateDatabaseIfModelChanges" use "DropCreateDatabaseAlways" – ILOVECSHARP May 20 '14 at 10:43
Add this assembly: using System.ComponentModel.DataAnnotation.Schema; and the above of Blog class write this. [Table("Blogs")] Above BlogId write this: [Key] Set Breakpoint on DataBase.SetInitializer<>.. Run the progrom. At Breakpoint --> F10 --> right click on DataBase word --> QuickWatch. Find the Connection string. Maybe it's different than what you gave it. – ILOVECSHARP May 20 '14 at 11:04
ILOVECSHARP, thanks for providing feedback but I am still having problems. "DropCreateDatabaseAlways" causes the same exception as before "Delete Database is not supported by the provider". Do I have the following correct when creating the database? – user3396724 May 21 '14 at 01:06
Do I have the beginning of the Main function correct where I create a connection string then a SQLiteConnection and then pass this connection into my context constructor? – user3396724 May 21 '14 at 01:13
I added the data annotations and viewed the connection string. The connection string looks correct. – user3396724 May 21 '14 at 01:21
You're welcome. Take a look at this: http://www.bricelam.net/2012/10/entity-framework-on-sqlite.html And read comments. Seems SQlite provider doesn't support creating database for older versions of EF. One guy who left a comment there had similar exception. And this: http://forums.devart.com/viewtopic.php?t=23616 and this post may be helpful too: http://stackoverflow.com/questions/20460357/problems-using-entity-framework-6-and-sqlite – ILOVECSHARP May 21 '14 at 04:41
Yes, what you did for Connection String is correct. – ILOVECSHARP May 21 '14 at 05:34

sqlite EF DbUpdateException

1 Answers1