How to map from query string to SQL query? [closed]

So I have as search endpoint

[HttpGet("search")]
public async Task<IActionResult> Search([FromQuery] string query)

that returns a list of users from the cosmosDb based on email, name etc.

The problem is that I don’t know how to convert the query string into an SQL query or LINQ statement. I have googled for hours, but I can’t find any examples, but it just seems weird if I have to write my own query parser?

Answer

Given the example value of query in your comment, I guess you actually mean something like /users/search?query=firstname%3DLucy%26lastname%3DJohnson (note the %3D instead of %D and %26 instead of &). The decoded value of this would be firstname=Lucy&lastname=Johnson. In this case you’d have kind of a query string inside the value of your query string’s query parameter.

One solution would be to get rid of the query parameter and just use the parameters of your actual query as normal query string parameters.

For example:

// URL with query string:
// /users/search?firstName=Lucy&lastname=Johnson

// Matching controller method
[HttpGet("search")]
public async Task<IActionResult> Search(
  [FromQuery] string firstName = null, 
  [FromQuery] string lastName = null)
{
  // use the firstName and lastName variables to put together some 
  // SQL as a string or include them in some linq
}

With the solution described above, you don’t have to parse anything as the values of your query would be parsed out of the box by asp.net. You would however have to put together the query to the database yourself.

If you want to stick to your original design and have one single query parameter that itself is a query string you can do the following:

// URL with query string:
// https://localhost:5000/search?query%3DLucy&26lastname%3DJohnson

// Matching controller method
[HttpGet("search")]
public async Task<IActionResult> Search(
  [FromQuery] string query)
{
  var parsed = HttpUtility.ParseQueryString(query);
  // ...or, depending on what version of .NET you're using:
  // var parsed = QueryHelpers.ParseQuery(query);
  var firstName = parsed["firstName"];
  var lastName = parsed["lastName"];
  // use the firstName and lastName variables to put together some 
  // SQL as a string or include them in some linq
}