EXEC sp_executesql framed from "FromSQLRaw" not working as expected

Question

I'm trying to get some details from Database using our EF core application with the help of "fromSQLRaw" function by framing the Query.

1. This is the Final Query that I wanted to build to get the desired results and this query is working fine : select * from [User] U where Substring(U.UserName, 1,Charindex(' ', M.UserName)) in ('Test1','Test2')

But,When I'm trying to do it with sqlrawquery as following from my data layer, code is like:

var userNameParams = new string[userNames.Count];

var sqlParameters = new List<SqlParameter>();

for (var i = 0; i < userNames.Count; i++)

{

userNameParams[i] = string.Format("@p{0}", i);

sqlParameters.Add(new SqlParameter(userNameParams[i], userNames[i]));

}

var rawCommand = string.Format("select * from [User] U where Substring(U.UserName, 1,Charindex(' ', U.UserName)) in ({0})", string.Join(",", userNameParams));

// Executing the SQL Raw Query

var userDetails = await _dbContext.Users.FromSqlRaw(rawCommand, sqlParameters.ToArray()).ToListAsync();

After running this statement I've checked the framed query in SQL profiler and the query is like

exec sp_executesql N'select * from [User] U where Substring(U.UserName, 1,Charindex('' '', U.UserName)) in (@p0,@p1) ',N'@p0 nvarchar(10),@p1 nvarchar(10)',@p0=N'''Test1''',@p1=N'''Test2'''

But when I'm running this framed query I'm not getting the results even though there are records but I'm getting results from the first query (point 1).

Where I'm going wrong and how can we resolve this?

I need to get the results from the query that is framed from rawsql.

Answer 1

var names = string.Join(',', userNames.Select(n => n.ToString()).ToArray());

var rawCommand = string.Format("select * from [User] U where Substring(U.UserName, 1,Charindex(' ', U.UserName)) in ({names})");

try the above