Переполнение переходов 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 - это объект Linq To SQL, сгенерированный SQLMetal. Если я пытаюсь 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 переполнит тип данных счисления С#. [Count] - INT, поэтому он должен быть CONVERTED, но [Weight] является DECIMAL (8, 4). Я не знаю, почему LINQ будет использовать такой большой тип данных.

Почему LINQ CONVERT относится к типу данных, который вызывает и переполняет? Есть ли способ изменить это поведение? Или я даже на правильном пути?

Кроме того, Data.Context.AverageWeight был сгенерирован SqlMetal, и я подтвердил, что вес является десятичным, а атрибут столбца правильный (десятичный (8,4)).

Спасибо заранее.

Update: Таким образом, похоже, что 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],
    [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]

Результат:

Average                  Count
0.000518750000000        16

Предыдущий подход дал:

Average                  Count
0.000518750000000000000  16

Больше нет переполнения, но запрос менее эффективен. Я не знаю, почему LINQ to SQL будет КОНВЕРТИРОВАТЬ К такой высокой точности. Не другие переменные настолько точны. И насколько я могу судить, в LINQ я ничего не могу сделать, чтобы заставить тип данных.

Любые идеи?

Ответ 1

Я не эксперт, но, глядя на таблицы сопоставления типов SQL-CLR (например, http://msdn.microsoft.com/en-us/library/bb386947.aspx), вы можете видеть, что SQL decimal значения преобразуются в тип 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, но двойной нельзя преобразовать автоматически, как int. Тем не менее,

(double) a.Weight / a.Count

будет работать, потому что деление теперь рассматривается как деление удвоений, а не System.Decimals,, поэтому полученный SQL выглядит следующим образом:

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

Что вы действительно хотите, так это для Linq для обработки a.Count, как если бы он уже был десятичным, а не int. Вы можете сделать это, изменив свойство Type of Count в вашем файле DBML (см. Здесь). Когда я это сделал, запрос 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]

что является желаемым результатом. Однако изменение типа свойства Count в файле DBML может иметь другие непредвиденные побочные эффекты.

Кстати, SQL, сгенерированный из вашего обновленного запроса Linq, кажется неправильным. Для Linq явно требуется, чтобы среднее значение всех весов делилось на среднее значение всех счетчиков, но это не то, что делает SQL. Когда я пишу тот же запрос Linq, я получаю SQL:

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]

Обратите внимание, что есть два вызова AVG, а не только один. Также обратите внимание, что преобразование в DECIMAL(29,4) все еще присутствует, поскольку Linq все еще выполняет разделение System.Decimal.

Ответ 1

Ответ 2