EFCore 2.1 Dynamic SQL Params

Question

I think I am on the right track with this, but coming across something that I'll be honest is stumping me.

I have a table with Geography so for now, until Geo support comes to EFCore I am building my own SQL Query for a particular table. It's actually a search so it builds dynamically based on a set of query params that can be passed to an end point.

Because of this, I use reflection to iterate over a DTO and build a SQL query based on what properties have values. I am returning a Tuple from my query builder which contains a List<SqlParameter> and List<string>, the latter being the raw sql and the former being the params.

I then do an aggregate to put this all together.

I am getting the following issue:

  //This is inside my SQL Param builder method which returns a 
  //Tuple of sqlQuery and sqlParams
  if (!string.IsNullOrEmpty(search.Municipality))
  {
    sqlQuery.Add("Municipality LIKE %@Municipality%");
    sqlParams.Add(new SqlParameter("@Municipality", search.Municipality));
  }

  //Thi lines aggregates the result to build a SELECT * FROM query
  sql = sqlParams.Item2.Aggregate(sql, (current, t) => current + " AND " + t);

  //This executes the query
  return DbSet.FromSql(sql, sqlParams.Item1.ToArray()).ToListAsync();

  //This is the generated SQL string that is printed from the sql variable above 
  SELECT * FROM AirportData WHERE Municipality LIKE %@Municipality%

  //This is the error I get from EFCore
  System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@Municipality'.

I am just using 1 param for now to get this working.

Any thoughts on why it's not converting the SQL Param into a value for the query? Am I totally off base here? Thanks in advance for looking and any suggestions?

Answer 1

The error is not from EF Core, but SqlServer because %@Municipality% is not a valid SQL expression.

It should be something like '%' + @Municipality + '%' or N'%' + @Municipality + N'%', so modify you SQL builder accordingly, e.g.

sqlQuery.Add("Municipality LIKE '%' + @Municipality+ '%'");