Sql – Column.DbType affecting runtime behavior

linq-to-sqlnet

According to the MSDN docs, the DbType property/attribute of a Column type/element is only used for database creation.

Yet, today, when trying to submit data to an IMAGE column on a SQLCE database (not sure if only on CE), I got an exception of 'Data truncated to 8000 bytes'. This was due to the DbType still being defined as VARBINARY(MAX) which SQLCE does not support. Changing the type to IMAGE in the DbType fixes the issue.

So what other surprises does Linq2SQL attributes hold in store? Is this a bug or intended? Should I report it to MS?

UPDATE

After getting the answer from Guffa, I tested it, but it seems for NVARCHAR(10) adding a 11 char length string causes a SQL exception, and not Linq2SQL one.

The data was truncated while converting from one data type to another. 
     [ Name of function(if known) =  ]
A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' 
     occurred in System.Data.SqlServerCe.dll

Best Answer

The DbType is only required if you are going to create a table, but that doesn't mean that it's ignored the rest of the time.

If you for example define a VarChar column with the size 100, you will get an exception if you send a string that is longer than 100 characters even if the field in the database actually could accomodate the string.

The documentation says that you shouldn't specify the DbType if it's not needed, as the data type is inferred from the value that you use. However, there might be some situations where you don't want it to use the DbType that is inferred.

Related Solutions

Sql – What’s the advantage of mapping a binary column to System.Data.Linq.Binary instead of byte[]

I found this mention here:

The Binary type exists because LINQ to SQL cannot track changes made to a byte[]. It only tracks changes made to a property when you assign a new byte[] to it. The Binary type is an immutable version of byte[], like the String type is an immutable version of char[]. Since you cannot change the contents of a Binary type, the only way to modify it is to assign your property a new instance.

Sql – How to force linq to sql to use the correct data type for sql parameters

You can get the command and reset the types of the parameters on it directly (to ansi-string in your case).

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx

Then you might call ExecuteReader on that command, yielding a DbDataReader. You can hand this DbDataReader to the Translate method of your datacontext, and it will give you the IEnumerable<T> that you'd expect from linq.

http://msdn.microsoft.com/en-us/library/bb534213.aspx

The performance problem is caused by the query parameter having a different type than the index selected by the query optimizer. What happens next is that the entire index is converted into the type of the parameter. This is done each time the query is issued - the conversion doesn't hang around for later querying.

I usually see this behavior when sending a collection of strings into the database:

  //this query will get correct parameter type
db.Customers.Where(c => c.Name == "Bob")
  //this query can get incorrect parameter type
List<string> names = new List<string>(){"Amy", "Bob"};
db.Customers.Where(c => names.Contains(c.Name));

Best Answer

Related Solutions

Sql – What’s the advantage of mapping a binary column to System.Data.Linq.Binary instead of byte[]

Sql – How to force linq to sql to use the correct data type for sql parameters

Related Topic