LINQ to SQL Conversion Overflows

I'm really stuck on this one. I have an extensive background in SQL, but I just started a new job and they prefer to use LINQ for simple queries. So in the spirit of learning, I tried to re-write this simple SQL query:

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

For the sake of clarity, here's the table schema:

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])
)

Here's what I came up with:

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 is a Linq To SQL object generated by SQLMetal. If I try to averageWeight.First() I get an OverflowException. I used the SQL Profiler to see what the parametrized query generated by LINQ looks like. Re-indented that looks like this:

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

Excessive nesting aside, I only see one problem: DECIMAL(29, 4). (The query runs and gives the expected result.) It's my understanding that anything above 28 will overflow the C# decimal data type. [Count] is an INT so it does need to be CONVERTed, but [Weight] is a DECIMAL(8, 4). I don't have any idea why LINQ would use such a large data type.

Why would LINQ CONVERT to a data type that causes and overflow? Is there anyway to change this behavior? Or am I even on the right track?

Also, Data.Context.AverageWeight was generated by SqlMetal and I verified W开发者_运维问答eight is a decimal and Column Attribute is correct (Decimal(8,4)).

Thanks in advance.

Update: So it looks like LINQ to SQL may be the culprit. I changed my LINQ like this:

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

Now the SQL generated looks like this:

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(16, 4), [t0].[Count])) AS [value],
                    [t0].[ID]
                FROM [dbo].[Average Weight] AS [t0]
             ) AS [t1]
        WHERE
            ([t1].[ID] = 187)
        GROUP BY
            [t1].[ID]
     ) AS [t2]

The result of this is:

Average                  Count
0.000518750000000        16

The previous approach gave:

Average                  Count
0.000518750000000000000  16

There is no longer an overflow, but the query is less efficient. I don't know why LINQ to SQL would CONVERT TO such a high precision. Not of the other variables are so precise. And as far as I can tell, there is nothing I can do in LINQ to force the data type.

Any ideas?

I'm no expert, but looking at the SQL-CLR type mapping tables (e.g. http://msdn.microsoft.com/en-us/library/bb386947.aspx) you can see that SQL decimal values are converted to the CLR System.Decimal type and CLR System.Decimal values are converted to the SQL DECIMAL(29,4) type.

So in your example, a.Weight as a SQL decimal is converted to a CLR System.Decimal. The division of a.Weight by a.Count is therefore treated as a System.Decimal division and the right operand (a.Count) must be converted to a CLR System.Decimal. Linq then translates this type conversion back to SQL which results in Count being converted to a DECIMAL(29,4).

Unfortunately,

a.Weight / (double) a.Count

won't work because the right operand must be converted to a System.Decimal but a double can't be automatically converted like an int can. However,

(double) a.Weight / a.Count

will work because the division is now treated as a division of doubles, not System.Decimals, so the resulting SQL looks like this:

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

What you really want is for Linq to treat a.Count as though it is already a decimal, not an int. You can do this by changing the Type of the Count property in your DBML file (see here). When I did this, the Linq query:

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

results in the 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]

which is the desired result. However, changing the type of the Count property in the DBML file may have other unintended side effects.

By the way, the SQL generated from your updated Linq query seems to be wrong. The Linq clearly requires that the average of all the weights be divided by the average of all the counts, but this is not what the SQL does. When I write the same Linq query, the SQL I get is:

SELECT [t1].[value] / (CONVERT(Decimal(29,4),[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]

Note that there are two calls to AVG rather than just one. Also note that the conversion to Decimal(29,4) is still present since Linq is still doing a System.Decimal division.

I haven't tested but you could try to cast a.Count :

... a.Weight / (double) a.Count ...