I'm building a query which should only include certain fields if a value is not null. In order to make this work, I need to pass into the query only the parameters needed by the actual query.
This is what I've tried so far, but I'm receiving an error telling me "SqlException: Must declare the scalar variable "@registrationStatus"."
public PagedResult<Restaurant> PaginatedResults(int page, string searchTerm, string accountStatus, string registrationStatus)
{
var parameters = new List<SqlParameter>();
var query = "SELECT * FROM Restaurant WHERE Name != '' ";
// Search term
if (!String.IsNullOrEmpty(searchTerm))
{
query += "AND (Name LIKE @searchTerm OR ContactName LIKE @searchTerm OR Email LIKE @searchTerm) ";
parameters.Add(new SqlParameter("@searchTerm", SqlDbType.VarChar) { Value = $"%{searchTerm}%" });
}
// Account status
if (!String.IsNullOrEmpty(accountStatus))
{
var enabled = accountStatus.Equals("ACTIVE") ? 1 : 0;
query += "AND Enabled = @enabled ";
parameters.Add(new SqlParameter("@enabled", SqlDbType.Bit) { Value = enabled});
}
// Registration status
if (!String.IsNullOrEmpty(registrationStatus))
{
query += "AND RegistrationStatus = @registrationStatus ";
parameters.Add(new SqlParameter("@registrationStatus", SqlDbType.VarChar) { Value = registrationStatus });
}
var pagedResult = new PagedResult<Restaurant>
{
CurrentPage = page,
PageSize = 20,
RowCount = _db.Restaurants.FromSql(query).ToList().Count
};
// Pagination
var queryWithPagination = query + "ORDER BY Id OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY";
parameters.Add(new SqlParameter("@offset", SqlDbType.Int) { Value = pagedResult.Offset()});
parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int) { Value = pagedResult.PageSize});
pagedResult.Results = _db.Restaurants.FromSql(queryWithPagination, parameters.ToArray()).ToList();
return pagedResult;
}
You are doing it correctly in general.
Just here
RowCount = _db.Restaurants.FromSql(query).ToList().Count
you forgot to pass parameters as you did here
pagedResult.Results = _db.Restaurants.FromSql(queryWithPagination, parameters.ToArray()).ToList();
hence the exception.
So simply provide them to the first call. Also (unrelated) you don't need to materialize the result (ToList
) just to get the Count
- FromSql
returns IQueryable<>
, so use Count()
queryable extension method instead to let the LINQ query be translated to SQL and evaluated server side:
RowCount = _db.Restaurants.FromSql(query, parameters.ToArray()).Count()