Entity Framework 6 SQL Server Compact 3.5 without Config file

Question

I am trying to access an SQL Server Compact 3.5 database using Entity Framework 6 using EntityFramework.SqlServerCompact.Legacy 6.1.3.

My application is a Visual Studio extension and there is no way that I can add anything to the application config file.

If I make a simple test application, I can connect to the database without any problems. If I try to access the database from a class library it fails with the error message

Schema specified is not valid. Errors: ProjectTemplate.ssdl(2,2) : error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlServerCe.3.5'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

I have found some interesting links, for example here and here but I can't get it to work, either because the example apply to EF5 or to SQL Server (or I have wool over my eyes).

How do I do it?

Answer 1

I am currently testing with a console application and a class library. The console application knows nothing about Entity Framework and simply calls a method in the class library.

static void Main(string[] args)
{
  EFTest t = new EFTest();
  t.Test();
}

This is the code in the class library.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.EntityClient;

namespace EF6_SQLCompact_CleanTest_000
{
  public class EFTest
  {
    public void Test()
    {
      try
      {
        // Add an event handler for DbConfiguration.Loaded, which adds our dependency 
        // resolver class to the chain of resolvers.
        System.Data.Entity.DbConfiguration.Loaded += (_, a) => {
          a.AddDependencyResolver(new MyDependencyResolver(), true);
        };

        // Define the provider connection string, specifying the SQL Server Compact 3.5 filename.
        String FileName = @"f:\DotNetTestProjects\2015\CS\SQLCompact35DllTest\SQLCompact35DllTest\ProjectTemplate.sdf";
        String ConnectionString = String.Format("Data Source={0}", FileName);

        // Create the entity framework connection string, specifying the model,
        // the provider and the provider connection string.
        var builder = new EntityConnectionStringBuilder();
        builder.Metadata = @"res://*/ProjectTemplate.csdl|res://*/ProjectTemplate.ssdl|res://*/ProjectTemplate.msl";
        builder.Provider = "System.Data.SqlServerCe.3.5";
        builder.ProviderConnectionString = ConnectionString;

        // Create the entity framework kontext
        Entities Context = new Entities(builder.ToString());

        // Do a trivial query as a test.
        int i = Context.Languages.Count();
        MessageBox.Show(i.ToString());
      }
      catch (Exception e)
      {
        MessageBox.Show(e.Message);
      }
    }
  }

  // Define an additional constructor for the Entities class, so that we can 
  // pass the connection string into the base class DbContext.
  public partial class Entities : DbContext
  {
    public Entities(String ConnectionString)
      : base(ConnectionString)
    {
    }
  }

  class MyDependencyResolver : System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver
  {
    public object GetService(Type type, object key)
    {
      // Output the service attempting to be resolved along with it's key 
      System.Diagnostics.Debug.WriteLine(string.Format("MyDependencyResolver.GetService({0}, {1})", type.Name, key == null ? "" : key.ToString()));

      if ((type == typeof(System.Data.Common.DbProviderFactory))
           && (key != null)
           && ((string)(key) == "System.Data.SqlServerCe.3.5"))
      {
        return System.Data.SqlServerCe.SqlCeProviderFactory.Instance ;
      }
      else if ((type == typeof(System.Data.Entity.Core.Common.DbProviderServices))
                && (key != null)
                && ((string)(key) == "System.Data.SqlServerCe.3.5"))
      {
        return System.Data.Entity.SqlServerCompact.Legacy.SqlCeProviderServices.Instance ;
      }
      else if ((type == typeof(System.Data.Entity.Infrastructure.IProviderInvariantName))
                && (key is System.Data.SqlServerCe.SqlCeProviderFactory))
      {
        return new MyProviderInvariantName();
      }

      return null;
    }

    public IEnumerable<object> GetServices(Type type, object key)
    {
      return new object[] { GetService(type, key) }.ToList().Where(o => o != null);
    }
  }

  // Implement IProviderInvariantName so that we can return an object when
  // requested in GetService()
  class MyProviderInvariantName : IProviderInvariantName
  {
    public string Name
    {
      get { return "System.Data.SqlServerCe.3.5"; }
    }
  }
}

The dependency resolver is based on based on the answer from Ryan Griffith in the link mentioned by EricEJ.

This appears to work in the context of my test program, but I have not yet tried it in the context of a Visual Studio Extension.

So far as I can tell, this technique will only work if an event handler is added to DbConfiguration.Loaded before the DbConfiguration is loaded for the first time in the AppDomain (which I might not be able to guarantee). I am also worried that it may have side effects for Visual Studio or for other Visual Studio extensions.