How to compare a list of integers in EF Core Interpolated Query-CodePudding

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)})

""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.

CodePudding user response：

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

context.SomeTable
  .From..($"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..

CodePudding user response：

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...