EF Core 8 does not inject parameters in raw SQL query

Question

I need to build a raw SQL query and I am using the new EF Core 8. But when it executes the query, it seems that the parameters are not getting replaced.

private static FormattableString GetCardsBaseQuery(CardListFilter filter)
{
    var rows = filter!.RowsPerPage ?? 10;

    int offset = filter.Page ?? 1 - 1;
    offset = offset < 0 ?
              rows :
              rows * offset;

    var orderByParameter = new SqlParameter { ParameterName = "orderBy", SqlDbType = SqlDbType.NVarChar, Value = filter.SortBy };
    var orderByDirectionParameter = new SqlParameter { ParameterName = "orderDirection", SqlDbType = SqlDbType.NVarChar, Value = filter.SortDirection };
    var offsetParametar = new SqlParameter { ParameterName = "offset", SqlDbType = SqlDbType.Int, Value = offset };
    var rawParameter = new SqlParameter { ParameterName = "rows", SqlDbType = SqlDbType.Int, Value = rows };

    //! if you modify this query then you should change the CardListModel(reader) constructor as well
    var query = FormattableStringFactory.Create(@"
        SELECT
            c.BatchId,
            c.LuckyId,
            c.RefId,
            SUBSTRING(c.RefId, 6, 16) as [RawRefId],
            (c.FirstName + ' ' + c.LastName) as [Name],
            c.Email,
            c.MobileNumber,
            c.HasMarketingPermisson,
            c.Status,
            c.PlayedAt,
            c.BigPrize,
            c.DipPrize,
            c.DrawPrize,
            c.BigPrizePaymentStatus,
            c.BonusDrawStatus
        FROM
            [Card] c
        ORDER BY {0} {1}
        OFFSET {2} ROWS
        FETCH NEXT {3} ROWS ONLY", orderByParameter.Value, orderByDirectionParameter.Value, offsetParametar.Value, rawParameter.Value);

    return query;
}

public async Task<CardListModel> GetCardsByFilterAsync(CardListFilter filter)
{
    var basequery = GetCardsBaseQuery(filter);
    var queryable = context.Database.SqlQuery<CardListModel>(basequery);

    var result = await queryable.ToListAsync();
    
    return result ;
}

When I print the generated and executed query on console, I see the following error:

*error : Failed executing DbCommand (26ms) [Parameters=[p0='?' (Size = 4000), p1='?' (DbType = Int32), p2='?' (DbType = Int32), p3='?' (DbType = Int32)], CommandType='Text', CommandTimeout='300']
                        SELECT
                                c.BatchId,
                                c.LuckyId,
                                c.GilRefId,
                                SUBSTRING(c.GilRefId, 6, 16) as [RawGilRefId],
                                (c.FirstName + ' ' + c.LastName) as [Name],
                                c.Email,
                                c.MobileNumber,
                                c.HasMarketingPermisson,
                                c.Status,
                                c.PlayedAt,
                                c.BigPrize,
                                c.LuckyDipPrize,
                                c.BonusDrawPrize,
                                c.BigPrizePaymentStatus,
                                c.BonusDrawStatus
                        FROM
                                [Card] c
                        ORDER BY @p0 @p1
                        OFFSET @p2 ROWS
                        FETCH NEXT @p3 ROWS ONLY
fail: 02/03/2024 09:39:23.574 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)*

When I debug the code, I can see that the basequery variable got the params right with the right indexes, the values are ok, and if I copy the query everything is fine, it executes, but when it executes the param values are missing.

Thanks

Answer 1

ASC/DESC and column name for ORDER BY can't be parameterized. You need to validate them beforehand (for direction it is easy - basically it has only 3 possible values - ascending, descending and "null or empty") and inlining it (also maybe orderBy will need the same treatment too):

var query = FormattableStringFactory.Create(
    $$"""
        SELECT
            c.BatchId,
            c.LuckyId,
            c.RefId,
            SUBSTRING(c.RefId, 6, 16) as [RawRefId],
            (c.FirstName + ' ' + c.LastName) as [Name],
            c.Email,
            c.MobileNumber,
            c.HasMarketingPermisson,
            c.Status,
            c.PlayedAt,
            c.BigPrize,
            c.DipPrize,
            c.DrawPrize,
            c.BigPrizePaymentStatus,
            c.BonusDrawStatus
        FROM
            [Card] c
        ORDER BY {{filter.SortBy}} {{filter.SortDirection}}
        OFFSET {0} ROWS
        FETCH NEXT {1} ROWS ONLY
      """, offset , rows);

Also creating parameter variables in the way you use them is not needed, you can pass the filter values directly (i.e. filter.SortBy etc.)