Question 87896 in ASP.NET Question posted in
The official documentation can be found here.

How to compare a list of integers in EF Core Interpolated Query – Asp.net

I am trying to convert my sql queries being run with EF Core’s .FromSqlRaw() method into queries to be run with .FromSqlInterpolated() so they are less vulnerable to SQL injection attacks. I’ve gotten almost everything converted and working fine, but the one thing that is stumping me is how to filter by a list of integers in an or configuration on a single field.
The database is postgres, and the quotes are because I used code first EF Core which means that all of the tables are capitalized like the class. ProjectTypeId is an integer column in my table, and projectTypes is a List<int> type variable.

The code in my where clause I’m trying to replace is:

WHERE ""PartGroups"".""ProjectTypeId"" IN({string.Join(",", projectTypes)})

The closest I’ve been able to get to getting it working is with this:

""PartGroups"".""ProjectTypeId""::text IN({string.Join(",", projectType)})

or

""PartGroups"".""ProjectTypeId""::text LIKE ANY(ARRAY[{string.Join(",", projectTypes)}])

These will work when there is only one value in projectTypes, but any more than that and it fails. I don’t know how to view the resulting query + parameter set, just the query, so I’m not sure what’s it’s doing to the parameter, so I’ve been struggling to figure out something that works. Also, the query has ~80 parameters total, so manually setting each one with a raw query isn’t really feasible.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    So I found a way to actually see how EF Core is interpreting the parameters for the interpolated query and was able to play around with it to get what I wanted out of it. So just in case someone wants to or needs to stick to a pure interpolated query, you can use the following pattern for your needs:

    WHERE MyTable.MyIntColumn::text LIKE ANY(Array[{string.Join(",", myIntList).Split(",", StringSplitOptions.None)}])

    This also works with text values (just no need to cast to text) and you can use wildcards like this:

    WHERE MyTable.MyStringColumn ILIKE ANY(ARRAY[{("%" + string.Join("%,%", myStringList) + "%").Split(",", StringSplitOptions.None)}])

  2. 0

    One way you could approach it, perhaps, is to leverage EF’s ability to compose over the top of a raw

    context.SomeTable
  .FromSqlInterpolated($"SeLeCt t.* FrOm ...")
  .Where(st => idList.Contains(st.id))

    EF will put your SQL in as a sub query and write the IN for you in the outer. The DB query optimizer will (probably) then push the IN into the sub query if it can..

    SELECT ...
FROM 
  (
    SeLeCt t.* FrOm ...
  ) x
WHERE x.ID IN (.., ..)

    If you want to have a look at the SQL EF made and you’re on EF5+ the easiest thing to do is do everything apart from ToList/ToArray, and capture the queryable:

    var q = context.Thing.Where(...)

    Then pause in the debugger and look at the DebugView property of the q. You’ll get the full SQL text and you can slot it into SSMS and inspect the execution plan, check it runs the same as a single level (non hierarchical) query..

    Login or Signup to reply.
  3. 0

    The other thing you can do, is create your query in a FormattableString yourself. So long as FromSqlInterpolated receives a FormattableString it will pull it apart and parameterize

        var args = new object[] { your, single, arguments, here}.Concat(yourListOfThingsHere.Cast<object>()).ToArray();

    var fs = FormattableStringFactory.Create(@"SELECT
  some,
  columns/{0},
  here 
FROM
  table t
WHERE  
  someColumn = {1} AND
  otherColumn BETWEEN {2} and {3} AND 
  columnToBeINned IN({" + string.Join("},{", Enumerable.Range(4, yourListOfThingsHere.Count)) + @"})
GROUP BY some, columns/{0}", args);

   var qq = context.Table.FromSqlInterpolated(fs).Where(m => ...);

    It’s, of course, possible to write a helper to do this for you…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search