I'm trying to get some details from Database using our EF core application with the help of "fromSQLRaw" function by framing the Query.
- 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.
CodePudding user response:
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