c# – LINQ to SQL转换溢出

我真的坚持在这一个.我在sql中有广泛的背景,但是我刚开始一个新工作,他们更喜欢使用LINQ进行简单的查询.
所以在本着学习的精神,我试图重写这个简单的SQL查询：

SELECT
    AVG([Weight] / [Count]) AS [Average],COUNT(*) AS [Count]
FROM [dbo].[Average Weight]
WHERE
    [ID] = 187

为了清楚起见,这里是表模式：

CREATE TABLE [dbo].[Average Weight]
(
    [ID] INT NOT NULL,[Weight] DECIMAL(8,4) NOT NULL,[Count] INT NOT NULL,[Date] DATETIME NOT NULL,PRIMARY KEY([ID],[Date])
)

这是我想出的：

var averageWeight = Data.Context.AverageWeight
    .Where(i => i.ID == 187)
    .GroupBy(w => w.ID)
    .Select(i => new { Average = i.Average(a => a.Weight / a.Count),Count = i.Count() });

Data.Context.AverageWeight是由sql Metal生成的一个Linq To sql对象.如果我尝试averageWeight.First()我得到一个OverflowException.我使用sql Profiler来查看LINQ生成的参数化查询是什么样的.重新缩进,看起来像这样：

EXEC sp_executesql N'
SELECT TOP(1)
    [t2].[value] AS [Average],[t2].[value2] AS [Count]
FROM (
        SELECT
            AVG([t1].[value]) AS [value],COUNT(*) AS [value2]
        FROM (
                SELECT
                    [t0].[Weight] / (CONVERT(DECIMAL(29,4),[t0].[Count])) AS 
                    [value],[t0].[ID]
                FROM [dbo].[Average Weight] AS [t0]
             ) AS [t1]
        WHERE
            ([t1].[ID] = @p0)
        GROUP BY
            [t1].[ID]
     ) AS [t2]',N'@p0 int',@p0 = 187

过多的嵌套在一边,我只看到一个问题：DECIMAL(29,4). (查询运行并给出预期的结果.)据了解,任何高于28的都将溢出C#十进制数据类型. [Count]是一个INT,所以它需要被CONVERTED,但是[Weight]是一个DECIMAL(8,4).我不知道为什么LINQ会使用这么大的数据类型.

为什么LINQ CONVERT会导致和溢出的数据类型？有没有改变这种行为？或者我甚至在正确的轨道上？

另外,Data.Context.AverageWeight是由sql Metal生成的,我验证了Weight是一个十进制,Column Attribute是正确的(十进制(8,4)).

提前致谢.

更新：
所以看起来LINQ to sql可能是罪魁祸首.我改变了我的LINQ这样：

var averageWeight = Data.Context.AverageWeight
    .Where(i => i.ID == 187)
    .GroupBy(w => w.ID)
    .Select(i => new { Average = i.Average(a => a.Weight) / (decimal)i.Average(a => a.Count),Count = i.Count() });

现在生成的sql看起来像这样：

SELECT TOP(1)
    [t2].[value] AS [Average],COUNT(*) AS [value2]
        FROM (
                SELECT
                    [t0].[Weight] / (CONVERT(DECIMAL(16,[t0].[Count])) AS [value],[t0].[ID]
                FROM [dbo].[Average Weight] AS [t0]
             ) AS [t1]
        WHERE
            ([t1].[ID] = 187)
        GROUP BY
            [t1].[ID]
     ) AS [t2]

其结果是：

Average                  Count
0.000518750000000        16

以前的做法给了：

Average                  Count
0.000518750000000000000  16

不再有溢出,但查询效率较低.我不知道为什么LINQ to sql会转换到这么高的精度.不是其他变量是非常精确的.据我所知,在LINQ中我没有办法强制数据类型.

有任何想法吗？

解决方法

我不是专家,但是查看sql-CLR类型映射表(例如 http://msdn.microsoft.com/en-us/library/bb386947.aspx),可以看到sql十进制值被转换为CLR System.Decimal类型,并且CLR System.Decimal值被转换为sql DECIMAL(29,4)类型.

所以在你的例子中,a.Weight作为sql十进制转换为CLR System.Decimal.因此a.Weight由a.Count分割因此被视为一个System.Decimal分区,右侧的操作数(a.Count)必须转换为CLR System.Decimal. Linq然后将此类型转换回转到sql,这导致Count被转换为DECIMAL(29,4).

不幸,

a.Weight / (double) a.Count

将不会工作,因为正确的操作数必须转换为System.Decimal,但double不能像int一样自动转换.然而,

(double) a.Weight / a.Count

将会工作,因为该部门现在被视为一个二分法,而不是System.Decimals,所以生成的sql看起来像这样：

SELECT (CONVERT(Float,[t0].[Weight])) / (CONVERT(Float,[t0].[Count])) AS [value]
...

你真正想要的是Linq将a.Count看作是一个十进制,而不是int.您可以通过更改DBML文件中的Count类型(see here)来执行此操作.当我这样做,Linq查询：

var averageweight = context.AverageWeights
            .Where(i => i.ID == 187)
            .GroupBy(w => w.ID)
            .Select(i => new {Average = i.Average(a => a.Weight/a.Count),Count = i.Count()});

导致sql：

SELECT AVG([t0].[Weight] / [t0].[Count]) AS [Average],COUNT(*) AS [Count]
FROM [dbo].[AverageWeight] AS [t0]
WHERE [t0].[ID] = @p0
GROUP BY [t0].[ID]

这是期望的结果.但是,更改DBML文件中Count属性的类型可能会有其他意外的副作用.

顺便说一下,从更新的Linq查询生成的sql似乎是错误的. Linq明确要求将所有权重的平均值除以所有计数的平均值,但这不是sql所做的.当我编写相同的Linq查询时,我得到的是：

SELECT [t1].[value] / (CONVERT(Decimal(29,[t1].[value2])) AS [Average],[t1].[value3] AS [Count]
FROM (
    SELECT AVG([t0].[Weight]) AS [value],AVG([t0].[Count]) AS [value2],COUNT(*) AS [value3]
    FROM [dbo].[Average Weight] AS [t0]
    WHERE [t0].[ID] = @p0
    GROUP BY [t0].[ID]
    ) AS [t1]

请注意,有两个AVG而不是一个.还要注意,转换为十进制(29,4)仍然存在,因为Linq仍在执行System.Decimal分区.

c# – LINQ to SQL转换溢出

解决方法

猜你在找的C#相关文章