String or binary data would be truncated.The state

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

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 2

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.

Answer 3

To me is sounds like you are trying to alter the length/size of a field in a table to be "smaller" then some of the data that already resides in it.

So for example if i have a varchar(28) type field field in my table-- with data that already inside that is 28 characters long... and then I try to execute an alter table to reduce the size to varchar(25) it will say: String or binary data would be truncated

It coul also arise IF you are trying to stuff a String that is lets say 30 cahraters long into a field that only supports 28 characters...

So it can happen if you are trying to insert data into a field and the data is to big to fit essientially

Answer 4

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