none
F# LINQ call stored procedure and return result as array of tuples

    Question

  • I am learning LINQ with F# 3.0. I want to know how to get the result returned by stored procedure.

     For example, I have created a simple data table in SQL Server 2008 R2, the database name is MyDatabase, the data table name is employee.

    -- Create the employee table.
    CREATE TABLE [dbo].[employee] (
        [Id]            INT        NOT NULL,
        [LastName]      NTEXT      NOT NULL,
        [SalesYTD]      FLOAT (53) NOT NULL,
        [SalesLastYear] FLOAT (53) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    );
    USE MyDatabase

    INSERT INTO employee VALUES(1, 'AAA', 2100.00, 1000.00);
    INSERT INTO employee VALUES(2, 'BBB', 2200.00, 2000.00);
    INSERT INTO employee VALUES(3, 'CCC', 2300.00, 3000.00);
    INSERT INTO employee VALUES(4, 'DDD', 2400.00, 4000.00);
    INSERT INTO employee VALUES(5, 'EEE', 2500.00, 5000.00);

    GO

     

     

    The following statements are used to create stored procedure:

     

    USE [MyDatabase]

    GO

     

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

     

    Create PROCEDURE [dbo].[spListEmployee]

    AS

    BEGIN

    SELECT ID, MAX(SalesYTD), Max(SalesLastYear) FROM Employee GROUP BY ID

    END;

    GO

     

     


    The following is my F# code, I want to call the stored procedure, but I also want to get the result and put them into an array of tuples.

    #light

    open Microsoft.FSharp.Data.TypeProviders

    open Microsoft.FSharp.Linq

    open System

    open System.Data

    open System.Data.Linq

    open System.Linq

     

     [<Generate>]

    type EntityConnection = SqlEntityConnection<ConnectionString="Server=.;Initial Catalog=MyDatabase;Integrated Security=SSPI;MultipleActiveResultSets=true" >

    let context = EntityConnection.GetDataContext()

     

    let nullable value = new System.Nullable<_>(value)

     

    let xyz: System.Object = context.DataContext.ExecuteStoreQuery("[dbo].[spListEmployee]")

    printfn "OK"

     

    But I got compiler error: Error: This expression was expected to have type Object but here has type     Objects.ObjectResult<'a>

    I can do this:

    context.DataContext.ExecuteStoreQuery("[dbo].[spListEmployee]") |> ignore()

     

    This code can work, but how I can get the result and put them into an array of tuples.

    Like:

    let xyz = [| (1, 2100.0M, 1000.0M); (2, 2200.0M, 2000.0M); (3, 2300.0M, 3000.0M);

    (4, 2400.0M, 4000.0M); (5, 2500.0M, 5000.0M) |]

     

    Thanks,


    • Edited by zydjohn Saturday, February 18, 2012 9:26 PM
    Wednesday, February 15, 2012 8:35 PM

All replies

  • Why are you explicitly writing xyz as type System.Object? Let it be Objects.ObjectResult<'a> as it wants and you will have an enumerable collection of results which you can cast/project into an array as necessary (via Seq.toArray, Seq.map and the like).
    Wednesday, February 15, 2012 9:43 PM
  • Hi,

    I have tried this:

    let xyz = context.DataContext.ExecuteStoreQuery("[dbo].[spListEmployee]")

    But I got another compile error:

    Error: Value restriction. The value 'xyz' has been inferred to have generic type val xyz : Objects.ObjectResult<'_a> Either define 'xyz' as a simple data term, make it a function with explicit arguments or, if you do not intend for it to be generic, add a type annotation. 

    I don't know what to do.

    If you know, please show me your code.

    Thanks, 


    • Edited by zydjohn Saturday, February 18, 2012 9:29 PM
    Thursday, February 16, 2012 6:23 AM
  • Ah, right. These should help you understand the error and evaluate the best option for your code:

    http://msdn.microsoft.com/en-us/library/dd233183(v=VS.100).aspx

    http://blogs.msdn.com/b/mulambda/archive/2010/05/01/value-restriction-in-f.aspx

    So, if you know the type of 'a (based on the data your stored procedure returns) you could add a type annotation for xyz like Objects.ObjectResult<(int*int*int)[]> or whatever it is.

    Friday, February 17, 2012 8:57 PM
  • Hi, Dan Quirk:

    Thank you very much for your help.
    However, I didn't save my old program, when I tried to use the code here, I got even worse result:

    context.DataContext.ExecuteStoreQuery("[dbo].[spListEmployee]")
    Error: The field, constructor or member 'ExecuteStoreQuery' is not defined

    I don't know what went wrong.  So I downloaded Entity Framework 4.1 and installed on my PC.
    Add reference to it.
    The following is my code:

    #light
    open Microsoft.FSharp.Data.TypeProviders
    open Microsoft.FSharp.Linq
    open System
    open System.Data
    open System.Data.Entity
    open System.Data.Linq
    open System.Linq

    [<Generate>]
    type EntityConnection = SqlDataConnection<"Server=.;Initial Catalog=MyDatabase;Integrated Security=SSPI;MultipleActiveResultSets=true">
    let context = EntityConnection.GetDataContext()
    let nullable value = new System.Nullable<_>(value)

    context.DataContext.ExecuteStoreQuery("[dbo].[spListEmployee]")
    printfn "OK"

    But my code can not get compiled, due to the same error:

    context.DataContext.ExecuteStoreQuery("[dbo].[spListEmployee]")
    Error: The field, constructor or member 'ExecuteStoreQuery' is not defined

    I don't know how to fix this.
    Let me know if I did anything wrong.

     
    Saturday, February 18, 2012 9:50 PM