C# EF 异常：字符串或二进制数据将被截断。该语句已终止。？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13380972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EF Exception: String or binary data would be truncated. The statement has been terminated.?
提问by monstro
I have read many posts related to this issue, but couldn't find an answer. I am trying to load a large amount of data from Excel into SQL Server. Thousands of records. And I am getting this exception:
我已经阅读了许多与此问题相关的帖子，但找不到答案。我正在尝试将大量数据从 Excel 加载到 SQL Server。数以千计的记录。我得到这个例外：
String or binary data would be truncated. The statement has been terminated.
字符串或二进制数据将被截断。该语句已终止。
Obviously some values exceed the field size in the database. The error comes from SQL Server AFIK.
显然有些值超过了数据库中的字段大小。该错误来自 SQL Server AFIK。
My question - How could I possibly know what record and what field value caused this?
我的问题 - 我怎么可能知道是什么记录和什么字段值导致了这种情况？
There are no specific details in EF exception, except the one I mentioned.
EF 异常中没有具体细节，除了我提到的那个。
Any help is appreciated.
任何帮助表示赞赏。
Some asked for the code fragment, but it's actually very simple, the problem is not with the code:
有人要代码片段，其实很简单，不是代码的问题：
// employees is a List<Employee> collection loaded from Excel
using (var context = new Entities())
{
employees.ForEach(e => context.Employee.AddObject(e));
context.SaveChanges();
}
Also the suggested approach to use DbEntityValidationException(which is only available in Entity Framework 5.0) is not working, the catchblock didn't catch the exception.
此外，建议的使用DbEntityValidationException（仅在 Entity Framework 5.0 中可用）的方法不起作用，catch块没有捕获异常。
try
{
ImportData();
}
catch (DbEntityValidationException ex)
{
foreach (var item in ex.EntityValidationErrors)
{
//...
}
}
The only solution that I foundso far is to use SQL Server Profiler, and define the following events to monitor:
到目前为止，我找到的唯一解决方案是使用 SQL Server Profiler，并定义以下要监视的事件：
Now I can see the Email is too long.
现在我可以看到电子邮件太长了。
回答by Cuong Nguyen
catch (DbEntityValidationException ex)
{
foreach (var item in ex.EntityValidationErrors)
{
//... inspect here
}
}
You can find the information you need inside foreach loop.
您可以在 foreach 循环中找到您需要的信息。
Hope that helps.
希望有帮助。
回答by D Stanley
You can't at that level. SQL Server is rejecting the entire query.
你不能在那个级别。SQL Server 拒绝整个查询。
I would add some pre-checks to the data against your database constraints for string size, date formats, etc.
我会针对字符串大小、日期格式等的数据库约束对数据添加一些预检查。
Alternatively you could TRIMeach string field in the raw data to the corresponding field size before trying to insert.
或者，您可以TRIM在尝试插入之前将原始数据中的每个字符串字段设置为相应的字段大小。
回答by Ben
You can check data beforesaving, using EF metadata, and raise appropriate error.
您可以在保存前检查数据，使用 EF 元数据，并引发适当的错误。
回答by ShawnFumo
Not sure about truncation specifically, but here is a tip for when you get an exception that tells you to examine EntityValidationErrors. Usuaully when debugging it won't let you see that property (unless you already had an explicit catch). However, you can open up quick watch and type $exception. Now you should be able to drill in and find that property. You can also just type the following:
不确定具体的截断，但这里有一个提示，当您收到异常提示您检查 EntityValidationErrors 时。通常在调试时它不会让您看到该属性（除非您已经有一个明确的捕获）。但是，您可以打开 quick watch 并输入$exception. 现在您应该能够深入了解并找到该属性。您也可以只输入以下内容：
(System.Data.Entity.Validation.DbEntityValidationException)$exception
回答by Ali
private static string FindLongStrings(object testObject)
{
foreach (PropertyInfo propInfo in testObject.GetType().GetProperties())
{
foreach (ColumnAttribute attribute in propInfo.GetCustomAttributes(typeof(ColumnAttribute), true))
{
if (attribute.DbType.ToLower().Contains("varchar"))
{
string dbType = attribute.DbType.ToLower();
int numberStartIndex = dbType.IndexOf("varchar(") + 8;
int numberEndIndex = dbType.IndexOf(")", numberStartIndex);
string lengthString = dbType.Substring(numberStartIndex, (numberEndIndex - numberStartIndex));
int maxLength = 0;
int.TryParse(lengthString, out maxLength);
string currentValue = (string)propInfo.GetValue(testObject, null);
if (!string.IsNullOrEmpty(currentValue) && currentValue.Length > maxLength && lengthString!="max")
return testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength;
}
}
}
return "";
}
foreach (object insert in dtx.GetChangeSet().Inserts)
{
string result = FindLongStrings(insert);
if (string.IsNullOrEmpty(result) == false)
{
responseBuilder.Append(result);
}
}
If responseBuilder is not empty, then it contains the field name, allowed length and error message.
如果 responseBuilder 不为空，则它包含字段名称、允许的长度和错误消息。
