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是由sqlMetal生成的一个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是由sqlMetal生成的,我验证了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中我没有办法强制数据类型.

有任何想法吗？