This is another post in a series on creating performant and scalable web APIs using ASP.NET Core. In this post, we’ll continue to focus on operating on large collections of data - this time allowing the consumers of our API to filter and search on the collection …

How filtering and searching helps performance

The reasons are the same as paging from the last post:

• The number of page read I/Os is reduced when SQL Server grabs the data
• The amount of data transferred from the database server to the web server is reduced
• The amount of memory used to store the data on the web server in our object model is reduced
• The amount of data transferred from the web server to the client is reduced

It also means the builder of the client application that consumes our API doesn’t have to do any client side filtering or searching which can have a nice positive impact on the overall performance as well.

Filtering

Filtering reduces a collection via criteria on filterable fields in the resource. Let’s implement a controller action method for: GET api/contacts?firstname={firstname}&surname={surname}. We’ll continue to use Dapper as our data access library …

The code is below. In summary, we construct the SQL, including the appropriate WHERE clause if filter values have been supplied in the URL.

[HttpGet]
public IActionResult GetContacts(UrlQuery urlQuery)
{
    IEnumerable<Contact> contacts = null;
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        string sql = @"SELECT ContactId, Title, FirstName, Surname
                       FROM Contact";
        if (urlQuery.HaveFilter)
        {
            string filterSQL = "";
            if (!string.IsNullOrEmpty(urlQuery.FirstName))
            {
                filterSQL += " FirstName = @FirstName";
            }
            if (!string.IsNullOrEmpty(urlQuery.Surname))
            {
                if (!string.IsNullOrEmpty(filterSQL))
                {
                    filterSQL += " AND";
                }
                filterSQL += " Surname = @Surname";
            }
            sql += $" WHERE {filterSQL}";
        }

        contacts = connection.Query<Contact>(sql, urlQuery);
    }

    return Ok(contacts);
}

Below is the model class for the filtering information that binds to the URL parameters:

public class UrlQuery
{
    public string FirstName { get; set; }
    public string Surname { get; set; }

    public bool HaveFilter => !string.IsNullOrEmpty(FirstName) || !string.IsNullOrEmpty(Surname);
}

Looking at the profiled SQL for GET api/contacts?surname=Kanne, we see that it is nice and efficient:

Looking at the SQL execution plan, we see that it is using an index on Surname:

Let’s also load test this endpoint:

Pretty good!

#asp.net #.net core #api #sqlserver