Pass a String as Parameter Into _context.Database.SqlQuery ASP.NET MVC 5

Question

I have A controller

  public ActionResult Index(int[] eo){ employes = _context.Database.SqlQuery(select * from table where tableId IN (@p0),String.Join(",", eo))}

The problem is the data of @p0 is being passed as string and TableID is int. The debug shows @p0 as ('10,2,3,5') what I want is (10,2,3,5).

How I can handle this problem?

Does this answer your question? [Entity Framework Stored Procedure Table Value Parameter](https://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter) — Charlieface, Dec 09 '21 at 20:26

score 0 · Answer 1 · answered Dec 09 '21 at 20:04

There are probably more elegant ways to solve this problem, but you can just split the string into a list of strings and parse each of them to int.

Something like this:

string test = "10,2,3,5";
List<string> stringList = test.Split(",").ToList();
List<int> intList = new List<int>();
foreach(string str in stringList)
{
    intList.Add(int.Parse(str));
}

score 0 · Accepted Answer · answered Dec 09 '21 at 20:19

0

For SQL Server you can use JSON:

public ActionResult Index(int[] eo)
{ 
  var sql = @"
  select * 
  from table 
  where tableId IN (select value from openjson(@p0))
  ";

  var json = "[" + String.Join(",", eo) + "]";

  var employes = _context.Database.SqlQuery(sql,json).ToList();
}

answered Dec 09 '21 at 20:19

David Browne - Microsoft

80,331
6
39
67

thanks it worked but i just want to know if i opened myself to SQL injection with this method . – izwaf yeb le assia Dec 10 '21 at 10:56
Not with int[]. If it was string[] you’d need to worry. – David Browne - Microsoft Dec 10 '21 at 11:46

Pass a String as Parameter Into _context.Database.SqlQuery ASP.NET MVC 5

2 Answers2