logo标签列表

EF6 数据库优先使用存储过程异步化

entity-framework
3

如何以异步模式运行EF6储存过程（数据库优先）？

我了解到ToListAsync()，但在储存过程上却没有看到这个方法。

同时，我不确定在实际调用返回（#1）一个OUT参数或者（#2）一组项目时是否有不同的调用储存过程的方法：

情况#1

 using (DBContext db = new DBContext())
 {
     ObjectParameter result = new ObjectParameter("Result", 
                                  typeof(global::System.Boolean));

     db.Login("email@email.com", "password", result);
 }

案例#2

 using (DBContext db = new DBContext())
 {
     var result = db.Contact_GetList("New York");
 }

感谢您的帮助。
这是在ASP.NET应用程序的上下文中吗？比如控制器方法？ - calebboyd
它位于WebAPI和MVC应用程序的数据层中。每个控制器都会调用此DataLayer方法。 - SF Developer
2个回答
3
根据这个工作项，您需要使用SqlQueryAsync。请随意在EF Codeplex网站上为该工作项投票。
你能在现有的存储过程中使用它吗？另外，我似乎找不到那个SqlQueryAsync方法。你有什么想法吗？ - SF Developer
@StriplingWarrior - 推荐在Entity Framework CodePlex网站上为工作项投票有什么问题吗？这有助于优先处理工作。 - Pawel
@Pawel：我对你的编辑没有任何问题。在原始形式中，你听起来像是在邀请人们为你的答案投票，如果这是一个好答案，我认为这是理所当然的。 - StriplingWarrior
1
@StriplingWarrior - 在我回滚之后，我意识到我的建议并不清楚，需要点赞。因此进行了编辑。感谢您保持答案的整洁！ - Pawel
4
请问SqlQueryAsync方法属于哪个命名空间？我在当前的dbContext.Database.xx上没有找到它。请帮忙确认一下。 - SF Developer
链接已损坏。 - Alain
1
为了实现对存储过程的映射并开始使用而无需编写任何初始代码，这是我的做法。

  1. 创建一个新的模型和新的连接字符串，这将在 web.config 文件中自动生成连接字符串（如果您使用当前的连接字符串，则在模型浏览器中测试存储过程功能时可能无法工作）。

  2. 映射表和存储过程（可以在模型浏览器中测试存储过程）。

  3. 创建代表每个存储过程检索到的属性的类，例如，如果您的存储过程返回三列 A、B、C，则该类也必须具有这三个列作为属性，并在要成为 PK 的列的顶部放置 [key()]。

  4. 现在使用创建的类和一个新的 DbContext 创建控制器。

  5. 然后将数据上下文中为模型生成的信息复制并粘贴到创建控制器时生成的新上下文中。

  6. 当要使用存储过程时，它们将准备好在 db.context 上，因为您将它们的代码粘贴到创建控制器时生成的新 db-context 中。

注意：希望这不会让你感到困惑，我可以在不键入任何代码的情况下使用存储过程，请告诉我如果你需要示例代码或屏幕截图，创建后你的新数据库上下文将不会被覆盖。
这是我映射的存储过程。
'--------------------------------------------------------------------------
' <auto-generated>
'     This code was generated from a template.
'
'     Manual changes to this file may cause unexpected behavior in your application.
'     Manual changes to this file will be overwritten if the code is regenerated.
' </auto-generated>
'-----------------------------------------------------------------------

Imports System
Imports System.Collections.Generic

Partial Public Class phone_CurrentConferences_Result
    Public Property AppointmentID As Integer
    Public Property AppTitle As String
    Public Property DateTime As Nullable(Of Date)
    Public Property [Date] As String
    Public Property Time As String
    Public Property Company As String
    Public Property Contact As String
    Public Property Phone As String
    Public Property Office As String
    Public Property Lead_Director As String
    Public Property TBD As Nullable(Of Boolean)
    Public Property conference As String
End Class

这是带有主键的相同模型

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel.DataAnnotations

Public Class Conferences
    [Key]
    Public Property AppointmentID As Integer
    Public Property AppTitle As String
    Public Property DateTime As Nullable(Of Date)
    Public Property [Date] As String
    Public Property Time As String
    Public Property Company As String
    Public Property Contact As String
    Public Property Phone As String
    Public Property Office As String
    Public Property Lead_Director As String
    Public Property TBD As Nullable(Of Boolean)
    Public Property conference As String
End Class

这是由 EF 生成的上下文

'--------------------------------------------------------------------------
' <auto-generated>
'     This code was generated from a template.
'
'     Manual changes to this file may cause unexpected behavior in your application.
'     Manual changes to this file will be overwritten if the code is regenerated.
' </auto-generated>
'--------------------------------------------------------------------------

Imports System
Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure
Imports System.Data.Entity.Core.Objects
Imports System.Linq

Partial Public Class DayMasterEntities
    Inherits DbContext

    Public Sub New()
        MyBase.New("name=DayMasterEntities")
    End Sub

    Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
        Throw New UnintentionalCodeFirstException()
    End Sub

    Public Overridable Function phone_CurrentConferences(number As String, [date] As Nullable(Of Date)) As ObjectResult(Of phone_CurrentConferences_Result)
        Dim numberParameter As ObjectParameter = If(number IsNot Nothing, New ObjectParameter("number", number), New ObjectParameter("number", GetType(String)))

        Dim dateParameter As ObjectParameter = If([date].HasValue, New ObjectParameter("date", [date]), New ObjectParameter("date", GetType(Date)))

        Return DirectCast(Me, IObjectContextAdapter).ObjectContext.ExecuteFunction(Of phone_CurrentConferences_Result)("phone_CurrentConferences", numberParameter, dateParameter)
    End Function

End Class

因此，当我创建控制器时，我使用带有<KEY()>的模型，并创建自己的上下文，它将如下所示

Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure
Imports System.Data.Entity.Core.Objects

Namespace Models

    Public Class DayMasterContext
        Inherits DbContext

        ' You can add custom code to this file. Changes will not be overwritten.
        ' 
        ' If you want Entity Framework to drop and regenerate your database
        ' automatically whenever you change your model schema, please use data migrations.
        ' For more information refer to the documentation:
        ' http://msdn.microsoft.com/en-us/data/jj591621.aspx

        Public Sub New()
            MyBase.New("name=DayMasterEntities")
        End Sub

        Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
            Throw New UnintentionalCodeFirstException()
        End Sub

        Public Property Conferences As System.Data.Entity.DbSet(Of Conferences)
    End Class
End Namespace

然后我将EF生成的上下文中的信息复制到我的上下文中。
Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure
Imports System.Data.Entity.Core.Objects

Namespace Models

    Public Class DayMasterContext
        Inherits DbContext

        ' You can add custom code to this file. Changes will not be overwritten.
        ' 
        ' If you want Entity Framework to drop and regenerate your database
        ' automatically whenever you change your model schema, please use data migrations.
        ' For more information refer to the documentation:
        ' http://msdn.microsoft.com/en-us/data/jj591621.aspx

        Public Sub New()
            MyBase.New("name=DayMasterEntities")
        End Sub

        Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
            Throw New UnintentionalCodeFirstException()
        End Sub

        Public Overridable Function phone_CurrentConferences(number As String, [date] As Nullable(Of Date)) As ObjectResult(Of phone_CurrentConferences_Result)
            Dim numberParameter As ObjectParameter = If(number IsNot Nothing, New ObjectParameter("number", number), New ObjectParameter("number", GetType(String)))

            Dim dateParameter As ObjectParameter = If([date].HasValue, New ObjectParameter("date", [date]), New ObjectParameter("date", GetType(Date)))

            Return DirectCast(Me, IObjectContextAdapter).ObjectContext.ExecuteFunction(Of phone_CurrentConferences_Result)("phone_CurrentConferences", numberParameter, dateParameter)
        End Function

        Public Property Conferences As System.Data.Entity.DbSet(Of Conferences)
    End Class
End Namespace

所以，现在您可以使用此上下文来查询。
entConferences(number As String, [date] As Nullable(Of Date)) As ObjectResult(Of phone_CurrentConferences_Result)

或者获取一个DBSet（会议）

这是我使用这种技术创建的控制器

看看我调用存储过程的位置

Dim conferences = db.phone_CurrentConferences(phoneNumber, currentDate)

Imports System.Data
Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure
Imports System.Linq
Imports System.Net
Imports System.Net.Http
Imports System.Web.Http
Imports System.Web.Http.Description
Imports BIWEBAPI
Imports BIWEBAPI.Models

Namespace Controllers.DayMasterControllers
    Public Class ConferencesController
        Inherits System.Web.Http.ApiController

        Private db As New DayMasterContext

        ' GET: api/Conferences
        Function GetConferences() As IQueryable(Of Conferences)
            Return db.Conferences
        End Function

        ' GET: api/Conferences/3053742500 
        ''' <summary>
        ''' Use to get the current conferences  selected by date
        ''' </summary>
        ''' <param name="id">phone number and date separated by coma ",""</param>
        ''' <returns>conferences by date</returns>
        ''' <remarks></remarks>
        <ResponseType(GetType(Conferences))>
        Function GetConferences(ByVal id As String) As List(Of Conferences)
            Dim conferencelist = New List(Of Conferences)
            Dim dateAndPhoneNumber = Split(id, ",")
            Dim currentDate = ""
            Dim phoneNumber = dateAndPhoneNumber(0)

            If dateAndPhoneNumber.Length > 1 Then
                currentDate = DateTime.Parse(dateAndPhoneNumber(1))
            Else : currentDate = DateTime.Today
            End If

            Dim conferences = db.phone_CurrentConferences(phoneNumber, currentDate)

            For Each conferenceInQuery As Object In conferences
                Dim conference = New Conferences()
                conference.AppointmentID = conferenceInQuery.AppointmentID
                conference.AppTitle = conferenceInQuery.AppTitle
                conference.DateTime = conferenceInQuery.DateTime
                conference.[Date] = conferenceInQuery.[Date]
                conference.Time = conferenceInQuery.Time
                conference.Company = conferenceInQuery.Company
                conference.Contact = conferenceInQuery.Contact
                conference.Phone = conferenceInQuery.Phone
                conference.Office = conferenceInQuery.Office
                conference.Lead_Director = conferenceInQuery.Lead_Director
                conference.TBD = conferenceInQuery.TBD
                conference.conference = conferenceInQuery.conference

                conferencelist.Add(conference)
            Next

            Return conferencelist
        End Function

        ' PUT: api/Conferences/5
        <ResponseType(GetType(Void))>
        Function PutConferences(ByVal id As Integer, ByVal conferences As Conferences) As IHttpActionResult
            If Not ModelState.IsValid Then
                Return BadRequest(ModelState)
            End If

            If Not id = conferences.AppointmentID Then
                 Return BadRequest()
            End If

            db.Entry(conferences).State = EntityState.Modified

            Try
                db.SaveChanges()
            Catch ex As DbUpdateConcurrencyException
                If Not (ConferencesExists(id)) Then
                    Return NotFound()
                Else
                    Throw
                End If
            End Try

            Return StatusCode(HttpStatusCode.NoContent)
       End Function

       ' POST: api/Conferences
       <ResponseType(GetType(Conferences))>
       Function PostConferences(ByVal conferences As Conferences) As IHttpActionResult
           If Not ModelState.IsValid Then
               Return BadRequest(ModelState)
           End If

           db.Conferences.Add(conferences)
           db.SaveChanges()

           Return CreatedAtRoute("DefaultApi", New With {.id = conferences.AppointmentID}, conferences)
       End Function

       ' DELETE: api/Conferences/5
       <ResponseType(GetType(Conferences))>
       Function DeleteConferences(ByVal id As Integer) As IHttpActionResult
           Dim conferences As Conferences = db.Conferences.Find(id)

           If IsNothing(conferences) Then
               Return NotFound()
           End If

           db.Conferences.Remove(conferences)
           db.SaveChanges()

           Return Ok(conferences)
       End Function

       Protected Overrides Sub Dispose(ByVal disposing As Boolean)
           If (disposing) Then
               db.Dispose()
           End If

           MyBase.Dispose(disposing)
       End Sub

       Private Function ConferencesExists(ByVal id As Integer) As Boolean
           Return db.Conferences.Count(Function(e) e.AppointmentID = id) > 0
       End Function
   End Class

结束命名空间

嗨，Cesar，听起来很有趣，但我不确定你是否在说我们应该复制EF为我们创建的类。是这样吗？如果我们可以得到你所提到的示例代码就太好了。也许把链接放在一个zip文件里。非常感谢！ - SF Developer
是的，您必须复制EF生成的类，因为如果在创建控制器时使用它们，您将收到错误消息，指出模型没有主键。我会放一些代码。 - Cesar Vega
我不是完全清楚控制器与此有何关系，除非您在创建控制器时指定了强类型。我正在使用数据层方法，因此我的存储过程调用与控制器完全分离（不确定是否相关）。 - SF Developer
好的，控制器需要一个模型以便创建控制器和上下文。如果您的存储过程没有带有主键的类模型，则无法使用该模型。当您从存储过程中检索一个集时，结果没有主键，这就是为什么您必须重新创建模型和上下文以创建控制器的原因。在我发布的示例中，我展示了我创建的模型和从存储过程创建的模型。如果您仍然有理解上的问题，请让我知道，我会尝试发布解决方案的链接。 - Cesar Vega
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接