String or binary data would be truncated WHERE IN

Question

I am getting SQL Exception:

String or binary data would be truncated

in SELECT. I read few questions with same title but they were all about inserting. I am SELECTING.

Code is following:

List<CategoryName> navigation = await db.Query<CategoryName>().FromSql(
    $"WITH NestedCategories AS (
        SELECT *
        FROM Categories
        WHERE Id IN (
            {string.Join(",", products.Select(x =>
                x.Categories.First().CategoryId).Distinct().Select(x => $"'{x}'"))}
        )
        UNION ALL 
            SELECT t.*
            FROM Categories t
            INNER JOIN NestedCategories c On c.ParentId = t.Id
    )
    SELECT DISTINCT c.Id, c.Name, c.ParentId
    FROM NestedCategories c")
.AsNoTracking()
.ToListAsync();

If I generate string.Join to console and then put SQL command into query window in Management Studio I dont get any error. I get proper results. Issue is obviously in EF CORE that I am passing too many category Ids. Command is to get nesting categories based on Product-Category Id.

EDIT:

public class CategoryName
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
}

EDIT 2 - Solution

string inClause = string.Join(",", products.Select(x => x.Categories.First().CategoryId).Distinct().Select(x => $"'{x}'"));

List<CategoryName> navigation = new List<CategoryName>();

using (DbCommand command = db.Database.GetDbConnection().CreateCommand())
{
     command.CommandText = $"WITH NestedCategories AS (SELECT * FROM Categories WHERE Id IN ({inClause}) UNION ALL SELECT t.* FROM Categories t INNER JOIN NestedCategories c On c.ParentId = t.Id) SELECT DISTINCT c.Id, c.Name, c.ParentId FROM NestedCategories c";

      await db.Database.GetDbConnection().OpenAsync();

      DbDataReader reader = await command.ExecuteReaderAsync();

      while (await reader.ReadAsync())
          navigation.Add(new CategoryName() { Id = reader.GetInt32(0), Name = reader.GetString(1), ParentId = await reader.IsDBNullAsync(2) ? null : await reader.GetFieldValueAsync<int?>(2) });
}

The error is due to the fields in the SQL Table not matching the db class in c#. Either the class has more columns that the SQL Table or the data types in the SQL Table and the c# class are not the same. — jdweng
– jdweng, Commented Feb 5, 2019 at 11:48
Could the {string.Join(",", products.Select(x => x.Categories.First().CategoryId).Distinct())} be written as a SELECT in the SQL instead? — Andrew Morton
– Andrew Morton, Commented Feb 5, 2019 at 11:49
You are missing quotes for each of your strings in string.Join Add something like String.Join(",", list.Select(p => $"'{p}'") — schlonzo
– schlonzo, Commented Feb 5, 2019 at 11:49
@jdweng it is matching. Andrew: im afraid not. I would have to rewrite a lot of code. schlonzo: I tried that before. — user1085907
– user1085907, Commented Feb 5, 2019 at 11:57
@user1085907: And then? The error message should disappear then ;-) — schlonzo
– schlonzo, Commented Feb 5, 2019 at 11:58

Ivan Stoev · Accepted Answer · 2019-02-05 20:18:00Z

You should be very careful when using FromSql method with an inline interpolated SQL string.

Normally interpolated strings are resolved to string type, but FromSql method has overload with FormattableString parameter, which allows it to find the placeholders inside the interpolated string and bind a command parameter for each of them.

This normally is a functionality. But in your case you want just the joined string with ids to be embedded in the SQL string, while EF creates a parameter for it, so even if there was not a truncation error, the query won't return correct results because it would contain something like WHERE IN (@param) and @param will contain the comma separated text which will never match.

The simplest fix is to force the other FromSql method overload by either putting the SQL in a variable:

var sql = $"...";
List<CategoryName> navigation = await db.Query<CategoryName>().FromSql(sql)
    // ...

or use cast operator:

List<CategoryName> navigation = await db.Query<CategoryName>().FromSql((string)
    $"...")
    // ...

A better approach would to create placeholders ({0}, {1}, ...) inside the SQL string and pass values through params object[] parameters argument. This way EF Core will bind parameter for each value (e.g. WHERE IN (@p0, @p1, ...)) rather than embedded constants:

var parameters = products.Select(x => x.Categories.First().CategoryId).Distinct()
    .Cast<object>().ToArray(); // need object[]

var placeholders = string.Join(",", Enumerable.Range(0, parameters.Length)
    .Select(i = "{" + i + "}"));

var sql =
$"WITH NestedCategories AS (
    SELECT *
    FROM Categories
    WHERE Id IN ({placeholders})
    UNION ALL 
        SELECT t.*
        FROM Categories t
        INNER JOIN NestedCategories c On c.ParentId = t.Id
)
SELECT DISTINCT c.Id, c.Name, c.ParentId
FROM NestedCategories c";

var query = db.Query<CategoryName>().FromSql(sql, parameters);

Correct answer. Thanks for info. I will use simple solution cause there is no User interaction. I am just generating feeds and I need all nested categories for each product.

Collectives™ on Stack Overflow

String or binary data would be truncated WHERE IN

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related