I need to set an attribute in my object with a value of a procedure. How i do this?

Question

I have a list of products in a database, I'm using linq to get the list. But, I need to know if the product is on sale and I am using a procedure to do this, a tried something like the code bellow:

select new ProdutoItemTabelaPreco
           {
               Produto = p,
               ItemTabelaPreco = ii,
               ClienteId = usuarioClienteEmpresa.FirstOrDefault().ClienteId,
               EmpresaId = usuarioClienteEmpresa.FirstOrDefault().EmpresaId,
               Tipo = pc.Tipo,
               ProdutoId = pc.ProdutoId,
               Quantidade = pc.Quantidade,
               ValorPromocao = _context.ProcedureValorPromocao
                                       .FromSqlRaw(ProcedureValorPromocao.Sql(clienteDistrbuidor.FirstOrDefault().DistribuidorId, pc.ProdutoId, 1))
                                       .AsNoTracking()
                                       .ToList()
                                       .FirstOrDefault().ValorPromocao,
               Promocao = _context.ProcedureValorPromocao
                                  .FromSqlRaw(ProcedureValorPromocao.Sql(clienteDistrbuidor.FirstOrDefault().DistribuidorId, pc.ProdutoId, 1))
                                  .AsNoTracking()
                                  .ToList()
                                  .FirstOrDefault().Promocao
           };

Here is the procedure call:

ValorPromocao = _context.ProcedureValorPromocao
                        .FromSqlRaw(ProcedureValorPromocao.Sql(clienteDistrbuidor.FirstOrDefault().DistribuidorId, pc.ProdutoId, 1))
                        .AsNoTracking()
                        .ToList()
                        .FirstOrDefault().ValorPromocao

The ProdutoItemTabelaPreco is the object that I want to set the value of procedure.

This is the response I get:

{
    "code": 404,
    "errorCode": 0,
    "message": "Expression of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ProcedureValorPromocao]' cannot be used for parameter of type 'Microsoft.EntityFrameworkCore.DbSet`1[ProcedureValorPromocao]' of method 'System.Linq.IQueryable`1[ProcedureValorPromocao] FromSqlRaw[ProcedureValorPromocao](Microsoft.EntityFrameworkCore.DbSet`1[ProcedureValorPromocao], System.String, System.Object[])' (Parameter 'arg0')"
}

Answer 1

transform it first outside as Entity Framework converts it to SQL at the same time

so my suggestion would be

var sqlProcedureQuery = ProcedureValorPromocao.Sql(clienteDistrbuidor.FirstOrDefault().DistribuidorId, pc.ProdutoId, 1);



ValorPromocao = _context.ProcedureValorPromocao
                        .FromSqlRaw(sqlProcedureQuery)
                        .AsNoTracking()
                        .ToList()
                        .FirstOrDefault().ValorPromocao