3 people like it.

Asynchronous Entity Framework Query

Entity Framework doesn't currently support async operations, but as long as the underlying provider is System.Data.SqlClient we can make it work.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
[<AutoOpen>]
module FsDataEx =

    open System
    open System.Data
    open System.Data.SqlClient

    type SqlCommand with
        member this.AsyncExecuteNonQuery() =
            Async.FromBeginEnd(this.BeginExecuteNonQuery, this.EndExecuteNonQuery)

        member this.AsyncExecuteReader() =
            Async.FromBeginEnd(this.BeginExecuteReader, this.EndExecuteReader)

        member this.AsyncExecuteReader(behavior: CommandBehavior) =
            Async.FromBeginEnd((fun (cb, state) -> this.BeginExecuteReader(cb, state, behavior)), 
                               this.EndExecuteReader)

        member this.AsyncExecuteXmlReader() =
            Async.FromBeginEnd(this.BeginExecuteXmlReader, this.EndExecuteXmlReader)

    /// Turns a null reference into DBNull.
    let private asDbNull (value:obj) =
        if Object.ReferenceEquals(value, null) 
        then box DBNull.Value else value

    open System.Data.Objects
    open System.Data.EntityClient

    type System.Linq.IQueryable<'a> with
        /// Executes an Entity Framework query asynchronously, provided the underlying data 
        /// provider is System.Data.SqlClient.
        member this.AsyncExecute<'a when 'a :> DataClasses.EntityObject>() = async {
            let query = this :?> ObjectQuery<'a>
            let connection = (query.Context.Connection :?> EntityConnection).StoreConnection :?> SqlConnection
            let builder = SqlConnectionStringBuilder(connection.ConnectionString, AsynchronousProcessing = true)
            connection.ConnectionString <- builder.ConnectionString
            
            use cmd = connection.CreateCommand()
            cmd.CommandText <- query.ToTraceString()
            
            query.Parameters
            |> Seq.map (fun x -> SqlParameter(x.Name, asDbNull x.Value))
            |> Array.ofSeq
            |> cmd.Parameters.AddRange

            // The connection should be closed when the EF DataContext is disposed.
            connection.Open()

            let! reader = cmd.AsyncExecuteReader()

            return seq {
                // The ObjectResult returned by Translate can be enumerated
                // only once, and it must be disposed
                use result = query.Context.Translate<'a>(reader)
                yield! result
            } |> Seq.cache
        }
Multiple items
type AutoOpenAttribute =
  inherit Attribute
  new : unit -> AutoOpenAttribute
  new : path:string -> AutoOpenAttribute
  member Path : string

Full name: Microsoft.FSharp.Core.AutoOpenAttribute

--------------------
new : unit -> AutoOpenAttribute
new : path:string -> AutoOpenAttribute
module FsDataEx

from Script
namespace System
namespace System.Data
namespace System.Data.SqlClient
Multiple items
type SqlCommand =
  inherit DbCommand
  new : unit -> SqlCommand + 3 overloads
  member BeginExecuteNonQuery : unit -> IAsyncResult + 1 overload
  member BeginExecuteReader : unit -> IAsyncResult + 3 overloads
  member BeginExecuteXmlReader : unit -> IAsyncResult + 1 overload
  member Cancel : unit -> unit
  member Clone : unit -> SqlCommand
  member CommandText : string with get, set
  member CommandTimeout : int with get, set
  member CommandType : CommandType with get, set
  member Connection : SqlConnection with get, set
  ...

Full name: System.Data.SqlClient.SqlCommand

--------------------
SqlCommand() : unit
SqlCommand(cmdText: string) : unit
SqlCommand(cmdText: string, connection: SqlConnection) : unit
SqlCommand(cmdText: string, connection: SqlConnection, transaction: SqlTransaction) : unit
val this : SqlCommand
member SqlCommand.AsyncExecuteNonQuery : unit -> Async<int>

Full name: Script.FsDataEx.AsyncExecuteNonQuery
Multiple items
type Async
static member AsBeginEnd : computation:('Arg -> Async<'T>) -> ('Arg * AsyncCallback * obj -> IAsyncResult) * (IAsyncResult -> 'T) * (IAsyncResult -> unit)
static member AwaitEvent : event:IEvent<'Del,'T> * ?cancelAction:(unit -> unit) -> Async<'T> (requires delegate and 'Del :> Delegate)
static member AwaitIAsyncResult : iar:IAsyncResult * ?millisecondsTimeout:int -> Async<bool>
static member AwaitTask : task:Task<'T> -> Async<'T>
static member AwaitWaitHandle : waitHandle:WaitHandle * ?millisecondsTimeout:int -> Async<bool>
static member CancelDefaultToken : unit -> unit
static member Catch : computation:Async<'T> -> Async<Choice<'T,exn>>
static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromContinuations : callback:(('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T>
static member Ignore : computation:Async<'T> -> Async<unit>
static member OnCancel : interruption:(unit -> unit) -> Async<IDisposable>
static member Parallel : computations:seq<Async<'T>> -> Async<'T []>
static member RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:CancellationToken -> 'T
static member Sleep : millisecondsDueTime:int -> Async<unit>
static member Start : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions * ?cancellationToken:CancellationToken -> Task<'T>
static member StartChild : computation:Async<'T> * ?millisecondsTimeout:int -> Async<Async<'T>>
static member StartChildAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions -> Async<Task<'T>>
static member StartImmediate : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartWithContinuations : computation:Async<'T> * continuation:('T -> unit) * exceptionContinuation:(exn -> unit) * cancellationContinuation:(OperationCanceledException -> unit) * ?cancellationToken:CancellationToken -> unit
static member SwitchToContext : syncContext:SynchronizationContext -> Async<unit>
static member SwitchToNewThread : unit -> Async<unit>
static member SwitchToThreadPool : unit -> Async<unit>
static member TryCancelled : computation:Async<'T> * compensation:(OperationCanceledException -> unit) -> Async<'T>
static member CancellationToken : Async<CancellationToken>
static member DefaultCancellationToken : CancellationToken

Full name: Microsoft.FSharp.Control.Async

--------------------
type Async<'T>

Full name: Microsoft.FSharp.Control.Async<_>
static member Async.FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member Async.FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member Async.FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member Async.FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
SqlCommand.BeginExecuteNonQuery() : IAsyncResult
SqlCommand.BeginExecuteNonQuery(callback: AsyncCallback, stateObject: obj) : IAsyncResult
SqlCommand.EndExecuteNonQuery(asyncResult: IAsyncResult) : int
member SqlCommand.AsyncExecuteReader : unit -> Async<SqlDataReader>

Full name: Script.FsDataEx.AsyncExecuteReader
SqlCommand.BeginExecuteReader() : IAsyncResult
SqlCommand.BeginExecuteReader(behavior: CommandBehavior) : IAsyncResult
SqlCommand.BeginExecuteReader(callback: AsyncCallback, stateObject: obj) : IAsyncResult
SqlCommand.BeginExecuteReader(callback: AsyncCallback, stateObject: obj, behavior: CommandBehavior) : IAsyncResult
SqlCommand.EndExecuteReader(asyncResult: IAsyncResult) : SqlDataReader
member SqlCommand.AsyncExecuteReader : behavior:CommandBehavior -> Async<SqlDataReader>

Full name: Script.FsDataEx.AsyncExecuteReader
val behavior : CommandBehavior
type CommandBehavior =
  | Default = 0
  | SingleResult = 1
  | SchemaOnly = 2
  | KeyInfo = 4
  | SingleRow = 8
  | SequentialAccess = 16
  | CloseConnection = 32

Full name: System.Data.CommandBehavior
val cb : AsyncCallback
val state : obj
member SqlCommand.AsyncExecuteXmlReader : unit -> Async<Xml.XmlReader>

Full name: Script.FsDataEx.AsyncExecuteXmlReader
SqlCommand.BeginExecuteXmlReader() : IAsyncResult
SqlCommand.BeginExecuteXmlReader(callback: AsyncCallback, stateObject: obj) : IAsyncResult
SqlCommand.EndExecuteXmlReader(asyncResult: IAsyncResult) : Xml.XmlReader
val private asDbNull : value:obj -> obj

Full name: Script.FsDataEx.asDbNull


 Turns a null reference into DBNull.
val value : obj
type obj = Object

Full name: Microsoft.FSharp.Core.obj
Multiple items
type Object =
  new : unit -> obj
  member Equals : obj:obj -> bool
  member GetHashCode : unit -> int
  member GetType : unit -> Type
  member ToString : unit -> string
  static member Equals : objA:obj * objB:obj -> bool
  static member ReferenceEquals : objA:obj * objB:obj -> bool

Full name: System.Object

--------------------
Object() : unit
Object.ReferenceEquals(objA: obj, objB: obj) : bool
val box : value:'T -> obj

Full name: Microsoft.FSharp.Core.Operators.box
type DBNull =
  member GetObjectData : info:SerializationInfo * context:StreamingContext -> unit
  member GetTypeCode : unit -> TypeCode
  member ToString : unit -> string + 1 overload
  static val Value : DBNull

Full name: System.DBNull
field DBNull.Value
namespace System.Linq
Multiple items
type IQueryable =
  member ElementType : Type
  member Expression : Expression
  member Provider : IQueryProvider

Full name: System.Linq.IQueryable

--------------------
type IQueryable<'T> =

Full name: System.Linq.IQueryable<_>
val this : Linq.IQueryable<'T>
member Linq.IQueryable.AsyncExecute : unit -> Async<seq<obj>>

Full name: Script.FsDataEx.AsyncExecute


 Executes an Entity Framework query asynchronously, provided the underlying data
 provider is System.Data.SqlClient.
val async : AsyncBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.async
val query : obj
val connection : SqlConnection
Multiple items
type SqlConnection =
  inherit DbConnection
  new : unit -> SqlConnection + 1 overload
  member BeginTransaction : unit -> SqlTransaction + 3 overloads
  member ChangeDatabase : database:string -> unit
  member Close : unit -> unit
  member ConnectionString : string with get, set
  member ConnectionTimeout : int
  member CreateCommand : unit -> SqlCommand
  member DataSource : string
  member Database : string
  member EnlistDistributedTransaction : transaction:ITransaction -> unit
  ...

Full name: System.Data.SqlClient.SqlConnection

--------------------
SqlConnection() : unit
SqlConnection(connectionString: string) : unit
val builder : SqlConnectionStringBuilder
Multiple items
type SqlConnectionStringBuilder =
  inherit DbConnectionStringBuilder
  new : unit -> SqlConnectionStringBuilder + 1 overload
  member ApplicationName : string with get, set
  member AsynchronousProcessing : bool with get, set
  member AttachDBFilename : string with get, set
  member Clear : unit -> unit
  member ConnectTimeout : int with get, set
  member ConnectionReset : bool with get, set
  member ContainsKey : keyword:string -> bool
  member ContextConnection : bool with get, set
  member CurrentLanguage : string with get, set
  ...

Full name: System.Data.SqlClient.SqlConnectionStringBuilder

--------------------
SqlConnectionStringBuilder() : unit
SqlConnectionStringBuilder(connectionString: string) : unit
property Common.DbConnectionStringBuilder.ConnectionString: string
val cmd : SqlCommand
property SqlCommand.CommandText: string
module Seq

from Microsoft.FSharp.Collections
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val x : obj
Multiple items
type SqlParameter =
  inherit DbParameter
  new : unit -> SqlParameter + 6 overloads
  member CompareInfo : SqlCompareOptions with get, set
  member DbType : DbType with get, set
  member Direction : ParameterDirection with get, set
  member IsNullable : bool with get, set
  member LocaleId : int with get, set
  member Offset : int with get, set
  member ParameterName : string with get, set
  member Precision : byte with get, set
  member ResetDbType : unit -> unit
  ...

Full name: System.Data.SqlClient.SqlParameter

--------------------
SqlParameter() : unit
SqlParameter(parameterName: string, dbType: SqlDbType) : unit
SqlParameter(parameterName: string, value: obj) : unit
SqlParameter(parameterName: string, dbType: SqlDbType, size: int) : unit
SqlParameter(parameterName: string, dbType: SqlDbType, size: int, sourceColumn: string) : unit
SqlParameter(parameterName: string, dbType: SqlDbType, size: int, direction: ParameterDirection, isNullable: bool, precision: byte, scale: byte, sourceColumn: string, sourceVersion: DataRowVersion, value: obj) : unit
SqlParameter(parameterName: string, dbType: SqlDbType, size: int, direction: ParameterDirection, precision: byte, scale: byte, sourceColumn: string, sourceVersion: DataRowVersion, sourceColumnNullMapping: bool, value: obj, xmlSchemaCollectionDatabase: string, xmlSchemaCollectionOwningSchema: string, xmlSchemaCollectionName: string) : unit
type Array =
  member Clone : unit -> obj
  member CopyTo : array:Array * index:int -> unit + 1 overload
  member GetEnumerator : unit -> IEnumerator
  member GetLength : dimension:int -> int
  member GetLongLength : dimension:int -> int64
  member GetLowerBound : dimension:int -> int
  member GetUpperBound : dimension:int -> int
  member GetValue : [<ParamArray>] indices:int[] -> obj + 7 overloads
  member Initialize : unit -> unit
  member IsFixedSize : bool
  ...

Full name: System.Array
val ofSeq : source:seq<'T> -> 'T []

Full name: Microsoft.FSharp.Collections.Array.ofSeq
property SqlCommand.Parameters: SqlParameterCollection
SqlParameterCollection.AddRange(values: Array) : unit
SqlParameterCollection.AddRange(values: SqlParameter []) : unit
val reader : SqlDataReader
member SqlCommand.AsyncExecuteReader : unit -> Async<SqlDataReader>
member SqlCommand.AsyncExecuteReader : behavior:CommandBehavior -> Async<SqlDataReader>
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Core.Operators.seq

--------------------
type seq<'T> = Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
val result : seq<obj>
val cache : source:seq<'T> -> seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.cache

More information

Link:http://fssnip.net/cm
Posted:5 years ago
Author:Joel Mueller
Tags: entity framework , async