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?
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)
If you need to combine .FromSql() and SQL WHERE x IN () then perform a second operation on the output IQueryable<T> of the .FromSql() call.
var ids = new List { 10, 20 };
var query = context.Users.FromSqlInterpolated(
$@"select *
from Users");
if (ids?.Count > 0)
{
query = query.Where(user => ids.Contains(user.Id));
}
Resultant SQL approximates to:
select * from (
select *
from Users
) u
where u.Id in (10,20)
This solution is for EfCore 6.x.
This is a different parameterized solution uses a Table-Value Parameter and may be more efficient or more practical than using queryable.Where(q => ids.Contains(q.id)) for some use-cases.
CREATE TYPE [dbo].[Ids] AS TABLE (
[Id] UNIQUEIDENTIFIER NULL);
public static SqlParameter ToIdsTableValueParameter(this IEnumerable<string> ids)
{
DataTable dataTable = new();
dataTable.Columns.Add("Id");
foreach (string id in ids)
{
dataTable.Rows.Add(new string[] { id });
}
return new SqlParameter()
{
ParameterName = "ids",
TypeName = "[dbo].[Ids]",
SqlDbType = SqlDbType.Structured,
Value = dataTable
};
}
dbset.fromSqlInterpolated(...) where the @param is is the SqlParameter from step 2.await dbContext.YourEntities
.FromSqlInterpolated(
$"select * from yourEntities y join {@param} ids on ids.id = y.Id")
.ToListAsync(cancellationToken)
