Use array of ints parameter in FromSQL query and Where In clause

entity-framework-core entity-framework-core-3.1 linq-to-entities

Question

I have a list of ints:

var ids = new List { 10, 20 };

And I need to find Users with that ids:

context.Users.FromSqlInterpolated($@" 
  select Users.* 
  where Users.Id in ({String.Join(',', ids)})"

But I get the following error:

'Conversion failed when converting the nvarchar value '10, 20' to data type int.'

How can I use such a parameter?

1
0
3/30/2020 3:53:25 PM

Accepted Answer

Using Interpolated method is not appropriate here, because {String.Join(',', ids)} defines single string placeholder, hence EF Core binds single nvarchar parameter with value '10,20', so the actual SQL is like this

select Users.* 
where Users.Id in ('10,20')

which is invalid, hence the exception.

You should use Raw method instead. Either

var query = context.Users.FromSqlRaw($@" 
select Users.* 
where Users.Id in ({String.Join(',', ids)})");

which will embed literal values

select Users.* 
where Users.Id in (10,20)

or if you want to parameterize it, generate parameter placeholders like {0}, {1} etc. inside the SQL and pass values separately:

var placeholders = string.Join(",", Enumerable.Range(0, ids.Count)
    .Select(i => "{" + i + "}"));
var values = ids.Cast<object>().ToArray();

var query = context.Users.FromSqlRaw($@" 
select Users.* 
where Users.Id in ({placeholders})", values);

which would generate SQL like this

select Users.* 
where Users.Id in (@p0,@p1)
1
3/30/2020 4:44:14 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow