所以在本着学习的精神,我试图重写这个简单的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是由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中我没有办法强制数据类型.
有任何想法吗？
解决方法
所以在你的例子中,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分区.