SQL Server AGGREGATE function C#

Question

I am trying to create an aggregate function in C# that will basically find the most occurrence of a value in a column

[Serializable]
[SqlUserDefinedAggregate(Format.Native, Name = "Norm")]
public struct Norm
{
    private SerializableDictionary<SqlInt64, int> Pair;

    public void Init()
    {
        //Initialization
        Pair = new SerializableDictionary<SqlInt64, int>();
    }

    public void Accumulate(SqlInt64 Value)
    {
     //Code removed for easier reading
    }

    public void Merge(Norm Group)
    {
     //Code removed for easier reading
    }
}

And my SerializableDictionary I have the following code

[XmlRoot("dictionary")]
public class SerializableDictionary<TKey, TValue>
    : Dictionary<TKey, TValue>, IXmlSerializable
{
    #region IXmlSerializable Members
    public System.Xml.Schema.XmlSchema GetSchema()
    {
        return null;
    }

    public void ReadXml(System.Xml.XmlReader reader)
    {
        XmlSerializer keySerializer = new XmlSerializer(typeof(TKey));
        XmlSerializer valueSerializer = new XmlSerializer(typeof(TValue));

        bool wasEmpty = reader.IsEmptyElement;
        reader.Read();

        if (wasEmpty)
            return;

        while (reader.NodeType != System.Xml.XmlNodeType.EndElement)
        {
            reader.ReadStartElement("item");

            reader.ReadStartElement("key");
            TKey key = (TKey)keySerializer.Deserialize(reader);
            reader.ReadEndElement();

            reader.ReadStartElement("value");
            TValue value = (TValue)valueSerializer.Deserialize(reader);
            reader.ReadEndElement();

            this.Add(key, value);

            reader.ReadEndElement();
            reader.MoveToContent();
        }
        reader.ReadEndElement();
    }

    public void WriteXml(System.Xml.XmlWriter writer)
    {
        XmlSerializer keySerializer = new XmlSerializer(typeof(TKey));
        XmlSerializer valueSerializer = new XmlSerializer(typeof(TValue));

        foreach (TKey key in this.Keys)
        {
            writer.WriteStartElement("item");

            writer.WriteStartElement("key");
            keySerializer.Serialize(writer, key);
            writer.WriteEndElement();

            writer.WriteStartElement("value");
            TValue value = this[key];
            valueSerializer.Serialize(writer, value);
            writer.WriteEndElement();

            writer.WriteEndElement();
        }
    }
    #endregion
}

When I import it into SQL Server the Assembly loads but when I try to execute the following line in SQL Server I get an error

GO
CREATE AGGREGATE Aggregates.Norm (@value BIGINT) RETURNS BIGINT
EXTERNAL NAME WFAggerates.Norm;

The Error I am getting is

Msg 6222, Level 16, State 1, Line 15
Type "WFAggerates.Norm" is marked for native serialization, but field "Pair" of type "WFAggerates.Norm" is not valid for native serialization.

I have already seen these posts but they didn't help me

SQL Server CLR Aggregate: Serialize a Dictionary?

C# and SQL Server 2008 CLR Serialization Problem

Do you have to use CLR for this? You can do it with a query. — Mike Miller, Apr 29 '16 at 12:38
I know I can do it in a querry, but I already have a querry that has SUM(xx) AVG(xx) etc now I want one that is NORM(xx) without an extra querry — Donald Jansen, Apr 29 '16 at 12:39

score 1 · Accepted Answer · answered Apr 29 '16 at 12:48

1

Instead of using Format.Native which only supports Blittable types use Format.UserDefined. You will then have to implement IBinarySerialize and handle the serialization yourself.

answered Apr 29 '16 at 12:48

Magnus

45,362
8
80
118

Thanx will try it, if I manage to get it to work I will Accept this answer – Donald Jansen Apr 29 '16 at 13:06
I think it will work but im getting `The size (0) for "WFAggerates.Norm" is not in the valid range. Size must be -1 or a number between 1 and 8000.` – Donald Jansen Apr 29 '16 at 13:28

score 1 · Answer 2 · answered Apr 29 '16 at 16:05

This is something I used ten years ago. You may not need the MaxByteSize flag any more! Although this calcualtes the median, I think it should be easy enough to adapt it to calculate the mode.

using System; /* contains: Serializable */
using System.Collections; /* contains: ArrayList */
using System.Collections.Generic; /* contains: definition of List<> */
using System.Data.SqlTypes; /* contains: definition of SqlDouble (FLOAT in T-SQL) */
using System.IO; /* contains: BinaryReader and BinaryWriter */
using System.IO.Compression; /* contains: DeflateStream and InflateStream */
using Microsoft.SqlServer.Server; /* contains: SqlUserDefinedAggregate */

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, 
IsInvariantToNulls = true, 
IsInvariantToDuplicates = false, 
IsInvariantToOrder = true, 
IsNullIfEmpty = true, 
MaxByteSize = 8000, 
Name = "Median" 
)
]
public class Agg_Median : IBinarySerialize 
{
    private List<Double> ValueList; 

    public void Init()
    {
        this.ValueList = new List<Double>(); 
    }

    public void Accumulate(SqlDouble value) 
    {
        if (value.IsNull)
        {
            return; 
        }

        this.ValueList.Add(value.Value); 
    }

    public void Merge(Agg_Median other) /* combine data from parallel     operations */
    {
        this.ValueList.AddRange(other.ValueList);
    }

    public SqlDouble Terminate() /* this method is called at the end to     return the result */
    {
        double __Median = 0.0D; /* local variable to hold and calculate     final result */
        int __Count = 0; /* local variable to hold number of values in list     */

        this.ValueList.Sort(); /* arrange the list of values in order so we     can determine
                    * middle value(s)
                    */

        __Count = this.ValueList.Count; /* we need to know how many in order     to find the middle */

        /* if there is at least one value we will calculate the Median, else     we return a NULL FLOAT
         */
        if (__Count > 0)
        {
            if (__Count % 2 == 0) /* if even number of values, middle will     be two values */
            {
                __Median = (
                    ((double)this.ValueList[(__Count / 2) - 1] +
                        (double)this.ValueList[(__Count / 2)]) / 2.0
                ); /* average the two middle values */
            }
            else /* there are an odd number of values so there is only one     middle value */
            {
                __Median =     (double)this.ValueList[Convert.ToInt32(Math.Floor(__Count / 2.0))];
            }

            /* send the final result back to SQL Server as a FLOAT (same as     SqlDouble in .Net) */
            return new SqlDouble(__Median);
        }
        else
        {
            return SqlDouble.Null;
        }
    }

    public void Read(BinaryReader Serialized)
    {
        BinaryReader __FromSerialize; /* variable to hold decompressed     serialized binary data */

        using (MemoryStream  __MemoryStreamIn = 
        new MemoryStream ((byte[])Serialized.ReadBytes(Convert.ToInt32(Serialized.BaseStream.Length))))
        {
            using (DeflateStream __DecompressStream = 
                    new DeflateStream(__MemoryStreamIn,     CompressionMode.Decompress))
            {
                using (MemoryStream  __MemoryStreamOut = new MemoryStream     ())
                {

                for (int __Byte = __DecompressStream.ReadByte(); __Byte !=     -1;
                                __Byte = __DecompressStream.ReadByte())
                    {
                        __MemoryStreamOut.WriteByte((byte)__Byte);
                    }

                    __FromSerialize = new BinaryReader(__MemoryStreamOut);


                    __FromSerialize.BaseStream.Position = 0;

                    int __CountDoubles = __FromSerialize.ReadInt32();

                    if (__CountDoubles > -1)
                    {

                        this.ValueList = new List<Double>(__CountDoubles);

                        for (int __Index = 0; __Index < __CountDoubles;     __Index++)
                        {
                                this.ValueList.Add(__FromSerialize.ReadDouble());
                        }
                    }
                }
            }
        }
    }

    public void Write(BinaryWriter ToBeSerialized)
    {
        MemoryStream __ToCompress = new MemoryStream(); 
        BinaryWriter __BinaryWriter = new BinaryWriter(__ToCompress); 

        __BinaryWriter.Write(this.ValueList.Count);

        this.ValueList.Sort(); 

            foreach (double __TempDouble in this.ValueList)
        {
            __BinaryWriter.Write(__TempDouble);
        }

        using (MemoryStream __ToSerialize = new MemoryStream())
        {

            using (DeflateStream __CompressStream =
                    new DeflateStream(__ToSerialize,     CompressionMode.Compress, true))
            {               
                byte[] __TempArray = (byte[])__ToCompress.ToArray();

                __CompressStream.Write(__TempArray, 0, __TempArray.Length);
            }
            ToBeSerialized.Write(__ToSerialize.ToArray());
        }
    }
}

Thank you I managed to get it working with Magnus's answer, however I stumbled on a new problem which your solution had the answer to `how you used the binary reader && writer and compression && decompression` That fixed my problem for 20 000 rows and more — Donald Jansen, May 03 '16 at 15:00

SQL Server AGGREGATE function C#

2 Answers2