How can I force varchar length in Linq To SQL

Question

When in Linq To SQL I use a user-defined function like this

[Function(Name = "udf_find_a", IsComposable = true)] public IQueryable<A> FindA([Parameter(DbType = "varchar(100)")] string keywords) { return CreateMethodCallQuery<A>(this, ((MethodInfo) (MethodBase.GetCurrentMethod())), keywords); } 

the resulting query always contains varchar (8000) as the parameter type.

Therefore, I need to change the type of function parameter to avoid SQL Server error.

Can I make Linq To SQL ignore the missing length?

PS There is the same problem with nvarchar (4000).

Answer 1

Looking through the decompiled code, it doesn't really look like it pays attention to this attribute when compiling a request, and I really don't see a path that would allow you to set it

It looks like a file determining the size of the parameter to use

https://github.com/Microsoft/referencesource/blob/master/System.Data.Linq/SqlClient/SqlTypeSystemProvider.cs

This part inside InitializeParameter initializes the size

  int? determinedSize = DetermineParameterSize(sqlType, parameter); if (determinedSize.HasValue) { parameter.Size = determinedSize.Value; } 

Then, after the execution path, only 8000 for varchars and 4000 for nvarchars and actually never look at this attribute

  internal virtual int? DetermineParameterSize(SqlType declaredType, DbParameter parameter) { // Output parameters and input-parameters of a fixed-size should be specifically set if value fits. bool isInputParameter = parameter.Direction == ParameterDirection.Input; if (!isInputParameter || declaredType.IsFixedSize) { if (declaredType.Size.HasValue && parameter.Size <= declaredType.Size || declaredType.IsLargeType) { return declaredType.Size.Value; } } // Preserve existing provider & server-driven behaviour for all other cases. return null; } protected int? GetLargestDeclarableSize(SqlType declaredType) { switch (declaredType.SqlDbType) { case SqlDbType.Image: case SqlDbType.Binary: case SqlDbType.VarChar: return 8000; case SqlDbType.NVarChar: return 4000; default: return null; } } internal virtual int? DetermineParameterSize(SqlType declaredType, DbParameter parameter) { // Output parameters and input-parameters of a fixed-size should be specifically set if value fits. bool isInputParameter = parameter.Direction == ParameterDirection.Input; if (!isInputParameter || declaredType.IsFixedSize) { if (declaredType.Size.HasValue && parameter.Size <= declaredType.Size || declaredType.IsLargeType) { return declaredType.Size.Value; } } // Preserve existing provider & server-driven behaviour for all other cases. return null; } 

this statement

 if (!isInputParameter || declaredType.IsFixedSize) 

IsFixedSize returns false for varchar and nvarchar, which you can see here

 internal override bool IsFixedSize { get { switch (this.sqlDbType) { case SqlDbType.NText: case SqlDbType.Text: case SqlDbType.NVarChar: case SqlDbType.VarChar: case SqlDbType.Image: case SqlDbType.VarBinary: case SqlDbType.Xml: return false; default: return true; } } } 

I source went through the code during the execution of the function, as well as to track its path of execution ... Also, it does not seem that there are any useful jumpers for changes before execution. SqlProvider has nothing useful to redefine or connect to ........