EF6 database first location of cscdl, .msl and .ssdl files

Question

I have a WPF application containing an EF6.0 project with a database first approach. The user should be able to open different databases in different locations with different names. So my ConnectionString does not come from the App.config like here:

  <connectionStrings>
<add name="SQLiteDatabaseEntities" connectionString="metadata=res://*/SQLiteModel.csdl|res://*/SQLiteModel.ssdl|res://*/SQLiteModel.msl;provider=System.Data.SQLite.EF6;provider connection string=&quot;data source=C:\SomePathThatMayChange\Database.sqlite;useutf16encoding=True;synchronous=Full&quot;" providerName="System.Data.EntityClient" />

I rather have to create it for myself in code and set the data source to the correct location:

if (false == File.Exists(fullPathAndFilename))
{
    return string.Empty;
}
EntityConnectionStringBuilder entityConnectionStringBuilder = new EntityConnectionStringBuilder();
entityConnectionStringBuilder.Name = "SQLiteConnection";
entityConnectionStringBuilder.Provider = "System.Data.EntityClient";
entityConnectionStringBuilder.ConnectionString = "metadata=res://*/SQLiteModel.csdl|res://*/SQLiteModel.ssdl|res://*/SQLiteModel.msl;provider=System.Data.SQLite.EF6;provider connection string=';data source=" + fullPathAndFilename + "';";
return entityConnectionStringBuilder.ToString();

Now I changed the structure of my application by adding other projects and putting the EF things into a separate project (different to the main project) that only contains database things. When debugging the following code I run into an exception

    try
{
    using (Entities context = new Entities(ConnectionString))
    {
        context.Database.Connection.Open();
        using (System.Data.Entity.DbContextTransaction dbTran = context.Database.BeginTransaction())
        {
            try
            {
                // Do things:
                // ...
                return true;
            }
            catch
            {
                dbTran.Rollback();
            }
        }
    }
}
catch (Exception ex)
{
    System.Diagnostics.Trace.WriteLine(ex.Message);
}

The exception message is: Schema specified is not valid. Errors: SQLiteModel.ssdl(2,2) : error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SQLite.EF6'. Make sure the provider is registered in the 'entityFramework' section of the application config file.

I found out that some files are not at the location where they should be. That is the SQLiteModel.csdl, SQLiteModel.msl, and the SQLiteModel.ssdl. They where in an ...\obj\Debug\edmxResourcesToEmbed\ directory. I also found out that in the *.ssdl file there is also a data source specifyed under ProviderManifestToken="data source=

<Schema Namespace="RohlingModel.Store" Provider="System.Data.SQLite.EF6" ProviderManifestToken="data source=C:\SomePathThatMayChange\Database.sqlite" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">

I assume my program worked only accidently the first time. And now I have some questions: 1. Do I really have to embed this *ssdl file containing a fixed location for the data source? 2. Would it be possible at all to change the database location later by setting the data source in the ConnectionString? 3. Is a database first approach feasible at all in this situation? 4. Where do I have to copy/embed these files in my multiproject solution?

Thanks in advance!

Update: I found out that my questions are silly. The problem seems to be that in my main project the app.config does not contain all the needed information. Since I want to separate the database stuff from the rest there shouldn't be EF related things in the app.config of the main project at all.

I resolved the issue by programming the app.config. My app.config looks like this:

<? xml version="1.0" encoding="utf-8"?>
<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>
  <entityFramework>
    <defaultConnectionFactory type = "System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework" >
      < parameters >
        < parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName = "System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName = "System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName = "System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant = "System.Data.SQLite.EF6" />
      < remove invariant="System.Data.SQLite" />
      <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" />
      <add name = "SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.104.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name = "SQLiteDatabaseEntities" connectionString="metadata=res://*/SQLiteModel.csdl|res://*/SQLiteModel.ssdl|res://*/SQLiteModel.msl;provider=System.Data.SQLite.EF6;provider connection string=&quot;data source=C:\SomePath\SQLiteDatabase.Template;useutf16encoding=True;synchronous=Full&quot;" providerName="System.Data.EntityClient" />
    <add name = "Entities" connectionString="metadata=res://*/SQLiteModel.csdl|res://*/SQLiteModel.ssdl|res://*/SQLiteModel.msl;provider=System.Data.SQLite.EF6;provider connection string=&quot;data source=C:\SomePath\SQLiteDatabase.Template;useutf16encoding=True;synchronous=Full&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

I created a class MySQLiteConfiguration that looks like that:

        public class MySQLiteConfiguration : DbConfiguration
    {
        public MySQLiteConfiguration()
        {
            SetDefaultConnectionFactory(new System.Data.Entity.Infrastructure.LocalDbConnectionFactory("mssqllocaldb"));
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);

            // this provider is part of the App.config. However, it seems to be needed only for updating the model from the database in this "database first approach" rather than reading from or writing to the database.
            //SetProviderServices("System.Data.SqlClient", System.Data.Entity.SqlServer.SqlProviderServices.Instance);

            DbProviderServices sqLiteProviderServices = (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices));
            SetProviderServices("System.Data.SQLite", sqLiteProviderServices);
            SetProviderServices("System.Data.SQLite.EF6", sqLiteProviderServices);

            // These providers have to be specified in detail. Since they are there already they must be exchanged:
            List<Provider> providersToExchange = new List<Provider>();
            providersToExchange.Add(new Provider("SQLite Data Provider (Entity Framework 6)", "System.Data.SQLite.EF6", ".NET Framework Data Provider for SQLite (Entity Framework 6)", "System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6"));
            providersToExchange.Add(new Provider("SQLite Data Provider", "System.Data.SQLite", ".NET Framework Data Provider for SQLite", "System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.104.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"));

            try
            {
                System.Data.DataRowCollection rows = (System.Configuration.ConfigurationManager.GetSection("system.data") as System.Data.DataSet).Tables?[0]?.Rows;

                if (rows != null)
                {
                    foreach (Provider provider in providersToExchange)
                    {
                        for (int i = 0; i < rows.Count; i++)
                        {
                            if (rows[i]["InvariantName"] as string == provider.InvariantName)
                            {
                                rows.RemoveAt(i);
                                break;
                            }
                        }
                    }

                    foreach (Provider provider in providersToExchange)
                    {
                        rows.Add(provider.InvariantName, provider.Description, provider.Name, provider.Type);
                    }
                }
            }
            catch (System.Data.ConstraintException constraintException)
            {
                System.Diagnostics.Debug.Assert(false, constraintException.Message);
            }
        }

        private class Provider
        {
            internal string Name { get; set; }
            internal string InvariantName { get; set; }
            internal string Description { get; set; }
            internal string Type { get; set; }

            internal Provider(string name, string invariantName, string description, string type)
            {
                Name = name;
                InvariantName = invariantName;
                Description = description;
                Type = type;
            }
        }
    }

and this class is used to configure the context:

     [DbConfigurationType(typeof(MySQLiteConfiguration))]
    public partial class Entities : DbContext
    {
        public Entities(string connectionString)
            : base(connectionString)
        { }
    }

When working with the context I use the constructor that gets my very own connection string passed:

            try
        {
            using (Entities context = new Entities(ConnectionString))
            {
                // Do things...
                // ...
            }
        }
        catch { }

I am sorry for my silly questions. I hope to help some people having hard times when doing their first steps with Entity Framework 6.0 and SQLite.

Regards