0

when I run my SQL query directly I have to issues:

SELECT [hostname]
  FROM [ComputerImport]
  where LOWER(SUBSTRING (computerimport.hostname, 1, 2)) in ('DE', 'FR') AND computerimport.hostname LIKE '%'

but when I try to do it out of c# I get no results

string searchString = ""
List<string> allowedStatus = new List<string>();
allowedStatus.Add("de");
            string sqlquery = "SELECT computerimport.hostname FROM ComputerImport WHERE LOWER(SUBSTRING (computerimport.hostname, 1, 2)) in ({1}) AND (computerimport.hostname LIKE '%' + {0} + '%') ";                          

_context.OverviewQueries.FromSqlRaw(sqlquery, searchString, string.Join(",", allowedStatus.Select(s => "'" + s + "'").ToArray()));

I also tried:

string.Join(",", allowedStatus.Select(s => "'" + s + "'")
string.Join(",", allowedStatus)
string.Join(",", allowedStatus)ToArray()

What works is:

_context.OverviewQueries.FromSqlRaw(sqlquery, searchString, "DE");

Any idea what I am missing?

Stephan
  • 335
  • 3
  • 12
  • Does this answer your question? [how to write sql query text for IN clause in C#](https://stackoverflow.com/questions/49858620/how-to-write-sql-query-text-for-in-clause-in-c-sharp) – mortb Jun 09 '20 at 09:40
  • Also, your code is open to a SQL injection attack. More info: https://stackoverflow.com/questions/4892166/how-does-sqlparameter-prevent-sql-injection – mortb Jun 09 '20 at 09:42
  • because you are using formatted version - you are basically sending one string which is wrong. You either change into SQL command or create finallize string before execution – Pribina Jun 09 '20 at 09:45

1 Answers1

0

You may use basic linq, FromSqlRaw is more for using Table Valued Functions etc.

string searchString = ""
List<string> allowedStatus = new List<string>();
allowedStatus.Add("de");

return _context.ComputerImport
  .Where(o => o.hostname != null && allowedStatus.Contains(o.hostname.Substring(0,2).ToLower()))
  .Where(o => o.hostname.Contains(searchString));
Leszek Mazur
  • 2,443
  • 1
  • 14
  • 28
  • Hi, sorry, I did not post the whole query but I am also using GROUP BY and STRING_AGG, thats whay I am using SQL – Stephan Jun 09 '20 at 09:51
  • [Group](https://learn.microsoft.com/en-gb/dotnet/csharp/linq/group-query-results) is also in Linq, string class contains [Join](https://learn.microsoft.com/en-us/dotnet/api/system.string.join?view=netcore-3.1) method – Leszek Mazur Jun 09 '20 at 09:52
  • but not STRING_AGG or group_concat, or am I missing something? I tried it with LINQ but I got crazy – Stephan Jun 09 '20 at 09:54
  • So you should ask question like "How to get result like STRING_AGG for GROUP BY SQL query using linq?" and add minimum reproducible example in description. But it may be duplicate: https://stackoverflow.com/questions/60007040/converting-t-sql-string-agg-to-linq-c-sharp – Leszek Mazur Jun 09 '20 at 09:59
  • Hi, thanks for the link. But that way I just dont get it working. I am always getting a GroupByShaperExpression, or when I just follow the example that in the last var the columns do not exist. Regarding the injection, I read that it is enough to put a $ before the query, I also tried putting different SQL statements in the query, no problem so far. – Stephan Jun 10 '20 at 18:28