Is it possible to use EF Core 3 FromSqlRaw to create new object list

0

I am working on a query with linq that uses a DbSet with FromSqlRaw. I am trying to get a different object list.

var habitaciones = await _context.HabitacionDisponibilidad 
    .FromSqlRaw("pg_get_habitacionDisponibles {0},{1},{2},{3},{4},{5}", 
     edificio.EdificioId, sm.Huespedes, sm.FechaInicial, sm.FechaFinal, EstatusDisponibilidad.Disponible, cantidadNoches)
     .Select(p => new HabitacionDisponible
     {
            EdificioId = p.EdificioId,
            HabitacionDisponibilidadId = p.HabitacionDisponibilidadId,
            HabitacionId = p.HabitacionId,
            TipoHabitacionId = p.Habitacion.TipoHabitacionId,
            Tarifa = p.Tarifa
     })
     .ToListAsync();

If I remove this code the query works

     .Select(p => new HabitacionDisponible
     {
            EdificioId = p.EdificioId,
            HabitacionDisponibilidadId = p.HabitacionDisponibilidadId,
            HabitacionId = p.HabitacionId,
            TipoHabitacionId = p.Habitacion.TipoHabitacionId,
            Tarifa = p.Tarifa
     })

This is the sql coe that runs in the sql server:

exec sp_executesql N'SELECT [h].[EdificioId], [h].[HabitacionDisponibilidadId], [h].[HabitacionId], [h0].[TipoHabitacionId], [h].[Tarifa]
FROM
(
   pg_get_habitacionDisponibles @p0, @p1, @p2, @p3, @p4, @p5
) AS [h]
INNER JOIN [Habitacion] AS [h0] ON [h].[HabitacionId] = [h0].[HabitacionId]',N'@p0 uniqueidentifier,@p1 int,@p2 datetime2(7),@p3 datetime2(7),@p4 int,@p5 float',@p0='AD8FEBD4-AF89-4EDE-5300-08D72BF290CE',@p1=1,@p2='2019-10-15 00:00:00',@p3='2019-10-18 00:00:00',@p4=1,@p5=4

And this is the error I get when running the query:

Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@p0'.
at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext 
executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:ab3f2f48-7ec6-4518-9be5-cc9ae15bbefb
Error Number:102,State:1,Class:15

Is this the right way to do what I want to achieve, or is there any other recommendation that you could give me?

linq
ef-core-3.0
asked on Stack Overflow Oct 17, 2019 by albertolo

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0