title: Using the GLOB function in LINQ to Entities
body: <p>I need SQLite's <code>glob</code> function in a (C#) method which has to return <code>Expression&lt;Func&lt;RandomEntity, bool&gt;&gt;</code> -- and I need <code>glob</code> because only then the index is used (already checked using <code>EXPLAIN QUERY PLAN [..]</code>).</p>
  
  <p>So I added the following function mapping to <code>&lt;edmx:StorageModels&gt;&lt;Schema&gt;</code> (SSDL):</p>
  
  <pre><code>    &lt;Function Name="glob" Aggregate="false" BuiltIn="true" NiladicFunction="false" IsComposable="true" ReturnType="bit"&gt;
          &lt;Parameter Name="pattern" Mode="In" Type="nvarchar"/&gt;
          &lt;Parameter Name="target" Mode="In" Type="nvarchar"/&gt;
      &lt;/Function&gt;
  </code></pre>
  
  <p>and a C# stub method:</p>
  
  <pre><code>public static class SQLiteFunctions
  {
      [DbFunction("Model.Store", "glob")]
      public static bool Glob(string pattern, string target)
      {
          throw new NotImplementedException("Only exists for IQueryable/Expression&lt;Func&lt;T,bool&gt;&gt;!");
      }
  }
  </code></pre>
  
  <p>and the usage (just a sample producing tiny SQL):</p>
  
  <pre><code>var count = context.Users.Count(u =&gt; SQLiteFunctions.Glob("admin*", u.Name));
  </code></pre>
  
  <p>While this works the resulting SQL uses a "silly" <code>= 1</code> comparison since the model only knows the datatype "bit" (which is 0 or 1) and I found no real boolean type although the native glob function seems to be true boolean:</p>
  
  <pre><code>SELECT 
  [GroupBy1].[A1] AS [C1]
  FROM ( SELECT 
      Count([Filter1].[A1]) AS [A1]
      FROM ( SELECT 
          1 AS [A1]
          FROM [Users] AS [Extent1]
          WHERE (glob('admin*', [Extent1].[Name])) = 1
      )  AS [Filter1]
  )  AS [GroupBy1]
  </code></pre>
  
  <p><strong>The problem</strong>: This way (with "= 1") SQLite doesn't use the index and the query is badly slow. If I remove the 3 characters "= 1" the query becomes fast and also the query plan changes from "SCAN TABLE" to "SEARCH TABLE USING INDEX".</p>
  
  <p>Any ideas how to make the function a true boolean function?</p>
  
  <hr>
  
  <p>Addition:</p>
  
  <p>I also tried using the CSDL section ():</p>
  
  <pre><code>    &lt;Function Name="GlobMatch" ReturnType="Edm.Boolean"&gt;
          &lt;Parameter Name="globPattern" Type="Edm.String" /&gt;
          &lt;Parameter Name="target" Type="Edm.String" /&gt;
          &lt;DefiningExpression&gt; glob(globPattern, target) &lt;/DefiningExpression&gt;
      &lt;/Function&gt;
  </code></pre>
  
  <p>and</p>
  
  <pre><code>    [DbFunction("DoPiMo", "GlobMatch")]
      public static bool Glob2(string globPattern, string target)
      {
          throw new NotImplementedException("Only exists for IQueryable/Expression&lt;Func&lt;T,bool&gt;&gt;!");
      }
  </code></pre>
  
  <p>but this only produces a runtime error saying "glob" is not defined/known.</p>