8

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?

Anas Alweish
  • 2,818
  • 4
  • 30
  • 44
Ilyas Idrees
  • 109
  • 1
  • 2
  • 9
  • 1
    You'll need to add the source code for the properties on your class on this - at a guess, you've not annotated the property correctly. – Rowland Shaw Jul 24 '14 at 10:13
  • Does this answer your question? [DbUpdateException: Which field is causing "String or binary data would be truncated"](https://stackoverflow.com/questions/43396895/dbupdateexception-which-field-is-causing-string-or-binary-data-would-be-trunca) – Ash K Mar 03 '22 at 17:58
  • Check out this answer if you want to catch the offending fields using C#: https://stackoverflow.com/a/71341426/8644294 – Ash K Mar 03 '22 at 18:00

5 Answers5

18

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
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • 7
    Using the `-Force` flag doesn't override the truncation error. However, running the `UPDATE MyTable SET MyColumn = LEFT(MyColumn, 50)` did allow the `Update-Database` command to complete successfully. – Travis Russi Sep 29 '15 at 15:45
  • VS2019. -Force doesn't work. I had a column NVARCHAR(60) and wanted to set it to NVARCHAR(6) with a code-first migration. There was just one existing row in the table with 10 characters in the column, while all the rest were 6, and "Update-Database" raised this exception, both with and without "-Force". I just used the SQL Server console to fix this one entry (reduced to 6 characters) and the Update-Database worked. – Stephen Hosking Nov 12 '21 at 20:29
3

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.

Vlad Bezden
  • 83,883
  • 25
  • 248
  • 179
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.

ddkserv
  • 96
  • 3
1

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

g00dnatur3
  • 1,173
  • 9
  • 16
  • yes i am trying to alter the length from NVARCHAR(MAX) to nvarchar(60),NVARCHAR(MAX) to nvarchar(30),NVARCHAR(MAX) to nvarchar(5).when i passed the command in package manager console,add-migration DataAnnotations and update-database,this error arises,tell me the solution kindly – Ilyas Idrees Jul 24 '14 at 10:23
  • execute this SQL to find the max length of data in column: SELECT name, LENGTH(name) AS mlen FROM mytable ORDER BY mlen DESC LIMIT 1 – g00dnatur3 Jul 24 '14 at 10:30
  • use the max length value when altering your table... otherwise you need to write a special DML SQL UPDATE query to truncate the data explicitly – g00dnatur3 Jul 24 '14 at 10:32
  • @user1500191 Pretty sure `LIMIT 1` isn't available in SQL Server, it's a MySQL thing right? – DavidG Jul 24 '14 at 10:35
  • sql-server: select max(len(Desc)) from table_name – g00dnatur3 Jul 24 '14 at 10:51
0

It is a property length issue.You are trying to insert property's value which length is less than your declared property's length.

Ripon Chowdhury
  • 1