Using Entity Framework Core 2.1 I have code that calls a stored procedure, passing in some params. During execution we are using Sql Server and everything works as expected. Our test cases however, run using Sqlite. Is there a decent way (without overly polluting the code with unwanted code added only to support testing) to get the code to run regardless of which database is actually being used?
Currently the code in question looks like this:
await MyContext.Database.ExecuteSqlCommandAsync("UpdateEndDate @p0, @p1, @p2", id, from, thru);
And causes a SqliteException:
Microsoft.Data.Sqlite.SqliteException
HResult=0x80004005
Message=SQLite Error 1: 'near "UpdateEndDate": syntax error'.
Source=Microsoft.Data.Sqlite
...
(Also wondering why are there SqlParameter and SQLiteParameter? Why cant there be a database agnostic parameter class?)
Configuration for DBContext in test harness is below:
public static SqliteConnection GetConnection()
{
SqliteConnection connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
connection.CreateFunction(
"NEWID",
Guid.NewGuid(),
g => g);
return connection;
}
serviceProvider = new ServiceCollection()
.<snipped>
.AddEntityFrameworkSqlite()
.AddDbContext<MyContext>(
(theServiceProvider, opt) => opt
.UseInternalServiceProvider(theServiceProvider)
.UseSqlite(GetConnection()))
.BuildServiceProvider();
MyContext cc = serviceProvider.GetService(typeof(MyContext)) as MyContext;
cc.Database.EnsureCreated();
Have you considered using the DbProviderFactory class? It became available in Core 2.1. Put both connection strings in your appSettings.json and in the startup set appropriate DbContext based on the environment it's running in.
https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbproviderfactory?view=netcore-2.1
User contributions licensed under CC BY-SA 3.0