title: Code first Entity Framework 6.1 Custom Aggregate Function
body: <p>I have a custom CLR Aggregate function on SQL Server to calculate percentiles. Is it possible to call my custom aggregate function through Entity Framework? How is the mapping configured to allow this?</p>
  
  <p>I have tried using codefirstfunctions similar to what is described on <a href="https://stackoverflow.com/questions/29517627/entity-framework-6-code-first-custom-functions">Entity Framework 6 Code First Custom Functions</a>, however the functions seem to only be allowed to take scaler parameters, where my function is an aggregate function so will need to take a list of items (similar to how Sum, Averagg and Count work).</p>
  
  <p>The Aggregate functions has the following signature, taking in the value we want the median from and the percentile (50 is median, 25 lower quartile, 75 upper quartile)</p>
  
  <pre><code>CREATE AGGREGATE [dbo].[Percentile]
  (@value [float], @tile [smallint])
  RETURNS[float]
  EXTERNAL NAME [SqlFuncs].[Percentile]
  GO
  </code></pre>
  
  <p>I have tried adding a DbFunctionAttribute, but not entirely sure how to hook it up to entity framework store model using code first.</p>
  
  <pre><code>[DbFunction("SqlServer", "Percentile")]
  
  public static double? Percentile(IEnumerable&lt;int?&gt; arg, int tile)
  {
      throw new NotSupportedException("Direct calls are not supported.");
  }
  </code></pre>
  
  <hr>
  
  <p>What I am looking for is to be able to write something like</p>
  
  <pre><code>paymentsTable
      .GroupBy(x=&gt;x.CustomerId)
      .Select(new{
              Median = MyDbContext.Percentile(x.Select(g=&gt;g.Amount), 50)
      });
  </code></pre>
  
  <p>Which will map to  SQL like</p>
  
  <pre><code>SELECT [dbo].[Percentile](Amount, 50) as Median
  FROM Payments
  GROUP BY CustomerId
  </code></pre>