F# 3 SQL 数据库在多列上使用 Type Provider/groupBy

Question

评估 F# 3 使用类型提供程序功能来替代编写 T/SQL 或存储过程。

let summary = 
  query {   for dsm in db.DistributorSalesMaster do
            join c in db.CustomerMain on 
              ( dsm.CustomerId = c.CustomerId)
            join cal in db.Calendar on
              ( dsm.InvoiceDate =? cal.TheDate)
            join dsd in db.DistributorSalesDetail on
              ( dsm.SalesId = dsd.SalesId)             
            where (dsm.InvoiceDate >= Convert.ToDateTime("2010-12-01") 
                && dsm.InvoiceDate <= Convert.ToDateTime("2011-11-30")
                && c.MainDistributorId=1s
                && c.DistributorId=1s
                && c.CustomerId = 159M
                && cal.APYear?=2011
                )

            groupValBy dsd.InvoiceQuantity ca.APYear into g
            select (g.Key,g.Count())}

它的工作原理与预期的差不多。数据库上下文日志确认了这一点:

    SELECT COUNT(*) AS [Item2], [t2].[APYear] AS [Item1]
    FROM [dbo].[DistributorSalesMaster] AS [t0]
    INNER JOIN [dbo].[CustomerMain] AS [t1] ON [t0].[customerId] = [t1].[customerId]
    INNER JOIN [dbo].[Calendar] AS [t2] ON ([t0].[invoiceDate]) = [t2].[TheDate]
    INNER JOIN [dbo].[DistributorSalesDetail] AS [t3] ON [t0].[salesId] = [t3].[salesId]
    WHERE ([t0].[invoiceDate] >= @p0) AND ([t0].[invoiceDate] <= @p1) AND ([t1].[mainDistributorId] = @p2) AND ([t1].[distributorId] = @p3) AND ([t1].[customerId] = @p4) AND ([t2].[APYear] = @p5)
    GROUP BY [t2].[APYear]
    -- @p0: Input DateTime (Size = -1; Prec = 0; Scale = 0) [12/1/2010 12:00:00 AM]
    -- @p1: Input DateTime (Size = -1; Prec = 0; Scale = 0) [11/30/2011 12:00:00 AM]
    -- @p2: Input SmallInt (Size = -1; Prec = 0; Scale = 0) [1]
    -- @p3: Input SmallInt (Size = -1; Prec = 0; Scale = 0) [1]
    -- @p4: Input Decimal (Size = -1; Prec = 29; Scale = 0) [159]
    -- @p5: Input Int (Size = -1; Prec = 0; Scale = 0) [2011]
    -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.17929

但是，尝试以下操作:

let summary = 
   query {   for dsm in db.DistributorSalesMaster do
             join c in db.CustomerMain on 
                ( dsm.CustomerId = c.CustomerId)
             join cal in db.Calendar on
                ( dsm.InvoiceDate =? cal.TheDate)
             join dsd in db.DistributorSalesDetail on
                ( dsm.SalesId = dsd.SalesId)             
             where (dsm.InvoiceDate >= Convert.ToDateTime("2010-12-01") 
                    && dsm.InvoiceDate <= Convert.ToDateTime("2011-11-30")
                    && c.MainDistributorId=1s
                    && c.DistributorId=1s
                    && c.CustomerId = 159M
                    && cal.APYear?=2011
                    )

             groupValBy dsd.InvoiceQuantity (cal.APYear, cal.APMonth) into g              
             select (g.Key,g.Count())}

生成数据库上下文日志为:

System.NotSupportedException: The member 'System.Tuple`2[System.Nullable`1[System.Int32],System.Nullable`1[System.Int32]].Item1' has no supported translation to SQL.
   at System.Data.Linq.SqlClient.PostBindDotNetConverter.Visitor.VisitMember(SqlMember m)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   .
   .
   .
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.FSharp.Collections.SeqModule.ToArray[T](IEnumerable`1 source)
   at FSI_0002.showDataGrid[a](IEnumerable`1 x) in D:\Work\Tests\FP\FSharpVS2012\FSharpVS2012\IPFS3ZDPTests.fsx:line 21
   at <StartupCode$FSI_0024>.$FSI_0024.main@()
Stopped due to error

我正在尝试对多个列(cal.APYear、cal.APMonth)进行groupValBy。它似乎没有通过数据类型提供程序转换为 SQL。

我找到了一些其他方法来实现结果，但数据库上下文日志表明只有连接被转换为 SQL，其余的 groupBy/groupValBy 处理在内存中完成。这我根本不想要。我希望整个查询表达式在数据库中而不是在内存中进行翻译和执行。希望我清楚地表达了我的追求。

非常感谢这方面的任何帮助或指导。

Answer 1

您必须使用AnonymousObject 。以下内容应该有效:

let summary = 
   query {   for dsm in db.DistributorSalesMaster do
             join c in db.CustomerMain on 
                ( dsm.CustomerId = c.CustomerId)
             join cal in db.Calendar on
                ( dsm.InvoiceDate =? cal.TheDate)
             join dsd in db.DistributorSalesDetail on
                ( dsm.SalesId = dsd.SalesId)             
             where (dsm.InvoiceDate >= Convert.ToDateTime("2010-12-01") 
                    && dsm.InvoiceDate <= Convert.ToDateTime("2011-11-30")
                    && c.MainDistributorId=1s
                    && c.DistributorId=1s
                    && c.CustomerId = 159M
                    && cal.APYear?=2011
                    )

             let key = AnonymousObject<_,_>(cal.APYear, cal.APMonth)

             groupValBy dsd.InvoiceQuantity key into g              
             select (key.Item1, key.Item2, g.Count())}