CLRSQL 聚合函数。 LINQ 代码在 CLR 函数中工作，但无法在聚合中部署

Question

感谢您阅读本文，

VS2010针对SQLServer2008企业版，开发CLR Aggregate函数来计算MODE，该函数返回此错误：

“第 1 行创建聚合失败 因为类型“CMode”不符合 由于现场而符合 UDAGG 规范 'CS$<>9__CachedAnonymousMethodDelegate1'。”

此处产生错误：

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

这是完整的代码：

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);
            }
        }
    }

任何指导将不胜感激！

我能够在 SQLCLR 函数中运行所需的代码，这将验证我是否拥有所有授权，dll 在那里，等等..：

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;

        }
    };

期待您的评论。

Answer 1

试试这个代码。您的代码不起作用，因为编译器将您的隐式 Func 委托重写为编译委托（我强烈建议使用反射器亲眼检查这一点）。这还不错，因为它主要是出于性能原因（以避免每次调用时进行编译），但不幸的是，这会创建一个不可序列化的字段，并且不能与 SQL Server 很好地配合。为了避免这种情况，您必须使用表达式并手动编译它们。我的实现仅在整个调用（init）中创建一次委托。

总而言之，我强烈建议使用 HashSet 集合和某种分组甚至 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);
        }
    }


}

Answer 2

为了计算众数，我决定用 SQL 进行编码。即使查询针对数百万行，它也能很快得到结果。该查询计算每天的众数。

那是：

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

Answer 3

非常感谢幸运卢克！！！！

我在执行代码时遇到了一些错误，涉及空值。在这个过程的最后，我用 SQL 对其进行了编码，它的工作速度非常快。根据您的回答，我尝试实施 Mediam。它工作正常，但有 8000 个参数大小限制。任何有兴趣学习如何克服该限制的人都可以转到 Expert SQL 2005 的第 6 章并实现字典以避免序列化。

使用系统；
使用系统数据；
使用 System.Data.SqlClient；
使用 System.Data.SqlTypes；
使用 Microsoft.SqlServer.Server；
使用 System.Collections.Generic；
使用系统.IO；
使用 System.Linq；
使用 System.Linq.Expressions；

<前><代码>[可序列化]
[SqlUserDefinedAggregate(Format.UserDefined

,
最大字节大小 = 8000 ,
IsInvariantToDuplicates = false ,
IsInvariantToOrder = false)]

公共结构 CMedian :IBinarySerialize
    {

    私有列表列表;
    公共无效初始化（）
        {
        this.list = new List();
        }

    公共无效累积（SqlInt16值）
        {
        this.list.Add(Value.Value);
        }

    public void 合并（CMedian Group）
        {
        this.list.AddRange(Group.list.ToArray());
        }

    公共静态 IQueryable ApplyOrdering(IQueryable;

查询，表达式表达式）
{
表达式> exp = (表达式>)表达式;
返回查询.OrderBy(exp);
}

 public SqlDecimal Terminate()
        {
        小数中位数；
        int halfIndex；
        int numberCount;

        IQueryable; myInts = list.AsQueryable();
        表达式> myExpression = i =>;我;
        var SortedNumbers = (ApplyOrdering(myInts ,

myExpression));

 numberCount = myInts.Count();
        halfIndex = (numberCount + 1) / 2;

        if ((数量% 2) == 0)
            {
            halfIndex = (numberCount) / 2;
            中位数 = ((list.ElementAt(halfIndex) +

list.ElementAt(halfIndex+1)) / 2 );
}
别的
{
halfIndex = (numberCount + 1) / 2;
中位数 = list.ElementAt(halfIndex);
}
返回（SqlDecimal）中位数；
}
//IBinary序列化
公共无效读取（BinaryReader r）
{
int itemCount = r.ReadInt16();
this.list = new List(itemCount);
for (int i = 0; i <= itemCount - 1; i++)
{
this.list.Add(r.ReadInt16());
}
}
//IBinary序列化
公共无效写入（BinaryWriter w）
{
尝试
{
foreach（this.list 中的 Int16）
{
w.写；
}
}
捕获（异常 e）
{
抛出新的异常（“经度：”+
w.BaseStream.Length + "位置：" +
w.BaseStream.Position);
}

<前><代码>}
}

Answer 4

我使用 .NET 3.5 在 Visual Studio 2010 中创建了两个 UDA 来计算 IRR 和 NPV。它们在 SQL 2012 上运行和部署得很好。但是，当我尝试将它们部署到 SQL 2008R2 时，它们失败了，并出现了可怕的“不符合 UDAGG 规范”错误。我尝试实施 LuckyLuke 的方法，但没有成功。只有在从 UDA 类中删除所有 lambda 表达式和 LINQ 后，我才最终在 SQL 2008R2 上取得成功。有趣的是，我仍然能够在我的 UDA 使用的类中使用这些语言元素。