Why does my DBML send varchar(8000) to my database on InsertOnSbumit?

Question

I have a pretty straight forward table in my dbml with a few varchar(20) and 2 varchar(50) columns:

Here is the xml definition of my dbml:

        <?xml version="1.0" encoding="utf-8"?>
<Database Name="AppraisalsLenderX" Class="AppraisalsLenderXDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
      <Connection Mode="AppSettings" ConnectionString="Data Source=emdb.gr-stage.com;Initial Catalog=AppraisalsLenderX;User ID=LenderXSQLUser" SettingsObjectName="GuaranteedRate.Appraisal.Infrastructure.Data.Properties.Settings" SettingsPropertyName="AppraisalsLenderXConnectionString" Provider="System.Data.SqlClient" />
      <Table Name="dbo.LenderXMessage" Member="LenderXMessages">
        <Type Name="LenderXMessage">
          <Column Name="LenderXMessageID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
          <Column Name="EventType" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
          <Column Name="EventID" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
          <Column Name="AppraisalOrderID" Type="System.String" DbType="VarChar(20) NOT NULL" CanBeNull="false" />
          <Column Name="LoanNumber" Type="System.String" DbType="VarChar(20) NOT NULL" CanBeNull="false" />
          <Column Name="OrderStatus" Type="System.String" DbType="VarChar(20) NOT NULL" CanBeNull="false" />
          <Column Name="LoanOfficerEmailAddress" Type="System.String" DbType="VarChar(20)" CanBeNull="true" />
        </Type>
      </Table>
    </Database>

Here is how I am using the entity:

            using (AppraisalsLenderXDataContext db = new AppraisalsLenderXDataContext(ConfigurationManager.ConnectionStrings["AppraisalsLenderX"].ConnectionString))
        {
            LenderXMessage message = new LenderXMessage();
            message.EventType = lxEvent.EventType;
            message.EventID = lxEvent.EventID;
            message.AppraisalOrderID = lxEvent.LXData.AppraisalOrder.AppraisalOrderID;
            message.LoanNumber = lxEvent.LXData.AppraisalOrder.LXAppFile.LoanNumber;
            message.OrderStatus = lxEvent.LXData.AppraisalOrder.OrderStatus;
            message.LoanOfficerEmailAddress = lxEvent.LXData.AppraisalOrder.LoanOfficerInfo.LoanOfficerAccount;
            db.LenderXMessages.InsertOnSubmit(message);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                log.Error($"Error attempting to insert a record into AppraisalsLenderX database ... StackTrace: {ex.ToString()}");
            }
        }
    }

Question: why is it that when I SubmitChanges(), it sends this to the SQL Engine:

    exec sp_executesql N'INSERT INTO [dbo].[LenderXMessage]([LenderXMessageID], [EventType], [EventID], [AppraisalOrderID], [LoanNumber], [OrderStatus], [LoanOfficerEmailAddress])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)',N'@p0 uniqueidentifier,@p1 varchar(8000),@p2 varchar(8000),@p3 varchar(8000),@p4 varchar(8000),@p5 varchar(8000),@p6 varchar(8000)',@p0='00000000-0000-0000-0000-000000000000',@p1='Event.Appraisal.Request.DatesSet',@p2='311AED46-7689-11E8-B8A0-2CEE6AEAE87B',@p3='248422',@p4='161481439',@p5='in_progress',@p6='testloanofficer@aaa.com'

Notice the varchar(8000) ? I am getting this error, of course:

System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.

WHY?

Do your `[dbo].[LenderXMessage]` table really accept varchar(8000)? As you said, your table is set with `few varchar(20) and 2 varchar(50) columns` — MT-FreeHK, Jul 11 '18 at 01:15
`String or binary data would be truncated.` Ignore the 8000. It is not the cause of your issue. The cause of that error is that one of the values you are passing is too long to fit in the database. — mjwills, Jul 11 '18 at 01:17
I think you are right @mjwills, I trimmed my strings and now I don't get the error. I will answer my own question, but, thank you! — ElMatador, Jul 11 '18 at 19:37

score -1 · Answer 1 · answered Jul 11 '18 at 19:39

I had to trim the strings that come from an xml specification:

[XmlRoot("event")]
public class LenderXEvent
{
    [XmlElement("type")]
    public string EventType { get; set; }
    [XmlElement("id")]
    public string EventID { get; set; }
    [XmlElement("data")]
    public LenderXData LXData { get; set; }
}

That I was sending to the database. It now works. Here is the updated code:

    using (AppraisalsLenderXDataContext db = new AppraisalsLenderXDataContext(ConfigurationManager.ConnectionStrings["AppraisalsLenderX"].ConnectionString))
{
    LenderXMessage message = new LenderXMessage();
    message.EventType = lxEvent.EventType.Substring(0, lxEvent.EventType.Length);
    message.EventID = lxEvent.EventID.Substring(0, lxEvent.EventID.Length);
    message.AppraisalOrderID = lxEvent.LXData.AppraisalOrder.AppraisalOrderID.Substring(0, lxEvent.LXData.AppraisalOrder.AppraisalOrderID.Length);
    message.LoanNumber = lxEvent.LXData.AppraisalOrder.LXAppFile.LoanNumber.Substring(0, lxEvent.LXData.AppraisalOrder.LXAppFile.LoanNumber.Length);
    message.OrderStatus = lxEvent.LXData.AppraisalOrder.OrderStatus.Substring(0, lxEvent.LXData.AppraisalOrder.OrderStatus.Length);
    message.LoanOfficerEmailAddress = lxEvent.LXData.AppraisalOrder.LoanOfficerInfo.LoanOfficerAccount.Substring(0, lxEvent.LXData.AppraisalOrder.LoanOfficerInfo.LoanOfficerAccount.Length);
    message.DateCreated = DateTime.Now;
    db.LenderXMessages.InsertOnSubmit(message);
    try
    {
        db.SubmitChanges();
    }
    catch (Exception ex)
    {
        log.Error($"Error attempting to insert a record into AppraisalsLenderX database ... StackTrace: {ex.ToString()}");
    }
}

Thanks to @mjwillis for the answer.

`lxEvent.LXData.AppraisalOrder.OrderStatus.Substring(0, lxEvent.LXData.AppraisalOrder.OrderStatus.Length);` is the same as `lxEvent.LXData.AppraisalOrder.OrderStatus` (since if the length is 500 you are asking for the first 500 characters, which is the whole string). It is not possible for that change to help. — mjwills, Jul 12 '18 at 01:30
It literally can't have. Something else must have changed. Or you just started sending smaller data unrelated to that change. — mjwills, Jul 12 '18 at 21:29

Why does my DBML send varchar(8000) to my database on InsertOnSbumit?

1 Answers1