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,

Answer 1

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).

Answer 2

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, 

Answer 3

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.

Answer 4

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.