Entity Framework Core how do you pass a list of parameters when using fromSql



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;
4/26/2019 3:07:02 PM

Accepted Answer

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()
4/26/2019 7:32:51 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow