Call table-valued function Entity Framework Core in .NET Core 6

Question

I am new to .NET and one of my first goals is creating an API in .NET Core 6. In this API I need to call a TVF with 2 parameters in my GET method in order to return the result in json.

I found many replies to this question and people always use SqlQuery method like this:

_context.Database.SqlQuery<model>("select * from myFunction()")

However, it looks like SqlQuery is missing in .NET Core 6. Another approach was using a DbDataReader like this:

var command = _context.Database.GetDbConnection().CreateCommand();
command.CommandText = "SELECT * FROM myFunction()";
DbDataReader reader = await command.ExecuteReaderAsync();

In this case I get an error

The connection's current state is closed

I am a bit confusing at this point because my other methods work fine, my context is working. Anyway, I am not sure if this is a good approach.

Any help would be appreciated.

This is my entire controller:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using API.Models;

namespace API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmpleadoController : ControllerBase
    {
        private readonly DBContext _context;

        public EmpleadoController(DBContext context)
        {
            _context = context;
        }

        // GET: api/Empleado
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Empleado>>> GetEmpleados()
        {
            return await _context.Empleados.ToListAsync();
        }

        // GET: api/Empleado/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Empleado>> GetEmpleado(int id)
        {
            var empleado = await _context.Empleados.FindAsync(id);

            if (empleado == null)
            {
                return NotFound();
            }

            return empleado;
        }

        // GET: api/Empleado/5/2022
        [HttpGet("{id},{year}")]
        public async Task<ActionResult<List<DiaCalendario>>> GetCalendario(int id, int year)
        {
            var command = _context.Database.GetDbConnection().CreateCommand();
            command.CommandText = "SELECT * FROM dbo.getCalendarioEmpleado(" + year + "," + id +")";
            DbDataReader reader = await command.ExecuteReaderAsync();

            if (reader != null)
            {
                List<DiaCalendario> Calendario = new List<DiaCalendario>();

                while (reader.Read())
                {
                    DiaCalendario dia = new DiaCalendario();
                    dia.Fecha = (DateOnly)reader["Fecha"];
                    dia.TipoDia = (int)reader["TipoDia"];
                    dia.PT = (bool)reader["PT"];
                    dia.Horas = (decimal)reader["Horas"];

                    Calendario.Add(dia);
                }

                return Calendario;
            }
            else
            {
                return NotFound();
            }
        }

        private bool EmpleadoExists(int id)
        {
            return _context.Empleados.Any(e => e.EmpleadoId == id);
        }
    }
}

Why is your get calendar method using ADO? Everything else is using a context. — GH DevOps, Nov 01 '22 at 11:30
You want to do a Raw SQL. See : https://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx?force_isolation=true — jdweng, Nov 01 '22 at 11:48
@GHDevOps, because I still don't know how to map the function. I read this article but I haven't make it working yet: https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping — Sergio Teijido, Nov 01 '22 at 12:05
@jdweng, both DbContext.Database.SqlQuery() and DbContext.Database.ExecuteSqlCommand() are missing in Core 6. — Sergio Teijido, Nov 01 '22 at 12:06
Not sure what library you are using. Looks like from MSDN SQLQuery is still supported. See : https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.sqlquery?view=entity-framework-6.2.0&force_isolation=true Make sure your model inherits the dBContext. — jdweng, Nov 01 '22 at 12:21
@jdweng, I am using framework .NET Core 6 and EntityFrameworkCore 6.0.10. Methods SqlQuery, ExecuteSqlCommand or FromSql are missing. I have ExecuteSqlRaw and ExecuteSqlInterpolated, but they return only the number of rows affected. Same problem here, with no solution or alternative: https://stackoverflow.com/questions/73151721/entity-framework-net-6-0-sql-query-cant-find — Sergio Teijido, Nov 01 '22 at 12:38
See ErikEJ answer and commend beneath. https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core?force_isolation=true — jdweng, Nov 01 '22 at 13:32
@jdweng, great, that was just I needed, I could solve my issue, thank you. — Sergio Teijido, Nov 01 '22 at 17:38

score 0 · Answer 1 · answered Nov 01 '22 at 17:47

Thanks to @jdweng's comments I could solve my problem.

The function is mapped to the context in DbContext.cs with this:

modelBuilder.Entity<DiaCalendario>(entity =>
            {
                entity.HasNoKey();
                entity.ToFunction("getCalendarioEmpleado");
                entity.Property(e => e.Fecha).HasColumnName("Fecha");
                entity.Property(e => e.TipoDia).HasColumnName("TipoDia");
                entity.Property(e => e.PT).HasColumnName("PT");
                entity.Property(e => e.ParteTrabajoID).HasColumnName("ParteTrabajoID");
                entity.Property(e => e.Horas).HasColumnName("Horas");
            });

In my controller I just call the function like this:

// GET: api/Empleado/219/2022
[HttpGet("{id}/{year}")]
public async Task<ActionResult<List<DiaCalendario>>> GetCalendario(int id, int year)
{
    string sql = "select * from dbo.getCalendarioEmpleado(" + year + "," + id + ")";
    return await _context.DiaCalendario.FromSqlRaw(sql).ToListAsync();
}

Be careful with FromSqlRaw/SQL Injection folks, take a look at [Microsoft recommendation](https://learn.microsoft.com/en-us/ef/core/querying/sql-queries#passing-parameters). The tldr of it is use FromSql or FromSqlInterpolated where possible. And if not possible, protect against SQL injection by constructing your parameters via DbParameter: `var columnValue = new SqlParameter("columnValue", "http://SomeURL");` — fuzzy_logic, Feb 23 '23 at 03:23

Call table-valued function Entity Framework Core in .NET Core 6

1 Answers1