在多列上使用类型提供程序/ groupBy的F# 3SQL数据库

Question

评估SQL3使用它的类型提供程序功能作为编写T/ F#或存储过程的替代。

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。

我找到了一些其他方法来实现这个结果，但是DB上下文日志建议只将连接转换为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())}