Things6: An XAF , EF Code First, SQLite Starter Project

working-out-loud

#1

There seem to be a few tricks getting these things to play nicely together.

I have written up my progress as I went. Sometimes I took a blind turn such as installing several packages in the win project when it turned out I could get away with one.

I have left these blind turns in as I am logging the steps I took ( i.e working out loud ) but I have also revisited some parts to add comments and side bar icons like the key to indicate a Key Point.

This project is on GitHub

  1. Create initial project
  2. Upgrade to EF 6.2 using Nuget
  3. Add SQLite.CodeFirst using Nuget
  4. Add System.Data.Sqlite using Nuget

Alter the program so we can follow the database creation more easily

            try {

                // access db so it is created here.
              HandyFunctions.EnsureDatabaseIsCreated();
                // end of modification

                winApplication.Setup();
                winApplication.Start();
            }
            catch(Exception e) {
                winApplication.HandleException(e);
            }

where HandyFunctions is in the Data Access Module.

using System.Linq;

namespace Things6.Module.BusinessObjects
{
    public static class HandyFunctions
    {
        public static void EnsureDatabaseIsCreated()
        {
            using (var db = new Things6DbContext())
            {
                var info = db.ModulesInfo.FirstOrDefault();
            }
        }
    }
}

Run it with Admin as the user

Oops, but we are still using Entity Framework. I need to fix app.config

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
  </configSections>
  <appSettings>
    <add key="Modules" value=""/>
    <add key="NewVersionServer" value=""/>
    <add key="EnableDiagnosticActions" value="False"/>

  </appSettings>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)"
           invariant="System.Data.SQLite.EF6"
           description=".NET Framework Data Provider for SQLite (Entity Framework 6)"
           type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite"
           description=".Net Framework Data Provider for SQLite"
           type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite.EF6"
                type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite"
               type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=c:\sqlite\things6_1.sqlite" providerName="System.Data.SQLite"/>
  </connectionStrings>
  <system.diagnostics>
    <switches>
      <!-- Use the one of predefined values: 0-Off, 1-Errors, 2-Warnings, 3-Info, 4-Verbose. The default value is 3. -->
      <add name="eXpressAppFramework" value="3"/>
      <!--
      <add name="XPO" value="3" />
      -->
    </switches>
  </system.diagnostics>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2"/>
  </startup>
</configuration>

Oops, looks like I am missing something

The Entity Framework provider type 'System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SQLite.EF6' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

   at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName)
   at System.Data.Entity.Internal.AppConfig.<.ctor>b__2(ProviderElement e)
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at System.Data.Entity.Internal.AppConfig.<.ctor>b__1()
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at System.Lazy`1.get_Value()
   at System.Data.Entity.Internal.AppConfig.get_DbProviderServices()
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.RegisterDbProviderServices()
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServiceFactory(Type type, String name)
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.<>c__DisplayClass1.<GetService>b__0(Tuple`2 t)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver)
   at System.Data.Entity.DbContext.InitializeLazyInternalContext(IInternalConnection internalConnection, DbCompiledModel model)
   at System.Data.Entity.DbContext..ctor(String nameOrConnectionString)
   at Things6.Module.BusinessObjects.Things6DbContext..ctor() in D:\dev\Things6\Things6.Module\BusinessObjects\Things6DbContext.cs:line 22
   at Things6.Module.BusinessObjects.HandyFunctions.EnsureDatabaseIsCreated() in D:\dev\Things6\Things6.Module\BusinessObjects\HandyFunctions.cs:line 9
   at Things6.Win.Program.Main() in D:\dev\Things6\Things6.Win\Program.cs:line 49

So add System.Data.SQLite.EF6 using Nuget?
Hmm actually it already shows as there.

Ah now I remember I need to set up the initializer
I learned that after a struggle a few days ago.

	Public Things6DbContext(String connectionString)
            : base(new SQLiteConnection() { ConnectionString = connectionString }, true)

        {
        }

Ah a new problem

he Entity Framework provider type 'System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SQLite.EF6' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

   at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName)
   at System.Data.Entity.Internal.AppConfig.<.ctor>b__2(ProviderElement e)
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at System.Data.Entity.Internal.AppConfig.<.ctor>b__1()
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at System.Lazy`1.get_Value()
   at System.Data.Entity.Internal.AppConfig.get_DbProviderServices()
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.RegisterDbProviderServices()
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServiceFactory(Type type, String name)
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.<>c__DisplayClass1.<GetService>b__0(Tuple`2 t)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver)
   at System.Data.Entity.DbContext.InitializeLazyInternalContext(IInternalConnection internalConnection, DbCompiledModel model)
   at System.Data.Entity.DbContext..ctor(String nameOrConnectionString)
   at Things6.Module.BusinessObjects.Things6DbContext..ctor() in D:\dev\Things6\Things6.Module\BusinessObjects\Things6DbContext.cs:line 25
   at Things6.Module.BusinessObjects.HandyFunctions.EnsureDatabaseIsCreated() in D:\dev\Things6\Things6.Module\BusinessObjects\HandyFunctions.cs:line 9
   at Things6.Win.Program.Main() in D:\dev\Things6\Things6.Win\Program.cs:line 49

#2

Time for some heavy debugging.
Ctrl Alt E
Check Common Language Runtime Exceptions
Run

Ah, I need to find out how to get the connectionstring using the name

Trying this ( after referencing system.configuration)


        //public Things6DbContext()
        //	: base("name=ConnectionString") {
        //}

        // now is

`      public Things6DbContext()
            : base(new SQLiteConnection()
            {
                ConnectionString =  System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
            }, true)
        {   }`

Oh dear, now when I run I get

Exception thrown: 'System.DllNotFoundException' in System.Data.SQLite.dll
Additional information: Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) occurred

Hmm Google?

Yuk!

Looking at the reference I see the path is

D:\dev\Things6\packages\System.Data.SQLite.Core.1.0.109.2\lib\net451\System.Data.SQLite.dll

I decide to try adding the package to the Win project.

Ah, the error has moved on

Lets wrap that in a try catch

    public static class HandyFunctions
    {
        public static void EnsureDatabaseIsCreated()
        {
            try
            {
                using (var db = new Things6DbContext())
                {
                    var info = db.ModulesInfo.FirstOrDefault();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
     
        }
    }

#3

What do you know.
Now the error has moved again

Exception thrown: 'System.InvalidOperationException' in EntityFramework.dll
Additional information: The Entity Framework provider type 'System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6' 
registered in the application config file for the ADO.NET provider with invariant name 
'System.Data.SQLite.EF6' could not be loaded. 
Make sure that the assembly-qualified name is used and that the assembly is available to the running application. 
See http://go.microsoft.com/fwlink/?LinkId=260882 for more information. occurred

I decide to add System.Data.SQLite.EF6 to the win project.

now the error becomes

Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll
Additional information: SQL logic error
no such table: __MigrationHistory

I add the Entity Framework to the win project and change the code to access a new Things table.

Now the error is

{System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SQLite.SQLiteException: SQL logic error
no such table: Things
   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.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   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.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at Things6.Module.BusinessObjects.HandyFunctions.EnsureDatabaseIsCreated() in D:\dev\Things6\Things6.Module\BusinessObjects\HandyFunctions.cs:line 14}
    [System.Data.Entity.Core.EntityCommandExecutionException]: {System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SQLite.SQLiteException: SQL logic error
no such table: Things
   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.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   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.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at Things6.Module.BusinessObjects.HandyFunctions.EnsureDatabaseIsCreated() in D:\dev\Things6\Things6.Module\BusinessObjects\HandyFunctions.cs:line 14}
    _className: "System.Data.Entity.Core.EntityCommandExecutionException"
    _data: null
    _dynamicMethods: null
    _exceptionMethod: null
    _exceptionMethodString: null
    _helpURL: null
    _HResult: -2146232004
    _innerException: {code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
no such table: Things
   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.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   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.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)}
    _ipForWatsonBuckets: 185993871
    _message: "An error occurred while executing the command definition. See the inner exception for details."
    _remoteStackIndex: 0
    _remoteStackTraceString: null
    _safeSerializationManager: {System.Runtime.Serialization.SafeSerializationManager}
    _source: null
    _stackTrace: {sbyte[384]}
    _stackTraceString: null
    _watsonBuckets: null
    _xcode: -532462766
    _xptrs: 0
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: null
    HResult: -2146232004
    InnerException: {code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
no such table: Things
   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.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   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.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)}
    IPForWatsonBuckets: 185993871
    IsTransient: false
    Message: "An error occurred while executing the command definition. See the inner exception for details."
    RemoteStackTrace: null
    Source: "EntityFramework"
    StackTrace: "   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)\r\n   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()\r\n   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()\r\n   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b
__0()\r\n   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()\r\n   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)\r\n   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)\r\n   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)\r\n   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)\r\n   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)\r\n   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)\r\n   at Things6.Module.BusinessObjects.HandyFunctions.EnsureDatabaseIsCreated() in D:\\dev\\Things6\\Things6.Module\\BusinessObjects\\HandyFunctions.cs:line 14"
    TargetSite: {System.Data.Common.DbDataReader ExecuteStoreCommands(System.Data.Entity.Core.EntityClient.EntityCommand, System.Data.CommandBehavior)}
    WatsonBuckets: null

Icon_64x64

Ah I forgot the instructions for Sqlite.CodeFirst

We need this in the context

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<MyDbContext>(modelBuilder);
        Database.SetInitializer(sqliteConnectionInitializer);
    }

Delete the empty data file and re-run.


#4

This looks hopeful
image

maybe not…
image

I tried a simple hack of accessing the table with DBBrowser for SQLite.and setting the password that way.
Thankfully it doesn’t work.

Ah, thats because the stored password is hashed.
Looking at the table in an Entity Framework XAF project I see ( after I set the password to SilverFish)

This is what the tables look like for an SQL Server , XAF project with Standard security

image

image


#5

I decide to try and get the data related references out of the win project.

Then I run into

The Entity Framework provider type 'System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SQLite.EF6' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

   at System.Data.Entity.Infrastructure.DependencyResolution.ProviderServicesFactory.GetInstance(String providerTypeName, String providerInvariantName)
   at System.Data.Entity.Internal.AppConfig.<.ctor>b__2(ProviderElement e)
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at System.Data.Entity.Internal.AppConfig.<.ctor>b__1()
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at System.Lazy`1.get_Value()
   at System.Data.Entity.Internal.AppConfig.get_DbProviderServices()
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.RegisterDbProviderServices()
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServiceFactory(Type type, String name)
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.<>c__DisplayClass1.<GetService>b__0(Tuple`2 t)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.<GetService>b__0(IDbDependencyResolver r)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
   at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver)
   at System.Data.Entity.DbContext.InitializeLazyInternalContext(IInternalConnection internalConnection, DbCompiledModel model)
   at System.Data.Entity.DbContext..ctor(DbConnection existingConnection, Boolean contextOwnsConnection)
   at Things6.Module.BusinessObjects.Things6DbContext..ctor() in D:\dev\Things6\Things6.Module\BusinessObjects\Things6DbContext.cs:line 31
   at Things6.Module.BusinessObjects.HandyFunctions.EnsureDatabaseIsCreated() in D:\dev\Things6\Things6.Module\BusinessObjects\HandyFunctions.cs:line 20
   at Things6.Win.Program.Main() in D:\dev\Things6\Things6.Win\Program.cs:line 49

OK, I will put the reference for System.Data.SQLite.EF6 back into the win project.

Now the app runs again… but my login is still rejected.


#6

Revisiting this for a new project I found the following modification is important. Otherwise the database creates without tables.

  public static class HandyFunctions
    {
        public static void EnsureDatabaseIsCreated()
        {
            try
            {
                using (var db = new Things6DbContext())
                {
                    var thing= db.Things.FirstOrDefault();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
     
        }
    }

Call this in program.cs

           try {

                // access db so it is created here.
                 HandyFunctions.EnsureDatabaseIsCreated();
                // end of modification

                winApplication.Setup();
                winApplication.Start();
            }
            catch(Exception e) {
                winApplication.HandleException(e);
            }