Entity Framework with mutliple database servers

Question

Today we're developing a solution for Windows environments with SQL Server CE and Entity Framework 6.13 as ORM (Code First). However we're studying the availability to port it to Linux environments and, of course, as Linux doesn't support SQL Server databases, we intend to use SQLite on Linux machines and continue using SQL Server on Windows machines. And the solution (.sln) will be the same for both environments.

So, is possible to have an Entity Framework model connecting with multiple databases (SQLServer, SQLite,..)? Should I create a model for each database?

For this scenario, is NHibernate a better solution than Entity Framework? Or is there anything else?

I've already found a couple of answers about that, but no one recently.

Ps: code first is not necessary and we're open to change it if needed.

Many thanks! :-)

score 1 · Accepted Answer · answered May 18 '15 at 14:39

using NHibernate (with FluentNHibernate) and code it would be like

using NHCfg = NHibernate.Cfg;

var config = new Configuration().AddMappingsFromAssembly<Entity>();
if (UseSqlCe)
{
    config.SetProperty(NHCfg.Environment.Driver, typeof(SqlCeDriver).AssemblyQualifiedName);
    config.SetProperty(NHCfg.Environment.Dialect, typeof(SqlCeDialect).AssemblyQualifiedName);
}
else if (UseSqlite)
{
    config.SetProperty(NHCfg.Environment.Driver, typeof(Sqlite20Driver).AssemblyQualifiedName);
    config.SetProperty(NHCfg.Environment.Dialect, typeof(SqliteDialect).AssemblyQualifiedName);
}

With EF it would be similar to http://rob.conery.io/2014/02/05/using-entity-framework-6-with-postgresql/ where you need to fight against some strong ties to MSSqlServer (CE) e.g.:

migrations or code-first creation ?only? work for MSSQL
default schema is 'dbo'

My advice would be to start off with what you are more familiar.

Mind you i am biased but some reasons for NHibernate:

easy testing with sqlite in memory and

config.SetProperty(NHibernate.Cfg.Environment.ConnectionProvider, typeof(SingletonConnectionProvider).AssemblyQualifiedName);

/// <summary>
/// ensures that the same connection is used for all sessions. Useful for in-memory databases like sqlite
/// </summary>
public class SingletonConnectionProvider : DriverConnectionProvider
{
    private IDbConnection _theConnection;

    public override void CloseConnection(IDbConnection conn)
    {
    }

    public override IDbConnection GetConnection()
    {
        if (_theConnection == null)
        {
            _theConnection = base.GetConnection();
        }
        return _theConnection;
    }
}

schemacreation for bioth databases (SchemaExport class)
read batching using futures

Entity Framework with mutliple database servers

1 Answers1