CODEWITHSUNDEEP
c#sql-serverlinq-to-sql
Alexander Ponomarev
Alexander Ponomarev

Reputation: 282

How can I force varchar length in Linq To SQL

When in Linq To SQL I use 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 a parameter type.

So I have to change the parameter type of function to avoid SQL Server error.

Can I force Linq To SQL not to ignore length that I pass?

P.S. There is the same issue with nvarchar(4000).

Upvotes: 9

Views: 1796

Answers (1)

Shane Neuville
Shane Neuville

Reputation: 2177

Looking through decompiled code it doesn't really look like it pays attention to that attribute when composing the query and I don't really see a path that would let you set it

This looks to be the file that determines the parameter size to use

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

This part inside of InitializeParameter initializes the Size 

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

Then following the execute path is just set to 8000 for varchars and 4000 for nvarchars and doesn't really ever look at that 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 stepped through the code while executing a function as well to watch its execution path... It also doesn't seem like there are any useful hooks to modify before executing.. The SqlProvider has nothing useful to override or hook into either....

Upvotes: 2

Related Questions