Troubles with shared state and anonymous delegates in SQLCLR

Posted by Chris on October 04, 2006

Earlier today I set out to create “a SQLCLR function for splitting a comma-separated string including ranges”:/articles/2006/01/27/splitting-a-comma-separated-string-with-ranges-included/ (such as “2,5,7-9,13”) into a set of values. I encountered some problems that I think is valuable to document here. The code below shows what I initially wrote:

SqlFunction(
  DataAccess = DataAccessKind.None,
  SystemDataAccess = SystemDataAccessKind.None,
  IsDeterministic = true,
  IsPrecise = true,
  FillRowMethodName = "FillRow")]
public static IEnumerable SplitWithRanges(SqlString input) {
  // First expand ranges
  Regex rangeExpander = new Regex(@"(?\d+)\-(?\d+)");

  string expandedRanges = rangeExpander.Replace(
    input.Value,
    delegate(Match match) {
      int startOfRange = Int32.Parse(
        match.Groups["RangeStart"].Value);
      int endOfRange = Int32.Parse(
        match.Groups["RangeEnd"].Value);
      string[] values = new string[endOfRange - startOfRange + 1];
      for(int i = 0; i < values.Length; i++) {
        values[i] = (startOfRange + i).ToString();
      }
      return String.Join(",", values);
    }
  );

  // Now split the string containing the comma-separated values
  string[] splittedValues = expandedRanges.Split(
    new char[] { ',' },
    StringSplitOptions.None);

  return splittedValues;
}

public static void FillRow(Object obj, out SqlString tuple) {
  tuple = new SqlString((String)obj);
}

However, when I tried to add this assembly to SQL Server I ran into problems. The CREATE ASSEMBLY statement failed with the following message:

@Msg 6212, Level 16, State 1, Line 8@

@CREATE ASSEMBLY failed because method 'SplitWithRanges' on type 'Functions' in safe assembly 'Splitting' is storing to a static field. Storing to a static field is not allowed in safe assemblies.@

I know that assemblies must be registered in UNSAFE to use shared state. The reason is that if an executing thread suffers a thread abort exception (for instance due to a stack overflow or out of memory exception being thrown by something it does) and there is any shared state in the application domain where it is executing, then the entire application domain is unloaded. It does not matter whether or not this specific thread was using the shared state, as a safety precaution the application domain is unloaded. Therefore SQL Server tries to restrict you from using shared state in SQLCLR.

But where does the C# code above use any shared state? A little reflecting with Lutz Roeder’s Reflector gave me the answer. The row with the anonymous delegate above is actually compiled into IL that matches C# code similar to this:

if (Functions.<>9__CachedAnonymousMethodDelegate1 == null) {
  Functions.<>9__CachedAnonymousMethodDelegate1 = new MatchEvaluator(
    Functions.b__0);
}
string text1 = regex1.Replace(
  input.Value,
  Functions.<>9__CachedAnonymousMethodDelegate1);

Functions is the name of the class containing my code, so what this does is use a static field for storing the delegate. The static field is defined as below:

[CompilerGenerated]
private static MatchEvaluator <>9__CachedAnonymousMethodDelegate1;

So there we have the shared state. To get around this there are two solutions. I can either define a function to use as the delegate, like this:

private static String ExpandRanges(Match match) {
  int startOfRange = Int32.Parse(
    match.Groups["RangeStart"].Value);
  int endOfRange = Int32.Parse(
    match.Groups["RangeEnd"].Value);
  string[] values = new string[endOfRange - startOfRange + 1];
  for(int i = 0; i < values.Length; i++) {
    values[i] = (startOfRange + i).ToString();
  }
  return String.Join(",", values);
}

Then I just change the row calling the delegate to this:

string expandedRanges = rangeExpander.Replace(
input.Value,
new MatchEvaluator(Functions.ExpandRanges));

The second “solution” is to use a variable local to the function SplitWithRanges inside the anonymous delegate. The reason this works is that the compiler now generates a private nested class with a function that is used as the delegate. So, if I for instance declare a variable @bool foo = true;@ outside the delegate and then simply do @foo = foo;@ in the code of the anonymous delegate then the compiler will actually create this:

[SqlFunction(
  DataAccess=DataAccessKind.None,
  SystemDataAccess=SystemDataAccessKind.None,
  IsDeterministic=true,
  IsPrecise=true,
  FillRowMethodName="FillRow")]
public static IEnumerable SplitWithRanges(SqlString input) {
  Functions.<>c__DisplayClass1 class1 = new Functions.<>c__DisplayClass1();
  Regex regex1 = new Regex(
    @"(?\d+)\-(?\d+)");
  class1.foo = true;
  string text1 = regex1.Replace(
    input.Value,
    new MatchEvaluator(class1.b__0));
  char[] chArray1 = new char[] { ',' } ;
  return text1.Split(chArray1, StringSplitOptions.None);
}

As I mentioned above, class1 is a private class defined (by the compiler) in my class Functions. So under the covers this solution is very similar to the previous one. It is just a question of where the function is placed. Although I love anonymous delegates, in this case I went with defining the function myself and using that for the delegate.

Trackbacks

Trackbacks are closed.

  • Doug

    thank you for this article -- 4 years later it is still helpful.

  • mausch

    Thanks! you just saved me a lot of reflecting/debugging/generally unpleasant time :-)

blog comments powered by Disqus