Controlling T-SQL output from LINQ to SQL
-
Does anyone know if it's possible to control the T-SQL output from LINQ to SQL in any way? Specifically, I'd like to control the definition of the parameters created for sp_executesql when using variable-length data types (varchar, decimal, numeric). VS 2008 defines the data types correctly by declaring the size when I drag and drop a table into the designer (O/R mapper?) for my dbml file. But that value isn't considered when the sp_executesql statement is generated. So the size of the gernated parameter is the size of the value passed in (ex. "test@test.com" becomes nvarchar(13) regardless of the size of the column in the database). Thanks, Mark
-
Does anyone know if it's possible to control the T-SQL output from LINQ to SQL in any way? Specifically, I'd like to control the definition of the parameters created for sp_executesql when using variable-length data types (varchar, decimal, numeric). VS 2008 defines the data types correctly by declaring the size when I drag and drop a table into the designer (O/R mapper?) for my dbml file. But that value isn't considered when the sp_executesql statement is generated. So the size of the gernated parameter is the size of the value passed in (ex. "test@test.com" becomes nvarchar(13) regardless of the size of the column in the database). Thanks, Mark
Ok, I've found one way to do this but it's hardly elegant and it doesn't allow you to take advantage of delayed execution: DataContext.GetCommand returns System.Data.Common.DbCommand which allows you to access the Parameters collection where you can set the size. If you're using SQL Server you need to set Precision you'll have to cast the parameter to a SqlParameter. After you've updated the parameter you can call ExecuteReader (don't forget to make sure the connection is open). Then use the DataContext.Translate. The problem is this doesn't allow for delayed execution because you can't update the actual command used by the query variable.
-
Ok, I've found one way to do this but it's hardly elegant and it doesn't allow you to take advantage of delayed execution: DataContext.GetCommand returns System.Data.Common.DbCommand which allows you to access the Parameters collection where you can set the size. If you're using SQL Server you need to set Precision you'll have to cast the parameter to a SqlParameter. After you've updated the parameter you can call ExecuteReader (don't forget to make sure the connection is open). Then use the DataContext.Translate. The problem is this doesn't allow for delayed execution because you can't update the actual command used by the query variable.
Just as an update, I've found a way to inject my workaround (read: hack) into the process in a way that is transparent to the consumer of my class. I'm going to create a class which wraps System.Data.Linq.Table and create a custom implementation of the GetEnumerator() and GetEnumerator() methods which will parse the expression tree (haven't proved this part of the concept yet) and determine the parameter mappings, then I'll be able to reflect over T (I'll have to cache the result of this for performance) to determine the correct value for DbParameter.Size. When I've completed the ability to parse the expression tree I'll write an article on everything I've learned. Wish me luck! Mark