CODEWITHSUNDEEP
entity-framework-corelinq-to-entitiesentity-framework-core-3.1
Miguel Moura
Miguel Moura

Reputation: 39364

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

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?

Upvotes: 9

Views: 7532

Answers (3)

captainkout
captainkout

Reputation: 79

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.

  1. Define a type in Database
CREATE TYPE [dbo].[Ids] AS TABLE (
    [Id] UNIQUEIDENTIFIER NULL);
  1. Create a SqlParameter that maps to the Type from step 1. I wrote an extension method to do this, but the initialization could be locally scoped.
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
    };
}
  1. Chain the 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)

Upvotes: 0

Scotty.NET
Scotty.NET

Reputation: 12561

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)
  • No invalid SQL if ids is empty
  • No empty result if ids is empty string (I was working with a string data type)

Upvotes: 5

Ivan Stoev
Ivan Stoev

Reputation: 205589

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)

Upvotes: 16

Related Questions