String or binary data would be truncated.The statement has been terminated

Question

I am updating my database using the update-database command in Entity Framework and it is raising an error with the message: String or binary data would be truncated. The statement has been terminated

How can I allow the update to work?

Answer 1

Take this code first entity:

public class Something
{
    public string SomeProperty { get; set; }
}

That will create a column of type NVARCHAR(MAX) which will let you pretty much store any size text.

Now we apply an annotation like this:

public class Something
{
    [MaxLength(50)]
    public string SomeProperty { get; set; }
}

So now the migration from this has to shorten your column to 50 characters only. If you have entries that are longer that 50 characters, you will get that error message.

Solution 1

Fix the data! Remove any data longer than 50 characters. Using SQL, either delete the offending rows:

DELETE FROM MyTable
WHERE LEN(MyColumn) > 50

Or, probably a better idea is to manually truncate the data:

UPDATE MyTable
SET MyColumn = LEFT(MyColumn, 50)
WHERE LEN(MyColumn) > 50

Solution 2 (not 100% sure this will work)

Let the migration truncate the data by using this command:

Update-Database -Force

Answer 2

Here is an article on how to debug this or any EF related issues:

String or binary data would be truncated. The statement has been terminated

Basically what you have to do is to turn on 'SQL Server Profiler' and turn on 'Exception' and 'RPC:Starting' profilers. When you get your error in profiler, you will be able to see full query that was executed by EF. From there you can copy and paste to MS SQL Server Management Studio run it manually and identify the issue.

Answer 3

In EF dot net core "code first" one can do this SQL Statement:

UPDATE MyTable
SET MyColumn = LEFT(MyColumn, 50)
WHERE LEN(MyColumn) > 50

In the Migration UP part, one can execute the sql like this:

modelBuilder.sql("UPDATE MyTable SET MyColumn = LEFT(MyColumn, 50) WHERE LEN(MyColumn) > 50");

Please note that the statement has to be beofre the before the alter of string length.