F# 3 SQL Database using Type Provider/ groupBy on multiple columns

Question

Evaluating F# 3 for using it Type Provider feature as a replacement of writing T/SQL or stored procedures.

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

It work pretty much as expected. DB Context log confirms this as:

    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

However, attempting following:

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

produces DB Context log as:

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

I am trying to groupValBy on multiple columns (cal.APYear, cal.APMonth). It doesn't seem to translate to SQL by Data Type Provider.

I found some other ways to achieve the result but DB context log suggests that only joins are translated to SQL and remaining groupBy/groupValBy processing is done in memory. This I simply do not want. I want whole query expression be translated and executed at DB not in memory. Hope I made my quest clear.

Will appreciate any help or guide in this regard.

Answer 1

You have to use AnonymousObject. The following should work:

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