次の方法で共有

Querying Northwind with Group By bug fixed

  • [アーティクル]

In this post:

https://blogs.msdn.com/calvin_hsia/archive/2007/08/17/install-northwind-for-sql-express-and-use-visual-studio-and-dlinq-to-query-it.aspx

I mentioned how a bug with Group By and composite keys made more local calculations. Using the AsEnumerable operator caused the query to execute locally.

I now have a build of Visual Studio that has the bug fixed and I modified the query to remove the AsEnumerable().

The performance is very quick, as it’s all executed on the back end, as seen by this log:

SELECT [t7].[CustomerID], [t7].[CompanyName], [t7].[value] AS [CustTotal], [t7].[value2] AS [PctTotal]

FROM (

    SELECT SUM([t6].[value2]) AS [value], SUM([t6].[value]) AS [value2], [t6].[CustomerID], [t6].[CompanyName]

    FROM (

        SELECT (@p1 * (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t3].[Quantity]))) * [t3].[UnitPrice])) * (@p2 - [t3].[Discount])) / ((

            SELECT SUM([t5].[value])

            FROM (

                SELECT (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t4].[Quantity]))) * [t4].[UnitPrice])) * (@p3 - [t4].[Discount]) AS [value]

                FROM [dbo].[Order Details] AS [t4]

                ) AS [t5]

            )) AS [value], [t3].[CustomerID], [t3].[CompanyName], [t3].[value] AS [value2]

        FROM (

     SELECT (CONVERT(Real,(CONVERT(Decimal(29,4),CONVERT(Int,[t2].[Quantity]))) * [t2].[UnitPrice])) * (@p0 - [t2].[Discount]) AS [value], [t2].[Quantity], [t2].[UnitPrice], [t2].[Discount], [t0].[CustomerID], [t0].[CompanyName]

            FROM [dbo].[Customers] AS [t0]

            INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]

            INNER JOIN [dbo].[Order Details] AS [t2] ON [t1].[OrderID] = [t2].[OrderID]

            ) AS [t3]

        ) AS [t6]

    GROUP BY [t6].[CustomerID], [t6].[CompanyName]

    ) AS [t7]

ORDER BY [t7].[value2] DESC, [t7].[CustomerID]

-- @p0: Input Real (Size = 0; Prec = 0; Scale = 0) [1]

-- @p1: Input Real (Size = 0; Prec = 0; Scale = 0) [100]

-- @p2: Input Real (Size = 0; Prec = 0; Scale = 0) [1]

-- @p3: Input Real (Size = 0; Prec = 0; Scale = 0) [1]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20828.0

The entire code is posted here:

Imports System.IO

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Width = 1024

        Me.Height = 768

        Dim NWind As New DataClasses1DataContext

        NWind.Log = Console.Out

        Dim q = From cust In NWind.Customers _

                    Join ord In NWind.Orders On cust.CustomerID Equals ord.CustomerID _

                    Join det In NWind.Order_Details On ord.OrderID Equals det.OrderID _

  Group By cust.CustomerID, cust.CompanyName Into _

                        CustTotal = Sum(det.Quantity * det.UnitPrice * (1 - det.Discount)), _

                        PctTotal = Sum(100 * (det.Quantity * det.UnitPrice * (1 - det.Discount)) / _

                            Aggregate d2 In NWind.Order_Details _

                             Into Sum(d2.Quantity * d2.UnitPrice * (1 - d2.Discount))) _

                    Order By CustomerID _

                    Select CustomerID, CompanyName, CustTotal, PctTotal _

                    Order By PctTotal Descending

        Browse(q)

    End Sub

    Sub Browse(Of t)(ByVal seq As IEnumerable(Of t))

        Dim GridView As New DataGridView

        GridView.Width = Me.Width

        GridView.Height = Me.Height

        Me.Controls.Add(GridView)

        Dim pl = New List(Of t)(seq)

        GridView.DataSource = pl

        Me.Text = pl.Count.ToString

        GridView.Dock = DockStyle.Fill

        GridView.AutoResizeColumns()

    End Sub

End Class

Comments