EF Core context open connection after a using statement closes it

c# ef-core-2.2 entity-framework-core

Question

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();
1
1
6/18/2019 6:09:44 PM

Popular Answer

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 DbSets.

1
6/17/2019 8:13:51 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow