There is already an open DataReader associated with this Connection which must be closed first in rawsql sentences

Question

context.Database.ExecuteSql raise the title exception. How must I use raw sql in .net core to retrieve a dataset and process it like ?

    public async Task<IActionResult> Refresh_Formatos()
    {            
        using (var context = _context)
        {
            var padre = context.Database.SqlQuery<int>(
                $@"SELECT id
                 FROM pds_etiquetas where nombre='FORMATOS' and tipo = 'Cat'
                 ").ToList();
            int padre_id = padre[0];
           
            var formatos = context.Database.SqlQuery<string>(
                $@"SELECT xdescripcion
                 FROM vc00_formatos a
                 left join pds_etiquetas c on a.xdescripcion=c.Nombre and {padre_id}=c.PadreId and c.tipo = 'Val'
                 where c.id is null
                 order by xdescripcion");
            context.Database.CloseConnection();
            foreach (var formato in formatos)
            {
                var affectedRows = context.Database.ExecuteSql(
                                    $@"INSERT INTO [imp].[pds_etiquetas]
                                           ([Nombre]
                                           ,[Tipo]
                                           ,[PadreId])
                                     VALUES
                                           ('{formato}'
                                           ,'Val'
                                           ,{padre_id})
                    ");
            }
        }                        
        return Ok();            
    } 

Answer 1

SqlQuery returns an IEnumerable that will execute the query when it is enumerated. You are enumerating it after you have closed the connection, so it is trying to execute the query after closing the connection.

Using ToList() before you close the connection should fix it as it will enumerate it when that is called. I.e.:

        var formatos = context.Database.SqlQuery<string>(
            $@"SELECT xdescripcion
             FROM vc00_formatos a
             left join pds_etiquetas c on a.xdescripcion=c.Nombre and {padre_id}=c.PadreId and c.tipo = 'Val'
             where c.id is null
             order by xdescripcion").ToList();
        context.Database.CloseConnection();

https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.sqlquery?view=entity-framework-6.2.0

Answer 2

You need to put formatos into a List

• You should also parameterize your queries properly
• You should use Async functions, but it appears the old EF does not provide SqlQueryAsync.

---

public async Task<IActionResult> Refresh_Formatos()
{            
    using (var context = _context)
    {
        var padre_id = context.Database.SqlQuery<int>(
            @"SELECT id
             FROM pds_etiquetas
             where nombre = 'FORMATOS'
               and tipo = 'Cat'
             ").First();
       
        var formatos = context.Database.SqlQuery<string>(
            @"SELECT xdescripcion
             FROM vc00_formatos a
             left join pds_etiquetas c on a.xdescripcion=c.Nombre and @padre_id = c.PadreId and c.tipo = 'Val'
             where c.id is null
             order by xdescripcion",
           new SqlParameter("@padre_id", SqlDbType.Int) { Value = padre_id }
           ).ToList();

        foreach (var formato in formatos)
        {
            var affectedRows = await context.Database.ExecuteSqlAsync(
                @"INSERT INTO imp.pds_etiquetas
                      (Nombr, Tipo, PadreId)
                  VALUES (@formato, 'Val', @padre_id)
                ",
                new SqlParameter("@padre_id", SqlDbType.Int) { Value = padre_id },
                new SqlParameter("@formato", SqlDbType.VarChar, 250) { Value = formato }
           );
        }
    }                        
    return Ok();            
} 

Having said that, you don't need to do any of this, you can just do one big joined INSERT.

You can also change the LEFT JOIN to a NOT EXISTS, and you should use a new context rather than an existing one.

public async Task<IActionResult> Refresh_Formatos()
{            
    using var context = new YourContextHere();
    var affectedRows = await context.Database.ExecuteSqlAsync(@"
INSERT INTO imp.pds_etiquetas
  (Nombr, Tipo, PadreId)
SELECT
  a.xdescripcion,
  'Val',
  p.id
FROM pds_etiquetas p
CROSS JOIN vc00_formatos a
where p.nombre = 'FORMATOS'
  and P.tipo = 'Cat'
  AND NOT EXISTS (SELECT 1
    FROM pds_etiquetas c
    WHERE a.xdescripcion = c.Nombre
      and p.id = c.PadreId
      and c.tipo = 'Val'
);
  ");
    return Ok();            
}