In a .NET core web api app I'm using ef core in a function that i have to run some raw sql then when I try to use the context after the raw sql it fails.
the context is passed from the from controller to logic files(actual business code) is a private readonly I've tried opening the connection again but that fails.
DbConnection conn = _context.Database.GetDbConnection();
using (conn)
{
await conn.OpenAsync();
using (DbCommand cmd = conn.CreateCommand())
{
//Raw SQL
}
}
Table1 t1 = await _context.Table1
.Where(i => i.id == 1)
.SingleOrDefaultAsync();
Everything works fine until the app gets to the Table1 t1
line then i get the error
The error I get is
InvalidOperationException: The ConnectionString property has not been initialized.
System.Data.SqlClient.SqlConnection.PermissionDemand()
Edit
I'm asking is there a best practice way to run raw SQL that returns data that is not mapped to an entity then continue to use EF Core. Also sometimes my raw SQL is only returning a string or decimal value
Edit #2
Thanks for the help I'll be using the suggestion made by Ivan Stoev
Updated working Code
DbConnection conn = _context.Database.GetDbConnection();
await context.Database.OpenConnectionAsync();
using (DbCommand cmd = conn.CreateCommand())
{
//Raw SQL
}
context.Database.CloseConnection();
Table1 t1 = await _context.Table1
.Where(i => i.id == 1)
.SingleOrDefaultAsync();
You are using
the connection which calls Dispose
and closes the connection at the end of the using
block. There's no need for that using
at all, simply remove it. However, it seems like you are trying to run a raw SQL command on the database, EF Core has this ability already built in, for example:
await _context.Database.ExecuteSqlCommandAsync("DELETE FROM SomeTable");
There are also some FromSql
methods specific to DbSet
s.