title: VS 2012 SSDT build CLR with IEnumerable failing on generated syntax
body: <p>I'm attempting to reuse some code found <a href="http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/" rel="nofollow">here</a>.  After adding a new using the c# (shown below) seems to be fine.</p>
  
  <p>When I go to publish the database, however, the code generator does not seem to recognize the IEnumerable type and ends up producing an error. The actual error (Incorrect Syntax near AS) from the generated code below is obvious, so my question is <strong>how can I get SSDT to generate the correct code and avoid the cause of the error?</strong> (I assume that I can add the CLR manually, however, I would prefer to do everything from SSDT)</p>
  
  <p>Currently Generates:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[RegExMatches] (@sourceString [nvarchar](4000), @pattern [nvarchar](4000))
  RETURNS /* Error: Unsupported type. */
  AS EXTERNAL NAME [CampaignStrategyStaging].[SQLRegEx].[RegExMatches];
  </code></pre>
  
  <p>Should Generate something like:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[RegExMatches] (@sourceString [nvarchar](4000), @pattern [nvarchar](4000))
  RETURNS  TABLE (
      [rowId] int, --RowId each row as it`s ID
      [matchId] int, --ID of particular match (starts from 1)
      [groupId] int, --ID of particular group in RegEx match (GroupID = 0) represents a complete match
      [value] nvarchar(4000) --value of the group
  ) WITH EXECUTE AS CALLER
  AS EXTERNAL NAME [CampaignStrategyStaging].[SQLRegEx].[RegExMatches];
  </code></pre>
  
  <p>The C# for the CLR:</p>
  
  <pre><code>using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using Microsoft.SqlServer.Server;
  using System.Text.RegularExpressions;
  using System.Collections;
  
  public class SQLRegEx
  {
      private class RegExRow
      {
      /// &lt;summary&gt;
      /// Private class for passing matches of the RegExMatches to the FillRow method
      /// &lt;/summary&gt;
      /// &lt;param name=”rowId”&gt;ID of the Row&lt;/param&gt;
      /// &lt;param name=”matchId”&gt;ID of the Match&lt;/param&gt;
      /// &lt;param name=”groupID”&gt;ID of the Group within the Match&lt;/param&gt;
      /// &lt;param name=”value”&gt;Value of the particular group&lt;/param&gt;
      public RegExRow(int rowId, int matchId, int groupID, string value)
      {
          RowId = rowId;
          MatchId = matchId;
          GroupID = groupID;
          Value = value;
      }
  
      public int RowId;
      public int MatchId;
      public int GroupID;
      public string Value;
  }
  
  /// &lt;summary&gt;
  /// Applies Regular Expression on the Source string and returns value of particular group from withing a specified match
  /// &lt;/summary&gt;
  /// &lt;param name=”sourceString”&gt;Source string on which the regular expression should be applied&lt;/param&gt;
  /// &lt;param name=”pattern”&gt;Regular Expression pattern&lt;/param&gt;
  /// &lt;param name=”matchId”&gt;ID of the Match to be returned 1 inex-based&lt;/param&gt;
  /// &lt;param name=”groupId”&gt;ID of the group from within a match to return. GroupID 0 returns complete match&lt;/param&gt;
  /// &lt;returns&gt;Value of the Group from within a Match&lt;/returns&gt;
  [SqlFunction(IsDeterministic=true)]
  public static SqlChars RegExMatch(string sourceString, string pattern, int matchId, int groupId)
  {
      Match m = null;
      Regex r = new Regex(pattern, RegexOptions.Compiled);
  
      if (matchId == 1)
      {
          m = r.Match(sourceString);
      }
      else if (matchId &gt; 1)
      {
          MatchCollection mc = r.Matches(sourceString);
  
          if (mc!=null &amp;&amp; mc.Count &gt; matchId-1)
          {
              m = mc[matchId-1];
          }
          else
          {
              m= null;
          }
  
          ///m = mc != null &amp;&amp; mc.Count &gt; matchId – 1 ? mc[matchId - 1] : null;
      }
  
      return m != null &amp;&amp; m.Groups.Count &gt; groupId ? new SqlChars(m.Groups[groupId].Value) : SqlChars.Null;
  }
  
  /// &lt;summary&gt;
  /// Applies Regular Expression o the Source strings and return all matches and groups
  /// &lt;/summary&gt;
  /// &lt;param name=”sourceString”&gt;Source string on which the regular expression should be applied&lt;/param&gt;
  /// &lt;param name=”pattern”&gt;Regular Expression pattern&lt;/param&gt;
  /// &lt;returns&gt;Returns list of RegExRows representing the group value&lt;/returns&gt;
  [SqlFunction(FillRowMethodName = "FillRegExRow")]
  public static IEnumerable RegExMatches(string sourceString, string pattern) 
  {
      Regex r = new Regex(pattern, RegexOptions.Compiled);
      int rowId = 0;
      int matchId = 0;
      foreach (Match m in r.Matches(sourceString))
      {
          matchId++;
          for (int i = 0; i &lt; m.Groups.Count; i++)
          {
              yield return new RegExRow(++rowId, matchId, i, m.Groups[i].Value);
          }
      }
  }
  
  /// &lt;summary&gt;
  /// FillRow method to populate the output table
  /// &lt;/summary&gt;
  /// &lt;param name=”obj”&gt;RegExRow passed as object&lt;/param&gt;
  /// &lt;param name=”rowId”&gt;ID or the returned row&lt;/param&gt;
  /// &lt;param name=”matchId”&gt;ID of returned Match&lt;/param&gt;
  /// &lt;param name=”groupID”&gt;ID of group in the Match&lt;/param&gt;
  /// &lt;param name=”value”&gt;Value of the Group&lt;/param&gt;
  public static void FillRegExRow(Object obj, out int rowId, out int matchId, out int groupID, out SqlChars value)
  {
      RegExRow r = (RegExRow)obj;
      rowId = r.RowId;
      matchId = r.MatchId;
      groupID = r.GroupID;
      value = new SqlChars(r.Value);
  }
  </code></pre>
  
  <p>}</p>