CLRSQL Aggregate function. LINQ Code works within CLR Function but cannot be deploy within Aggregate

Question

Thanks for reading this,

VS2010 against SQLServer2008 enterprise, developing CLR Aggregate function to calculate the MODE, the function is returning this error:

"Line 1 CREATE AGGREGATE failed because type 'CMode' does not conform to UDAGG specification due to field 'CS$<>9__CachedAnonymousMethodDelegate1'."

the error is produced here:

int mode = list.GroupBy(n => n).
               OrderByDescending(g => g.Count()).
               Select(g => g.Key).FirstOrDefault();

this is the complete code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined , MaxByteSize = 8000)]
public struct CMode : IBinarySerialize
    {
    private List<int> list;
    public void Init()
        {
            this.list = new List<int>();
        }
    public void Accumulate(SqlInt16 Value)
        {
            this.list.Add(Value.Value);
        }
    public void Merge(CMode Group)
        {
            this.list.AddRange(Group.list.ToArray());
        }
    public SqlDecimal Terminate()
        {
        SqlInt16 rtn = new SqlInt16();
        int mode = list.GroupBy(n => n).
               OrderByDescending(g => g.Count()).
               Select(g => g.Key).FirstOrDefault();
        rtn = (SqlInt16)mode;
        return rtn;
        }
    //IBinarySerialize
    public void Read(BinaryReader r)
        {
        int itemCount = r.ReadInt16();
        this.list = new List<int>(itemCount);
        for (int i = 0; i <= itemCount - 1; i++)
            {
            this.list.Add(r.ReadInt16());
            }
        }
    //IBinarySerialize
    public void Write(BinaryWriter w)
        {
        w.Write(this.list.Count);
        foreach (Int16 s in this.list)
            {
            w.Write(s);
            }
        }
    }

Any guidance would be appreciated !!

I am able to run the wished code within a SQLCLR function what verifies that I have all the grants, the dlls are there, etc.. :

u

sing System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Linq.Expressions;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;

public partial class UserDefinedFunctions
    {
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt16 SQLCLR2008MODE()
        {
        List<int> list;
        list = new List<int>();

        list.Add(7);
        list.Add(1);
        list.Add(2);
        list.Add(2);
        list.Add(3);
        list.Add(3);
        list.Add(4);
        list.Add(4);
        list.Add(5);
        list.Add(5);
        list.Add(6);

        int mode = list.GroupBy(n => n).
            OrderByDescending(g => g.Count()).
            Select(g => g.Key).FirstOrDefault();   

        return (Int16)mode;

        }
    };

Looking forward your comments.

luckyluke · Answer 1 · 2011-03-25T01:33:56.437

Try this code. YOur code does not work, because compiler rewrites Your implicit Func delegates to compiled delegates (i strongly recommend using reflector to check this on your own eyes). That would not be bad since it is mainly for performance reasons (to avoid compilation every time this is called) but unfortunately this creates a field which is not serializable and just doe not work well with SQL Server. To avoid that, You have to use expressions and compile them manually. My implemenatation only creates the delegates once in whole call (the init).

All in all I would strongly recommend implementing mode using the HashSet collection with some kind of a grouping or perhaps even SortedHashSet.

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000, IsInvariantToOrder=true, IsNullIfEmpty=true, IsInvariantToNulls=true)]
public struct Mode : IBinarySerialize
{
    public void Init()
    {
        placeholder = new List<int>(10000);
        Expression<Func<int, int>> ass = p => p;
        grouper = ass.Compile();
        Expression<Func<IGrouping<int, int>,int>> ass2 =  q =>  q.Count();
        sorter = ass2.Compile();

    }

    public void Accumulate(SqlInt32 Value)
    {
        placeholder.Add(Value.Value);
    }

    public void Merge(Mode Group)
    {
        placeholder.AddRange(Group.placeholder);
    }

    public SqlInt32 Terminate()
    {
        SqlInt32 result =      placeholder.GroupBy(grouper).OrderByDescending(sorter).FirstOrDefault().Key ?? null;
         placeholder.Clear();
         return result;

    }

    // This is a place-holder member field
    private List<int> placeholder;
    private Func <int, int> grouper;
    private Func<IGrouping<int, int>, int> sorter;

    //IBinarySerialize
    public void Read(BinaryReader r)
    {
        int itemCount = r.ReadInt32();
        this.placeholder = new List<int>(itemCount);
        for (int i = 0; i <= itemCount - 1; i++)
        {
            this.placeholder.Add(r.ReadInt16());
        }
    }
    //IBinarySerialize
    public void Write(BinaryWriter w)
    {
        w.Write(this.placeholder.Count);
        foreach (Int32 s in this.placeholder)
        {
            w.Write(s);
        }
    }


}

If YOu are using MS SQL 2008 the max size for user type is much much bigerr 2GB I believe. You may set size to -1. Only drawback is that for some reason You can't deploy it using Visual studio for some reason. YOu have to do it manually using create/update assembly in Management studio but this should not be a problem. I would still go with c# solution:) — luckyluke, Mar 25 '11 at 01:30

score 0 · Answer 2 · answered Nov 04 '14 at 18:53

I created two UDAs to compute IRR and NPV in Visual Studio 2010 using .NET 3.5. They ran and deployed nicely on SQL 2012. However, when I attempted to deploy them to SQL 2008R2, they failed with the dreaded 'does not conform to UDAGG specification' error. I tried to implement LuckyLuke's approach but had no success. I was finally successful on SQL 2008R2 only after I removed all lambda expressions and LINQ from the UDA classes. Interestingly, I was still able to use these language elements in a class used by my UDAs.

score 0 · Answer 3 · answered Mar 24 '11 at 18:00

In order to calculate the mode, I decided to code it in SQL. It has resulted quite quick even when the query goes against millions of rows. The query calculates the Mode for each day.

That is:

select T_Values.Date batch_date, T_Values.value mode
from       (select a.Date, b.value, COUNT(*) num_items
            from T1 a, T2 b
            where a.Batch = b.Batch
            and b.Product_ref = 100
            and b.Attribute_Id = 1052
            group by a.Date, b.value)
           T_Values,
           (select c.Date, MAX(c.num_items) max_num_items
            from
            (
                select a.Date, b.value, COUNT(*) num_items
                    from T1 a, T2 b
                    where a.Batch = b.Batch
                    and b.Product_ref = 100
                    group by a.Date, b.value
            ) c
            group by c.Date
            ) T_Max
where T_Values.num_items = T_Max.max_num_items
      and T_Values.Date = T_Max.Date
order by T_Values.Date

score 0 · Answer 4 · answered Mar 24 '11 at 18:10

Many thanks Luckyluke !!!!

I got some errors while executing your code, something regarding nulls values. At the end of the process, I coded it with SQL and it does the job very fast. Following the idea of your answer, I have tried to implement the Mediam. It works fine but it has the 8000 parameter size limitation. Anyone interested in learning how to overpass that limitation can go to Chapter 6 of Expert SQL 2005 and implement a dictionary to avoid serialization.

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.IO; using System.Linq; using System.Linq.Expressions;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined
, MaxByteSize = 8000 , IsInvariantToDuplicates = false , IsInvariantToOrder = false)]
public struct CMedian :IBinarySerialize
    {

    private List<int> list;
    public void Init()
        {
        this.list = new List<int>();
        }

    public void Accumulate(SqlInt16 Value)
        {
        this.list.Add(Value.Value);
        }

    public void Merge(CMedian Group)
        {
        this.list.AddRange(Group.list.ToArray());
        }

    public static IQueryable<T> ApplyOrdering<T , U>(IQueryable<T>
query , Expression expression) { Expression> exp = (Expression>)expression; return query.OrderBy(exp); }
    public SqlDecimal Terminate()
        {
        decimal median;
        int halfIndex;
        int numberCount;

        IQueryable<int> myInts = list.AsQueryable<int>();
        Expression<Func<int , int>> myExpression = i => i;
        var sortedNumbers = (ApplyOrdering<int , int>(myInts ,
myExpression));
        numberCount = myInts.Count();
        halfIndex = (numberCount + 1) / 2;

        if ((numberCount % 2) == 0)
            {
            halfIndex = (numberCount) / 2;
            median = ((list.ElementAt(halfIndex) +
list.ElementAt(halfIndex+1)) / 2 ); } else { halfIndex = (numberCount + 1) / 2; median = list.ElementAt(halfIndex); } return (SqlDecimal)median; } //IBinarySerialize public void Read(BinaryReader r) { int itemCount = r.ReadInt16(); this.list = new List(itemCount); for (int i = 0; i <= itemCount - 1; i++) { this.list.Add(r.ReadInt16()); } } //IBinarySerialize public void Write(BinaryWriter w) { try { foreach (Int16 s in this.list) { w.Write(s); } } catch (Exception e) { throw new Exception("Longitud: " + w.BaseStream.Length + "Position: " + w.BaseStream.Position); }
        }
    }

CLRSQL Aggregate function. LINQ Code works within CLR Function but cannot be deploy within Aggregate

4 Answers4