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

Question

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

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?

c#

sql-server-2008

dbml

asked on Stack Overflow Jul 11, 2018 by

ElMatador

1 Answer

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.

answered on Stack Overflow Jul 11, 2018 by

ElMatador

User contributions licensed under CC BY-SA 3.0