"String or Binary Data Would be Truncated" Error when using Linq to SQL

Question

I'm using Linq to SQL to insert data into a table, but get an SqlException saying that "String or binary data would be truncated". However, the table only consists of two GUIDs, which have an exact size; and a nvarchar(max), and my strings are less than 20 characters.

I tried inserting rows by hand, using SqlCmd, and this worked fine. I also verified using SQL Profiler that nothing gets written tot the trace, indicating the problem is coming from the Linq to SQL framework.

The table definition is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Results](
    [GroupID] [uniqueidentifier] NOT NULL,
    [ItemID] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    PRIMARY KEY (GroupID, ItemID)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

And the Linq to SQL mapping (from the DBML file) is:

<Table Name="dbo.Results" Member="Results">
  <Type Name="Result">
    <Column Name="GroupID" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    <Column Name="ItemID" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
    <Column Name="Name" Type="System.String" DbType="NVarChar(MAX) NOT NULL" CanBeNull="false" />
  </Type>
</Table>

And in my C# code, this gives the exception:

db.Results.InsertOnSubmit(new Result
{
    GroupID = Guid.NewGuid(),
    ItemID = Guid.NewGuid(),
    Name = "test"
});
db.SubmitChanges();

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

I found this which is a question about nvarchar(max) being truncated. Possibly the answer to that issue will fix yours. It might be misunderstanding but I hope it helps. It's a different issue but both are about nvarchar(max) being truncating so...

nvarchar(max) still being truncated

edited May 23 '17 at 12:23

Community

1
1

answered Jan 24 '16 at 10:27

Moses

78
6

score 0 · Accepted Answer · answered Jan 24 '16 at 12:01

I still don't understand exactly what's going on here; looks like a bad Linq to SQL mapping, or (unlikely) a bug in the framework.

But to get around this, I replaced:

db.Results.InsertOnSubmit(new Result
{
    GroupID = Guid.NewGuid(),
    ItemID = Guid.NewGuid(),
    Name = "test"
});
db.SubmitChanges();

With:

string cmd = string.Format("insert into Results values('{0}', '{1}', '{2}')", Guid.NewGuid(), Guid.NewGuid(), "test")
db.ExecuteCommand(cmd);

And this works (not just with the test string, but with the actual data).

Still curious to understand what's causing the exception with the original code...

"String or Binary Data Would be Truncated" Error when using Linq to SQL

2 Answers2